0

I'm struggling to understand the logic behind MongoDB date formatting, if I save a field like so:

date: Date.now()

in the database looks like:

date: 1633186879027

I have a date like 2021-08-27T19:00:38.000+00:00 , and I save it like so:

date: "2021-08-27T19:00:38.000+00:00"

in the database looks like:

date: "2021-08-27T19:00:38.000+00:00"

and it's not what I want cause it's a string and I can't sort stuff by date then.

so i tried to save it like so:

date: new Date("2021-08-27T19:00:38.000+00:00")

and in the database looks like:

date: 2021-08-27T19:00:38.000+00:00

without brakets, as Mongodb does for strings, so must not be a string either

how am I supposed to save it so that it looks like the first one (1633186879027)? because I then need to sort stuff by date and I think that's the correct format to use?

Nathan Bernard
  • 391
  • 2
  • 7
  • 22
  • refer [mongodb date doc](https://docs.mongodb.com/manual/reference/method/Date/) and similar [so question](https://stackoverflow.com/questions/3778428/best-way-to-store-date-time-in-mongodb). – turivishal Oct 02 '21 at 15:21
  • Be aware, your client application/shell may format the date value based on some application settings and/or locale data. Which application/shell do you use? – Wernfried Domscheit Oct 02 '21 at 16:41
  • I use Node, on vscode – Nathan Bernard Oct 02 '21 at 16:54
  • @turivishal the documentation doesn't explain how to save it as a number sequence as Date.now() does by default, its examples are also horrible and incomplete to put it lightly – Nathan Bernard Oct 02 '21 at 17:01
  • @turivishal and the example in the link ({date: ISODate()}) returns error ISODate is not defined in Node.js – Nathan Bernard Oct 02 '21 at 17:02
  • @NathanBernard the documentation says "*`new Date("")` returns the ISODate with the specified date.*" so the recommended approach is `new Date()` and ISODate method is for shell. second it is possible to do sort operation in ISO date. – turivishal Oct 02 '21 at 17:09
  • why does it save Date.now() as a number by default then if the recommended format is js date object? – Nathan Bernard Oct 02 '21 at 17:36
  • it will not save any default format, you have to set manually, [Date.now()](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Date/now) will return number of milliseconds and [new Date()](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Date/Date) will return an date object. – turivishal Oct 02 '21 at 18:11
  • 1
    @NathanBernard there is no restriction, you can save in any format, the recommended way is `new Date()` means ISODate format, it will save as BSON `$date` type in mongodb. because it supports almost all operations that we do in numeric timestamp. and there are lots of [Date aggregation operators](https://docs.mongodb.com/manual/reference/operator/aggregation/#date-expression-operators). – turivishal Oct 02 '21 at 18:32
  • ok thanks, I saved it as new Date() but now when I retrieve it and do .toString() it throws error cannot do .toString of undefined, which by documentation is supposed to turn it back to string – Nathan Bernard Oct 02 '21 at 20:02
  • 1
    Your timestamp is in one of the formats supported by ECMA-262 for parsing by *Date.parse*, which returns a time value. So consider `date: Date.parse("2021-08-27T19:00:38.000+00:00")`, which is equivalent to `date: new Date("2021-08-27T19:00:38.000+00:00").getTime()` but less to type. – RobG Oct 02 '21 at 20:46

1 Answers1

0

MongoDB stores data using BSON. The definition is here

A datetime is stored as the number of milliseconds since 1970-01-01 using a 64-bit integer.

Date.now() returns an integer, so the sample data would be stored as BSON type \x12 with the value 1633186879027.

new Date("2021-08-27T19:00:38.000+00:00") would be stored as BSON type \x09 with the value 1630090838000.

The output date: 2021-08-27T19:00:38.000+00:00 was generated by the driver or application on the client side after it was retrieved.

MongoDB directly support sorting on dates.

Also note that if you have dates stored as strings like "2021-08-27T19:00:38.000+00:00", a lexicographical sort of those string would put them in chronological order.

Joe
  • 25,000
  • 3
  • 22
  • 44
  • from what I know MongoDB doesn't support lexicographical sorting? I'm using aggregate to query the db – Nathan Bernard Oct 03 '21 at 10:49
  • lexicographical sorting is how strings are sorted. – Joe Oct 04 '21 at 04:28
  • 1
    I honestly thought that in 2021 a popular db like MongoDB had such a functionality, but I don't think it actually does, the tests I did via pipeline aggregation didn't work that way. Not very familiar with lexicographical but it didn't sort correctly dates as strings – Nathan Bernard Oct 05 '21 at 09:39