Retrieve local time info with mongo $hour

Question

I am currently storing documents in mongodb with the help of a mongoose model. Querying by date works fine, but when I try to apply the $hour operator, it seems the ISO time is returned, and not the local time.

The current naming convention gives me a filename which includes the timestamp of the creation of the file: YYYYMMDDHHMMSS-foo-bar.file

Example: the import in the DB results in the following values for the timestamp parsed: 2014 04 02 07 57 33-foo-bar.file gets parsed into ISODate("2014-04-02T05:57:33Z").

When I do a query to find this item: I create new date objects like this (the dates are bogus and only for the sake of the querying approach I chose):

var begin = new Date(2014, 01, 10, 13, 00, 00);
var end = new Date(2014, 01, 10, 20, 00, 00);

db.pinas.aggregate([{
    $project: {
        [attributes I need...]
    }
, {
    $match: {
        'start_time': {
            $gte: begin, //ISODate
            $lt: end //ISODate
        }
    }
}]);

And again, these ISODates do not seem to store the timezone information although in this post ISODates contain something: note the trailing +01:00 ISODate("2012-07-14T01:00:00+01:00")

Now I have two questions/problems:

I read somewhere that the point of returning datetime info in ISODates was to shift the "fine work" to the application rather than the DBMS, and that's true: it works in the case of parsing back the ISODatestring, I retrieve the correct time (I assume through knowing my local settings? What if the server was in another timezone?)

When I want to use the $hour date aggregation operator, how am I supposed to bring it back to local time? How does it work when I only return $hour?

Answer 1

Since version 3.6 you can add a timezone attribute to get the hour in the correct time:

{ date: <dateExpression>, timezone: <tzExpression> }

for example:

{ $hour: {
    date: new Date("August 14, 2011Z"),
    timezone: "America/Chicago"
} }

for more details here is $hour documentation https://docs.mongodb.com/manual/reference/operator/aggregation/hour/

Answer 2

MongoDB has no support for times in anything but UTC. Anything you need to do in other time zones needs to be done client-side, so you would need to adjust the $hour values provided by MongoDB to local time in your own code.

See the getTimezoneOffset() method of Date, and be mindful of daylight savings.

Not ideal, but that's how it is.