Querying data according to time zone set by user when all entries are in UTC

Working with Data
1

So to put it simply, we have a huge database of entries and the time associated with each entry is being saved in UTC in the standard format/syntax 2020-03-02T01:11:18.965Z

Now there are multiple users using the application that uses VueJS as the front-end and each user has the ability to set his own time zone.

Let’s say the user wants to calculate stats based on these entries for durations like Today, Yesterday, This Week, This Month.

Now for the Today stat, we would need to calculate data from entries that are between 00:00 and 23:59 in this user’s set timezone but the problem is that the entries are saved in the system in the UTC timezone for all users.

My question is - what is the best and most efficient (performance wise) way to carry out such calculations? The idea is to continue to keep all entries in UTC for everyone but changing timezones means that some entries might go in the previous day and some might go in the next day based on the time zone set which would affect the calculation of the stats completely.

2

Hi @Dev_User ,

Since the indexed values are in UTC , before you provide the predicate to the query/aggregation you need to move personal timezone of the predicate to UTC. Since it is at most 2 dates (upper and lower bound) its a fairly light operation.

Once the matching of the documents complete you can decide to move the dates into user timezone using aggregation operators like $dateToString in $project or doing it on front end side when forming vue to convert any UTC to relevant timezones…

Thanks
Pavel