What does the negative integer Date value mean in MongoDB?
I found a problem with some data stored in MongoDB. We have a field that stores the date, and this usually includes values like ISODate("1992-08-30T00:00:00.000Z")
or ISODate("1963-08-15T00:00:00.000Z")
. It's nice and straightforward; I can easily look at these dates and see August 30, 1992 or August 15, 1963.
However, I noticed a couple of entries where the date looks something like this:
Date(-61712668800000)
I honestly don't know how the data was saved this way in the first place, since it should have been saved the same way. And I will have to address a software bug with my code that periodically causes it to be saved this way.
However, the big problem is what to do with data that looks like this. I don't even know what date it should have been. My first guess is that it is only milliseconds, like a UNIX timestamp or something, but it is not. Even if I reverse the negative sign and remove some of the trailing zeros that still end up being dates in the future (for example, 23rd July 2165) and it is not correct. This must 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 cannot use the query $type
because the type is still 9 (ie it still considers it a "date").
Has anyone else come across these strange date entries before? How can I find them? And how can I recover the actual date from them?
source to share
The problem seems to be that your code is storing dates before the epoch, which nevertheless have gotten to the point where they cannot be represented using the ISODate wrapper:
(in italics)
date
BSON date is a 64-bit integer that represents the number of milliseconds since the Unix epoch (January 1, 1970). This results in a representable date range of about 290 million years in the past and future.
The official BSON specification refers to the BSON date type as UTC datetime.
Changed in version 2.0: BSON date type signed. [2] Negative values represent dates before 1970.
While not explicitly stated in Mongo's documentation, it looks like they follow a strict interpretation of the ISO 8601 standard and not one of the options that are allowed by a "trade partner agreement" based on what I found on wikipedia
years old
YYYYYYYYY ISO 8601 prescribes at least a four-digit year [YYYY] to avoid the Y2K problem. Therefore, it represents years 0000 to 9999, year 0000 is 1 BC and all other BPs. However, years before 1583 are automatically allowed by the standard. Instead, "values in the range [0000] to [1582] shall only be used by mutual agreement of the partners in the communication." [nine]
To represent years before 0000 or after 9999, the standard also allows the extension of the year representation, but only prior to the agreement between the sender and the receiver. [10] Extended year representation [± YYYY] must have an agreed number of additional year digits beyond the four-digit minimum, and must be prefixed with a + or - sign [11] instead of the more common AD or BC (or the less widely used BCE / CE); by convention, 1 BC is denoted +0000, 2 BC is denoted as -0001, etc. [12]
If you read the rest of the article, you will also see that the reason the number of digits must be predefined is because the date can be stored unambiguously without using separators such as "-" between components.
source to share