47

I am just starting to learn about MongoDB and hoping to slowly migrate from MySQL.

In MySQL, there are two different data types - DATE ('0000-00-00') and DATETIME ('0000-00-00 00:00:00'). In my MySQL, I use the DATE type, but I am not sure how to transfer them into MongoDB. In MongoDB, there is a Date object, which is comparable to DATETIME. It seems it would be most appropriate to use Date objects, but that would be wasting space, since hours, min, sec are not utilized. On the other hand, storing dates as strings seems wrong.

Is there a golden standard on storing dates ('0000-00-00') in MongoDB?

jhonkola
  • 3,385
  • 1
  • 17
  • 32
burger
  • 5,683
  • 9
  • 40
  • 63
  • @DanDascalescu : this is not a duplicate. The discussion you refer to is about date/time, while here it is about how to store dates without the time. – Patrick Hammer Mar 11 '16 at 12:58

3 Answers3

73

I'm actually in the process of converting a MongoDB database where dates are stored as proper Date() types to instead store them as strings in the form yyyy-mm-dd. Why, considering that every other answerer says that this is a horrible idea? Simply put, because of the neverending pain I've been suffering trying to work with dates in JavaScript, which has no (real) concept of timezones. I had been storing UTC dates in MongoDB, i.e. a Date() object with my desired date and the time set as midnight UTC, but it's unexpectedly complicated and error-prone to get a user-submitted date correctly converted to that from whatever timezone they happen to be in. I've been struggling to get my JavaScript "whatever local timezone to UTC" code to work (and yes, I'm aware of Sugar.js and Moment.js) and I've decided that simple strings like the good old MySQL standard yyyy-mm-dd is the way to go, and I'll parse into Date() objects as needed at runtime on the client side.

Incidentally, I'm also trying to sync this MongoDB database with a FileMaker database, which also has no concept of timezones. For me the simplicity of simply not storing time data, especially when it's meaningless like UTC midnight, helps ensure less-buggy code even if I have to parse to and from the string dates now and then.

Geoffrey Booth
  • 7,168
  • 5
  • 35
  • 42
  • 10
    Upvote. The biggest downside of storing a date as a BSON UTC date/time is not storage. It makes you deal with timezones where they simply do not apply, and it is way harder than parsing simple date strings. I would also recommend representing dates as strings in MongoDB. – Rafa Viotti Apr 29 '14 at 15:08
  • 25
    Agreed. Also I discovered to my pleasant surprise that `$gt` and `$lt` work as desired with dates stored as strings: `.find({created: {$gt: '2012-01-01'}}` does, in fact, return all documents created since January 1, 2012. – Geoffrey Booth Apr 29 '14 at 15:43
  • 1
    I'm glad that I'm not along in using `yyyy-mm-dd` to store date in MongoDB. – leesei Aug 21 '14 at 10:44
  • This is the best options to deal with dates when you want to query them. i always use/store date as string when it comes to JavaScript – gaurang171 Sep 22 '14 at 15:32
  • 3
    This way you lose all date aggregation options. – Lukasz Frankowski May 14 '18 at 07:50
  • 1
    @ŁukaszFrankowski, from the version 3.6 you can use $dateFromString. – Sandro Simas Jul 21 '18 at 19:55
  • most of the tool will use their own functions to truncate timestamp part before they use it so they might as well use DATE function to convert date stored as string in mongodb... it is practical... – VGupta Aug 06 '18 at 22:40
  • @GeoffreyBooth Yes, it will do a lexicographic string comparison, which happens to work out well for the 'yyyy-mm-dd' format. – Pranaya Tomar Oct 03 '21 at 21:09
31

BSON (the storage data format used by mongo natively) has a dedicated date type UTC datetime which is a 64 bit (so, 8 byte) signed integer denoting milliseconds since Unix time epoch. There are very few valid reasons why you would use any other type for storing dates and timestamps.

If you're desperate to save a few bytes per date (again, with mongo's padding and minimum block size and everything this is only worth the trouble in very rare cases) you can store dates as a 3 byte binary blob by storing it as an unsigned integer in YYYYMMDD format, or a 2 byte binary blob denoting "days since January 1st of year X" where X must be chosen appropriately since that only supports a date range spanning 179 years.

EDIT: As the discussion below demonstrates this is only a viable approach in very rare circumstances. Basically; use mongo's native date type ;)

Dan Dascalescu
  • 143,271
  • 52
  • 317
  • 404
Remon van Vliet
  • 18,365
  • 3
  • 52
  • 57
  • Binary fields include 4 byte length and 1 byte subtype, so empty binary field occupies 5 bytes. – pingw33n Jul 21 '11 at 22:17
  • I know. To clarify, this would be storing multiple dates in a single blob. The only valid reason to go through that amount of effort to remove a few bytes is when you have a lot of dates to store. I thought this was implied in the OP. – Remon van Vliet Jul 22 '11 at 09:04
  • This way of storing will also make the dates non-indexable and tedious to update. Developing your idea of packing one can use some fast compression library over it (like Google Snappy). – pingw33n Jul 22 '11 at 10:23
  • Yes, there are many disadvantages and frankly I can't think of any valid use case. Perhaps it's easier to remove that option from the answer ;) – Remon van Vliet Jul 22 '11 at 12:13
  • 7
    The valid use case is to avoid the difficulties in dealing with timezones in JavaScript. See my answer below. I find it easier to deal with parsing/formatting strings like `yyyy-mm-dd` than to try to coordinate client and server timezones and create a `Date()` object at the proper date. Remember we’re saving just dates here, not datetimes. – Geoffrey Booth Apr 29 '14 at 15:47
  • 3
    @Geoffrey I was referring to using binary data to store dates. Storing them as strings is perfectly valid. That said I'm a strong proponent of never dealing with timezones on the server side to begin with and store everything as UTC. After that you can do timezone conversion on the client. A lot cleaner. – Remon van Vliet Apr 30 '14 at 12:22
2

If you really care about saving 4 bytes per field (in case you have many DATE fields per document) you can store dates as int32 fields in form 20110720 (note MySQL DATE occupies 3 bytes, so the storage will be greater in any case). Otherwise I'd better stick to standard datetime type.

pingw33n
  • 12,292
  • 2
  • 37
  • 38