I have a collection that stores all the orders by date. The order date is stored in the database as UTC DateTime.
e.g. when I am sending a request from EST (just as a sample), and want to check the daily statistics of orders from 14/09/2022 to the end of 16/09/2022, it comes to the server like 14/09/2022 04:00:00 to 17:09/2022 04:00:00.
Now if I get the data from the database and group it based on the Date I will have the result for 14,15,16,17. Since it was based on the date, the time will be 00:00:00. And for the user who wants to see this data, it won't be correct because, in the report, the next day contains statistics for 4 hours of the previous day.
My database is MongoDB and I am doing this in C#.
My question is What is the best way to do the GroupBy Date based on the user's time zone?
One solution can be to get the data based on the start and end date time and do the grouping manually in a while loop. But What is the other way to do this using LINQ GroupBy or on db side?