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.
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.
How can i optimise this??
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)