How to do query with timezone settings in Mongodb

Question

I'm trying to transfer my application database from Mysql to Mongodb, but I met a problem with datetime queries.

In Mysql, when there is a datetime or timestamp column, you can set specific timezone for each request using command:

SET time_zone = timezone;
// do queries here

Is there any kind of similar solutions for this kind of demands?

I know applications can do the job after retrieving data from mongodb, but what about using aggregation with $hour, $month or $day operators?

i don't think there is... that is why you should always store dates as UTC. let the application figure out the timezone stuff! — c0deNinja, Jan 24 '13 at 08:10
considering functions in mysql like: hour(), day(), month() for dates, application will have to do a lot of work to accomplish all these things — Jack, Jan 24 '13 at 10:22
If you set the timezone manually via the timezone notation on the ISO date object as shown in this question: http://stackoverflow.com/questions/11486779/formatting-isodate-from-mongodb mongodb might be able to resolve the timezone correctly server side however I would recommend doing stuff in UTC resolving to UTC timestamps and then adding the integer representation of the timezone ontop and then resolving the display again on client side — Sammaye, Jan 24 '13 at 14:13
This is only a solution for simple find methods. But when you want to do aggregations with datetime operations like $hour, $dayOfWeek. You could only do the timezone work on server side. — Jack, Jan 25 '13 at 03:57
@user1914683 - I agree, this is a problem I'm encountering too. — Ken Williams, Jun 10 '13 at 15:08

score 19 · Accepted Answer · edited Oct 20 '14 at 19:40

MongoDB stores all dates and times in UTC. This allows the timezone support and translation to be done application side instead of server side.

The best way to store dates and times is via the Javascript Date object. This will allow you to convert dates to and from Unix timestamps, using getTime() and Date(milliseconds).

It should be noted, that Javascript stores time in milliseconds since UNIX Epoch, whilst the standard UNIX timestamp is in seconds since Epoch.

score 1 · Answer 2 · answered Jan 24 '13 at 08:14

1

had a similar issue discussed here, https://groups.google.com/forum/?fromgroups=#!topic/mongodb-user/PodDGnWM09Q

also, this website also might come in handy for future refrencing :) http://www.querymongo.com/

answered Jan 24 '13 at 08:14

saad arshad

259
1
10

How to do query with timezone settings in Mongodb

2 Answers2

Linked