MongoDB “empty” or NULL Date

user_2853283 0
Viewed 7496

I will begin using MongoDB in a project and made the following observation: ‘Date’ must be a valid point in time, and cannot be NULL.

With MongoDB, we cannot store a NULL date, so I am guessing that the best thing to do is to simply exclude the date field from our document.

For example, if I have a ‘car’ document and a field named ‘date_sold’. If the car has not been sold yet, I simply exclude this field from the document. Once the car sells, I can add the 'date_sold' field.

By doing the above, I am assuming that I can query records for cars sold or not sold. Is this a recommended way to handle “empty” or null dates in MongoDB?

P.S. My confusion stems from the fact that since SQL has a schema, date or datetime fields can be stored as NULL, but in MongoDB, a ‘Date’ field cannot be NULL and must be a valid point in time.

mongodb
2 Answers

MongoDB types are BSON

If you go to DATE type there, you will read:

BSON Date is a 64-bit integer that represents the number of milliseconds since the Unix epoch (Jan 1, 1970). This results in a representable date range of about 290 million years into the past and future.

So, you need to give a "point on time" per data type definition itself.

Usually the Unix Epoch -> Jan 1, 1970 is the one used as null in order to follow good practices on most database systems that handle with that kind of dates (if you don't need to work with dates as old as those ones).

Remember also this point:

BSON Date type is signed. Negative values represent dates before 1970.

So, you can also set a convention default value for your development, that is on the past just for this purpose. For example:

Jan 1, 1900

I Hope this clarifies to you.

One Hint more: As usually json structures may be validated on different steps, I strongly reccomend to you to maintain the field, and use a convention value for representing NULL under your system.

user_36175310

Omitting the date field when there is no date to store works fine in MongoDB unlike a traditional SQL database where every record / document has to follow the same schema.

You can use $exists to test for a value in that field.

user_2243700
Related