CODEWITHSUNDEEP
mongodbdatedatetime
soapergem
soapergem

Reputation: 9989

What does a negative integer Date value mean in MongoDB?

I've discovered an issue with some of data being stored in MongoDB. We have a field that stores a Date, and normally this includes values like ISODate("1992-08-30T00:00:00.000Z") or ISODate("1963-08-15T00:00:00.000Z"). That's nice and straight-forward; I can easily look at those dates and see August 30, 1992 or August 15, 1963.

However, I've noticed a couple of entries where the date looks something like this instead:

Date(-61712668800000)

I'm honestly not sure how the data got persisted that way in the first place, as it should have been stored the former way. And I'll have to address the software bug with my code that is intermittently causing it to be stored that way.

However, the bigger problem is what to do with data entries that look like that. I'm not even sure what date that was supposed to be. My first assumption is that it's just milliseconds, like a UNIX timestamp or something, but that's not right. Even if I flip the negative sign and remove some of the trailing zeros, that still ends up being a date way in the future (e.g. July 23, 2165), and that's not correct. It should be a date in the past.

And the other big problem is that I'm not sure how to even search for this in the database. I can't utilize a $type query because the type is still 9 (i.e. it still thinks it's a "Date").

Has anyone else encountered these weird date entries before? How can I find them? And how can I recover the actual date from them?

Upvotes: 5

Views: 3172

Answers (1)

user700390
user700390

Reputation: 2339

The problem seems to be that your code is storing dates prior to the epoch, which are furthermore so far into the past that they cannot be represented using an ISODate wrapper:

As per the documentation

(emphasis added)

Date

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.

The official BSON specification refers to the BSON Date type as the UTC datetime.

Changed in version 2.0: BSON Date type is signed. [2] Negative values represent dates before 1970.

Although not explicitly stated in the Mongo documentation, it appears that they are following a strict interpretation of the ISO 8601 standard and not one of the variants which are allowed "by trading partner agreement" based on what I found at wikipedia

Years

YYYY ±YYYYY ISO 8601 prescribes, as a minimum, a four-digit year [YYYY] to avoid the year 2000 problem. It therefore represents years from 0000 to 9999, year 0000 being equal to 1 BC and all others AD. However, years prior to 1583 are not automatically allowed by the standard. Instead "values in the range [0000] through [1582] shall only be used by mutual agreement of the partners in information interchange."[9]

To represent years before 0000 or after 9999, the standard also permits the expansion of the year representation but only by prior agreement between the sender and the receiver.[10] An expanded year representation [±YYYYY] must have an agreed-upon number of extra year digits beyond the four-digit minimum, and it must be prefixed with a + or − sign[11] instead of the more common AD or BC (or the less widely used BCE/CE) notation; by convention 1 BC is labelled +0000, 2 BC is labeled -0001, and so on.[12]

If you read through the rest of the article you will also see that the reason the number of digits must be pre-defined is so that the date can be stored unambiguously without using separator characters such as "-" between the components.

Upvotes: 5

Related Questions