13

I've read a lot about how to store simple dates (without time) in MongoDB, but I still can't find an answer. Some say to store theme like MongoDate (date + utc time), some say to store theme like a YYYYMMDD string, and some like other funny ways. The rightest way seems to be MongoDate, but why should I store a date of birth as a date with UTC time?

Plus, the date of birth "1990-05-21" is stored as "1990-05-20T23:00:00Z" (the day before): this date shouldn't change depending on timezone, but remain the same world wide.

I'm still wondering why MongoDB doesn't provide a simple "date" type, as all the other databases do.

Drew
  • 251
  • 1
  • 3
  • 13
  • If you want to store a birthday, then perhaps you don't want to store a year at all - i.e. you could just store the month and day? To be honest, it depend on your use case: how much information do you need to store, how do you need to query it, how do you need to compare them, etc. – Vince Bowdren Apr 21 '17 at 10:50
  • @Vince Bowdren: I need to store the dates of birth. YYYY-MM-DD obviously without time. I've changed the post title. – Drew Apr 21 '17 at 18:33

5 Answers5

3

Simply use:

new Date("<YYYY-mm-dd>");

Which returns the ISODate with the specified date without a timestamp. MongoDB uses the ISO-8601 date notation, to represent date objects. This way, a lot of date operations are provided. I.e.

  • new Date("<YYYY-mm-dd>") returns the ISODate with the specified date.

  • new Date("<YYYY-mm-ddTHH:MM:ss>") specifies the datetime in the client’s local timezone and returns the ISODate with the specified datetime in UTC.

  • new Date("<YYYY-mm-ddTHH:MM:ssZ>") specifies the datetime in UTC and returns the ISODate with the specified datetime in UTC.

  • new Date(<integer>) specifies the datetime as milliseconds since the Unix epoch (Jan 1, 1970), and returns the resulting ISODate instance.

And even more, internally, date objects are stored as a signed 64-bit integer representing the number of milliseconds since the Unix epoch (Jan 1, 1970).

Kurt Van den Branden
  • 11,995
  • 10
  • 76
  • 85
  • 6
    The problem with this answer is when it comes to a birthdate, you never want the result to be "in the client's local timezone". If a person was born on July 3rd and you store it as UTC+0 on July 3rd, for locales that will result in a different local date. – stevex Jun 20 '19 at 13:30
1

As mentioned in comments its almost depends on your final target. I would suggest you to use MongoDate because it's native format for dates in MongoDB and will keep things intuitive for other developers. And the native type supports a whole range of useful methods out of the box, which you can use in your map-reduce jobs, for example.

Plus, the date of birth "1990-05-21" is stored as "1990-05-20T23:00:00Z" (the day before)

I think that this happens just because timezone on your server is UTC+1. You can manually set the timezone to UTC before saving to DB and it will solve your problem.

Andrey Degtyaruk
  • 488
  • 4
  • 9
  • 2
    What do you mean by "it depends on your final target"? My target is to save the date of birth and do any kind of query with it (like all the other values). To save the time along with the date of birth, it sounds kind of weird. In my ID Card I have no time beside the date of birth. That happens with all those values that don't need a time at all. Setting the UTC to zero before saving, it means to set the time to midnight, not to "null". That's nonsense to me. – Drew Apr 21 '17 at 19:16
  • > My target is to save the date of birth and do any kind of query with it (like all the other values) It depends on what kind of queries do you want to perform. If you want, for example to query all users whose birthday is in Friday it would be difficult to implement with strings. And all other date-specific queries except exact match. How would you perform query which will select all the users born between 21.03.1979 and 22.02.1980 in case of using strings? – Andrey Degtyaruk Apr 21 '17 at 19:21
  • 1
    With "all kind of queries" I mean to have no limit. That same happens with invoices: do I store an invoice with a midnight time? I don't know how I would perform that, that is why I'am asking. It seems absurd to me that MongoDB does not have a simple "date" type. – Drew Apr 21 '17 at 19:27
  • 1
    Why do u think it's absurd? Simply - do not use anything except date in your result. Ignore time and timezone and you're done. I don't see any problem here at all. https://docs.mongodb.com/v3.2/reference/bson-types/#document-bson-type-date – Andrey Degtyaruk Apr 21 '17 at 19:30
  • 1
    thank you for your answer. I think it is "formally" absurd to store a meaningless time that I will not use. We're talking about storing a date with a UTC+0 that is midnight in Greenwich. It is neither a fact of bytes (I don't care about it), but all databases have a "date" type, because there are a lot of values that dosn't have a time: date of birth/death, invoice, food expiration, exams... – Drew Apr 21 '17 at 19:41
1

Store it as a simple string "1985-21-07". It might sound counter-intuitive and light up your red "database types" bulb, but saving a date of birth as a Date() brings to the table many "parts" (hours, minutes, seconds...) that don't mean anything and will cause headache and be error-prone when saving and getting birth dates in and out of mongo.

Read the highest answer here (NOT THE ONE MARKED AS CORRECT): What is the best way to store dates in MongoDB?

Yaron Levi
  • 12,535
  • 16
  • 69
  • 118
0

That's an interesting question and it can't be easily answered. As @Andrey Degtyaruk already noticed it's all about purpose of these datas.

In my case I solve almost the same problem something like these: 

store time like a YYYYMMDD string

but, a bit different way. Because "timestamp" field is already reserved by MongoDB (according to BSON docs) I convert date to timestamp (you could use one of these js scripts if you're interested) and store it in DB as "lastModified":"Number".

I'm not sure that my method could be relevant in your Date-of-Birth case, but it's still a way and you could try it.

Community
  • 1
  • 1
AlexZeDim
  • 3,520
  • 2
  • 28
  • 64
0

I was looking for the best solution to this problem as well, and while reading these answers I thought that the simplest solution which would ignore time and thus timezone shifts, yet still allow comparisons and queries on a persons age etc. would be to use an integer format along the lines of YYYYMMDD.

So for birth date 12 March 1980, this would be saved as integer 19800312.

Then I found this plugin for Mongoose, which does more or less that: https://github.com/boblauer/mongoose-dateonly

Only drawback is that you'd have to use DateOnly directly if you want to use the aggregate framework.

Adam Reis
  • 4,165
  • 1
  • 44
  • 35