Sql-server – Optimise query based on year month (multiple timezone)

database-designsql server

Note: Though I am working on sql server, this solution might not need to tie to a specific database. Just need a general idea on how it could be done.

So, I have been research and found out that it is best to store separate YEAR and MONTH field (with indexing) instead of default datetime when query report based on year and month as this is much more efficient than comparing datetime value.

Then I WILL query something like this

SELECT * FROM [dbo].[tblReport] WHERE YEAR([Year]) = @YEAR AND MONTH([Month]) = @MONTH

This would be a good solution if you are assuming all user are seeing all the available report in a same timezone. and we save the year and date in the one specific local timezone.

Suppose I have requirement that report shown to user should be dependent on the timezone of the user. Then this will not be working.

Scenario:

User in Zone A (GMT + 12) and User in Zone B (GMT – 4). (The user timezone offset will be pass as parameter in the stored proc)

Now I have a report 1 of 2019-4-1 00:00 (UTC) in database.
So By right user in Zone A will be able to see that report if i select March 2019.
But since it is stored as Year – 2019, Month – 4, it is unable to show.

Same if I have a report 2 of 2019-03-31 23:00 (UTC) in database. User in Zone B should be able to see the report 2 when queying April 2019, but since it is stored Year – 2019, Month – 3, it will not be shown in the list when querying.

Question:
How can i optimise this??

Note:
This should be look as a technical requirement. Do not try to question the validity of the business requirement (eg: seeing report should not be dependent on user timezone itself, this is rarely a use case etc)

Best Answer

If it really depends on the user's location anywhere in the world, then you can't do it. Use the complete date stamp, and why is it supposedly less efficient anyway? If you want less precision to keep your keys and indexes smaller, you can just truncate that extra second precision, but you need to go down to half hours in order to be right on your time zones. Seems not worth the effort.

Semantically it is also not always right to assume the date should be relative to the querying user. It might be more correct to keep the date according to the locality where the event in question happened. Let's say a database of crime events, makes much more sense to store these events with local time, that would not be subject to time zone differences relative to the querying users.