Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to do query with timezone settings in Mongodb

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?

like image 998
Jack Avatar asked Jan 24 '13 08:01

Jack


People also ask

What is timezone in MongoDB?

Overview. MongoDB stores times in UTC by default, and will convert any local time representations into this form. Applications that must operate or report on some unmodified local time value may store the time zone alongside the UTC timestamp, and compute the original local time in their application logic.

Can I run query in MongoDB compass?

Yes, Mongo compass provides only filter option(Query Bar) to do queries on specific collection.


2 Answers

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.

like image 142
Nick Avatar answered Sep 21 '22 08:09

Nick


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/

like image 41
saad arshad Avatar answered Sep 18 '22 08:09

saad arshad