Work with dates and timezones

Working with Data
1

Hello everyone!

Sorry if my question is silly, as I have just started to learn MongoDB, but I can not figure out how to solve one problem, using MongoDB. Therefore ask your advices here.

The main problem - how to work with timezones in MongoDB? In case if we just need to store time - here is no problem and everything is clear, but so if we don’t have time but only timezone and need to know current time, applying particular timezone ?

As an example, what I mean by that here is small example which I would like to achieve.

Imagine we have users in collection, every user has its own timezone stored in field timezone.
We have scheduled task to send notifications to user, but, time for send it has to be strictly, let’s say, from 9:00 am to 6:00 pm. And of course, in particular moment of time every user will have different time.
The question is: how to select those users which are in period of 9:00 am - 6:00 pm by their local time?

–
In this article: https://docs.mongodb.com/manual/tutorial/model-time-data

Its advised instead of timezone store offset, in this case it’s easy to reconstruct time, but this time will not always be correct. Therefore in my opinion it’s harmful advise.

Imagine that we created record at first of January with Germany timezone (UTC +1) so offset will be 3600. But once German will switch to summer time (29 March 2:00 am) it has to be UTC +2. So we will convert time wrong after that period. That’s why i don’t really like solution with offset.

Here is $where operator which could help, but unfortunately there is no way to work with timezones in JavaScript.

Any advise or thoughts in that?

Thank you in advance,
Best regards,
Alex

2

I’m confused - why do you think $where would help?

As far as storing time zone, don’t store offset from UTC, store the actual time zone like Europe/Berlin

3

Hello Asya!
Thank you for your time replying me!

Yes, that’s what i want to store. Store only timezone name, such you mentioned, Europe/Berlin, because storing offset will require update all the users in a future once DST turns on or off occurred.
But how can i convert current time applying timezone from document? (Is there any tool in MongoDB to work with timezones?) And then, how to compare it to allowed time range ?
I can see only one way doing some calculations in MongoDB query - $where, that’s why I was thinking it might help.

4

Handling and displaying time and date in the user timezone is a presentation layer issue not a data layer issue. For this reason it depends on which driver you use. For example, with the node.js driver, you could use Date.prototype.toLocaleTimeString() - JavaScript | MDN to do it.

1 Like
5

I definitely understand the issue given that usually storing UTC and then displaying it in the “local” format is pretty simple with various helper functions (you can even do it server side as $dateToString takes timezone).

What I would recommend is using either $dateToString or antoher aggregation function $dateToParts to deal with this - depending on what you want to do with result.

db.tz.find()
{ "_id" : "Asya", "tz" : "America/New_York" }
{ "_id" : "Stennie", "tz" : "Australia/Sydney" }
{ "_id" : "Alex", "tz" : "Australia/Sydney" }
{ "_id" : "Joe", "tz" : "Europe/Dublin" }
Fetched 4 record(s) in 2ms
> db.tz.aggregate({$addFields:{now:{$dateToString:{date:new Date(), timezone:"$tz"}}}})
{ "_id" : "Asya", "tz" : "America/New_York", "now" : "2020-02-16T11:14:02.734Z" }
{ "_id" : "Stennie", "tz" : "Australia/Sydney", "now" : "2020-02-17T03:14:02.734Z" }
{ "_id" : "Alex", "tz" : "Australia/Sydney", "now" : "2020-02-17T03:14:02.734Z" }
{ "_id" : "Joe", "tz" : "Europe/Dublin", "now" : "2020-02-16T16:14:02.734Z" }

If you are on 4.2 you can even generate current time server-side with $$NOW expression.

4 Likes
6

Thank you again Asya!

In case of aggregate - it will be possible to filter results by $match operator. Just need to think over, how can I apply rules to select only users which are in desired time window.

But wouldn’t it be very heavy query to execute aggregation for every single document in collection ?

Thnaks!

7

Hello Steevej, I think you did not understand my question.

I do not want to display time. I want to filter users which are in specific time window applying their own timezone.

8

Indeed. I hope you did not lost too much time investigating my link.

9

Take a look at $expr operator - it allows you to use aggregation expressions in $match stage or find command.

Like this:

db.tz.find({$expr:{$eq:[0, {$hour:{date:new Date(), tz:"$tz"}}]}})
{ "_id" : "Joe", "tz" : "Europe/Dublin" }

This finds all users for whom the time now is between midnight and 1am (aka hour=0).

wouldn’t it be very heavy query to execute aggregation for every single document

Well, if you need to query by something, then you query by it. If you are able to constrain the results by another field that could be indexed, then it would help, otherwise, it’s like any other collection scan - not optimal and if this is a large collection and this query needs to run frequently I would recommend figuring out how you can index this query.

4 Likes
10

Thank you very much Asya for your flawless support!