When working with dates in MongoDB, you have to be extra careful because: MongoDB by default stores date & time in ISODate object which is in UTC/GMT. - without any timezone data appended to it. The idea is - that you convert the date in a correct timezone when you query it and display to the user. If you don't do that - you gonna have bad time. Literally. :) Day of birth should not get converted to local time zone, but should be stored as UTC and used as UTC.
I started writing this document as a reminder to my self when doing mongo stuff.
While working with time and date information in any database eingine and any programming language, where you live and where your clients live will determinate greately how you approach time, date and timezones in general while learning or developing.
If you are dealing with users only in one timezone - chances are - you never had to give a special tought about time zones in general. You probably ocurred light issues onece or twice a year (for eg. if you live in dailighy savings time zone which change +/- 1 hour several times durring one year). But mostly - you would store some time and dates, and retrieve them without much issues.
Maybe you even save your time and date information in a database in a UNIX timestamp format - without any timezone information added or substraceted to it (Unix timestamps are always based on UTC/GMT and eg. do not account for leap seconds).
If you do most of programming server-side, case is that your application & database is working internally with a dates in a timezone of your server. If your server is in your timezone - you probably won't notice anything "off". Until daylight saves time changes kick in and you start noticing in your database records with same timestamps (as they were repeating).
No matter if you think nothing is off with this concept because it "mostly works" for you, things are so much off with this concept that this document cannot even grasp small part of the problem.
If your application has customers from multiple time zones, serving from multiple servers from multiple data centers, then ignoring time zones will just not work.
Let's take a following example of User X who did Y in a Europe/Zagreb on 2021-12-31 at 23:00:00 (CET/UTC +1)
- we save this to DB in UTC - so for mongodb event happened on - UTC 2021-12-31 22:00:00
- while retrieving we apply timezone:
- for a user in Sydney/Australia that event happeed by his local time at AEDT (UTC +11) Sat, 1 Jan 2022 20:00
- for a user in New York, NY, USA that event happened b his local time at EST (UTC -5) Fri, 31 Dec 2021 17:00
But what about date of birth you may ask? If for example - you are querying international user database for printing out user documents with date of birth field on it - you do not want to converted that date to your local timezone. You ofc need original one. But date is saved in UTC, and you are in New York, and you do not know wheere on Earth that user was born, nor in which timezone.....
https://docs.mongodb.com/manual/reference/method/Date/ https://docs.mongodb.com/manual/tutorial/model-iot-data/ https://docs.mongodb.com/manual/reference/bson-types/#std-label-document-bson-type-date
You can specify a particular date by passing an ISO-8601 date string with a year within the inclusive range 0 through 9999 to the new Date() constructor or the ISODate() function. These functions accept the following formats:
new Date("<YYYY-mm-dd>")returns theISODatewith the specified date.new Date("<YYYY-mm-ddTHH:MM:ss>")specifies the datetime in the client's local timezone and returns theISODatewith the specified datetime in UTC.new Date("<YYYY-mm-ddTHH:MM:ssZ>")specifies the datetime in UTC and returns theISODatewith the specified datetime in UTC.new Date(<integer>)specifies the datetime as milliseconds since the UNIX epoch (Jan 1, 1970), and returns the resultingISODateinstance.
Internally, Date objects are stored as a signed 64-bit integer representing the number of milliseconds since the Unix epoch (Jan 1, 1970).
Not all database operations and drivers support the full 64-bit range. You may safely work with dates with years within the inclusive range 0 through 9999.
In the MongoDB, you can store both the current date and the current client's offset from UTC.
var now = new Date();
db.data.save( { date: now, offset: now.getTimezoneOffset() } );
You can reconstruct the original local time by applying the saved offset:
var record = db.data.findOne();
var localNow = new Date( record.date.getTime() - ( record.offset * 60000 ) );
When inserting date & time as Date object, without specifying timezone offset (Z), if the date you specified is within your daylight savings period - the hour is subtracted/added to create UTC forcing your date to be on the previous day.
db.test.insertOne({dob: new Date("1977-3-20")})
[
{
_id: ObjectId("61bdc7dbebc9fb604195c2e6"),
dob: ISODate("1977-03-19T23:00:00.000Z")
}
]
db.test.insertOne({dob: new Date("1977-03-20T00:00:00")})
[
{
_id: ObjectId("61bdcaa4ebc9fb604195c2ed"),
dob: ISODate("1977-03-19T23:00:00.000Z")
}
]
You can specify the UTC date (notice Z) when using Date object, or to use ISODate object alltogether:
db.test.insertOne({dob: new Date("1977-03-20Z")})
db.test.insertOne({dob: new Date("1977-03-20T00:00:00Z")})
Will insert:
[
{
_id: ObjectId("61bdca88ebc9fb604195c2ec"),
dob: ISODate("1977-03-20T00:00:00.000Z")
}
]
db.test.insertOne({ dob: ISODate("1977-03-20T00:00:00") })
[
{
_id: ObjectId("61bdc818ebc9fb604195c2e7"),
dob: ISODate("1977-03-20T00:00:00.000Z")
}
]
db.test.insertOne({ dob: ISODate("1977-03-20") })
[
{
_id: ObjectId("61bdc840ebc9fb604195c2e8"),
dob: ISODate("1977-03-20T00:00:00.000Z")
}
]
db.getCollection('test').find(
{
dob:{
$eq: ISODate("1977-03-20")
}
})
db.getCollection('test').find(
{
dob:{
$gte: ISODate("2021-01-01"),
$lt: ISODate("2020-05-01")
}
})
Converts a date object to a string according to a user-specified format.
db.test.aggregate([
{
$project: {
dateOnly: {
$dateToString: {
format: "%d.%m.%Y.", // EU
date: "$dob"
}
},
}
}
])
Timezone accepts:
-
Olson Timezone Identifier "America/New_York" "Europe/London" "GMT"
-
UTC Offset +/-[hh]:[mm], e.g. "+04:45" +/-[hh][mm], e.g. "-0530" +/-[hh], e.g. "+03"
db.test.aggregate([
{
$project: {
dateOnly: {
$dateToString: {
date: "$dob" ,
timezone: "America/New_York"
}
},
}
}
])
returns - UTC date from db and converts to UTC/GMT -5 hours (24-5=19h) a day before - 1977-03-19T19:00:00.000Z:
[
{
_id: ObjectId("61bdd83bebc9fb604195c2f9"),
dateOnly: '1977-03-19T19:00:00.000Z'
}
]
// UTCDateTime - needs microsecond precision
$current_time = new MongoDB\BSON\UTCDateTime(time() * 1000);
$doc = ['_id' => new MongoDB\BSON\ObjectID, 'event_date' => $current_time];
$res = $collection->insertOne($doc);
date_default_timezone_set("Europe/Zagreb");
var_dump(date_default_timezone_get()); // string(13) "Europe/Zagreb"
$cursor = $collection->find();
foreach ($cursor as $statement) {
$event_date = $statement->event_date;
// MongoDB\BSON\UTCDateTime - The value is a 64-bit integer that represents the number of milliseconds
/// since the Unix epoch (Jan 1, 1970). Negative values represent dates before 1970.
var_dump($event_date); // 1639850650000
$datetime = $event_date->toDateTime(); // let's convert MongoUCTDateTime to DateTime object
var_dump($datetime->getTimestamp()); // 1639850650 - here is timestamp
var_dump($datetime->format('Y-m-d H:i:s (e)')); // string(28) "2021-12-18 18:04:10 (+00:00)" - here is UTC time!
$date = new DateTime('@' . $datetime->getTimestamp(), new DateTimeZone('UTC'));
$date->setTimezone(new DateTimeZone(date_default_timezone_get()));
var_dump($date->format('Y-m-d H:i:s (e)')); // string(35) "2021-12-18 19:04:10 (Europe/Zagreb)" <- local time we can use!
$dateInUTC=$datetime->format(DATE_RSS);
$time = strtotime($dateInUTC.' UTC');
$dateInLocal = date("Y-m-d H:i:s", $time);
var_dump($dateInLocal); // string(19) "2021-12-18 19:04:10" <- local time we can use!
$datetime->setTimezone(new DateTimeZone('America/New_York'));
var_dump($datetime->format('Y-m-d H:i:s (e)')); // string(38) "2021-12-18 13:04:10 (America/New_York)"
$datetime->setTimezone(new DateTimeZone('Australia/Sydney'));
var_dump($datetime->format('Y-m-d H:i:s (e)')); // string(38) "2021-12-19 05:04:10 (Australia/Sydney)"
$datetime->setTimezone(new DateTimeZone('Europe/Zagreb'));
var_dump($datetime->format('Y-m-d H:i:s (e)')); // string(35) "2021-12-18 19:04:10 (Europe/Zagreb)" <!- local time we can use!
}
// not quite
db.test.find({}, {dob:1, _id:0 })
[ { dob: ISODate("1977-03-20T00:00:00.000Z") } ]
// good
db.test.findOne().dob
ISODate("1977-03-20T00:00:00.000Z")
let result = {
last_import_date: cron_last_import_date, // will output UTC version
last_import_date_local: cron_last_import_date.toString() // will convert to local time zone and output
}
will output:
{
"last_import_date": "2021-11-12T23:39:05.043Z",
"last_import_date_local": "Sat Nov 13 2021 00:39:05 GMT+0100 (Central European Standard Time)"
}