[SERVER-45324] Incorrect ISODate format output for year before 0000 Created: 31/Dec/19  Updated: 25/Feb/20  Resolved: 25/Feb/20

Status: Closed
Project: Core Server
Component/s: None
Affects Version/s: 4.0.2, 4.2.0
Fix Version/s: None

Type: Improvement Priority: Minor - P4
Reporter: Morty Choi Assignee: Justin Seyster
Resolution: Won't Fix Votes: 0
Labels: qexec-team
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Sprint: Query 2020-01-27, Query 2020-02-10, Query 2020-02-24, Query 2020-03-09
Participants:

 Description   

The ISODate format is wrong for date-time which year is negative.

If I mongoexport the collection with data with these values, I can't mongoimport back those records.

Enter the following in mongo console

> new Date("0000-01-01T00:00:00.000+08:00")

ISODate("00-1-12-31T16:00:00Z")



 Comments   
Comment by Justin Seyster [ 25/Feb/20 ]

I'm going to close this for now. If there is a way to populate the database with values that can't be backed up and then restored with mongoexport and mongoimport, we should file a bug for that case, though. Morty, I'm still interested in more details if you have them. Thanks!

Comment by Justin Seyster [ 06/Feb/20 ]

mortyccp@gmail.com Sorry for the confusion. We actually did do the test with a date before the year zero but forgot to include it in the run where we pasted from the shell.

Are you definitely using mongoexport to output date documents? When I use mongoexport, I get a document that looks like this:

# MongoDB shell:
> db.invalid_date.find()
{ "_id" : ObjectId("5e3c4cac0fcd7edd92e48326"), "date" : ISODate("00-1-12-31T23:59:00Z") }
 
# Bash command line
$ mongoexport -d test -c invalid_date
{ "_id" : { "$oid" : "5e3c4cac0fcd7edd92e48326" }, "date" : { "$date" : { "$numberLong" : "-62167219260000" } } }
exported 1 records

The date field from mongoexport is formatted as a timestamp instead of a string, and restoring that to the database (as in Asya's example above) correctly inserts the date, even thought it's before the year 0.

Storing and querying dates before the year 0 is not supported, so you will probably need a workaround. That said, I expect that you will be able to back up and restore these dates using mongoexport and mongorestore, and I'd be interested to know if that case is still failing.

Comment by Morty Choi [ 06/Feb/20 ]

Your data set need to contain date value before year 0, for example Date("0000-01-01T00:00:00.000+08:00")

Comment by Asya Kamsky [ 05/Feb/20 ]

mortyccp@gmail.com I'm able to mongoexport and then mongoimport dates back without error.

Can you please provide more detail about what exactly is giving an error and maybe provide a sample document.

> db.olddates.find()
{ "_id" : ObjectId("5e3b217835f4f009ddea5aa2"), "date" : ISODate("0000-01-01T00:00:00Z"), "newDate" : ISODate("0-28-08-15T00:00:00Z") }
// at OS command line
mongoexport -d test -c olddates | mongoimport -d test -c oldnewdates --drop
 
> db.oldnewdates.find()
{ "_id" : ObjectId("5e3b217835f4f009ddea5aa2"), "date" : ISODate("0000-01-01T00:00:00Z"), "newDate" : ISODate("0-28-08-15T00:00:00Z") }

Comment by Danny Hatcher (Inactive) [ 02/Jan/20 ]

I'll forward this to the appropriate team to take a look.

Comment by Morty Choi [ 01/Jan/20 ]

I need to store user input with time before year 0. And I think the ISODate is wrong for negative year so it's already a bug what should be fixed.

In my chrome console, the following result is shown.

new Date("0000-01-01T00:00:00.000+08:00").toISOString()
"-000001-12-31T16:00:00.000Z"

And If I create date in mongo with the following, it will work just fine.

> new Date("-000001-12-31T16:00:00.000Z")

ISODate("00-1-12-31T16:00:00Z")

So I think it's not about we have a business case or not, it's just a bug need to be fixed.

Comment by Danny Hatcher (Inactive) [ 31/Dec/19 ]

Our docs mention that our date functions work from years 0 through 9999. Do you have a business case that you need to use years before 0? In the example you provided, if you're using UTC as the time zone instead of +0800 you will not have an issue.

Generated at Thu Feb 08 05:08:29 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.