[SERVER-40727] $dateToString %G format specifier results in unexpected year Created: 19/Apr/19  Updated: 27/Oct/23  Resolved: 02/May/19

Status: Closed
Project: Core Server
Component/s: Aggregation Framework
Affects Version/s: 4.0.9
Fix Version/s: None

Type: Bug Priority: Major - P3
Reporter: Faisan TIE Assignee: Backlog - Query Team (Inactive)
Resolution: Works as Designed Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Attachments: PNG File Screenshot 2019-04-22 at 23.14.58.png    
Assigned Teams:
Query
Operating System: ALL
Participants:

 Description   

$dateToString with the %G format specifier results in an unexpected year value. %Y seems normal.

// Date()
> new Date(1546300799999)
ISODate("2018-12-31T23:59:59.999Z")
 
// $dateToString with %G
> db.test.aggregate([{$project:{date: {$dateToString: {format:"%G-%m-%d %H:%M:%S.%L", date:{$toDate: 1546300799999}}}}}])
{ "_id" : ObjectId("5cbf7ca5e176254eb1d8be0c"), "date" : "2019-12-31 23:59:59.999" }

original description

When dealing with datetimes specified as milliseconds since the Unix epoch, it gives inconsistent results whatever helper function or operator is used. For instance, both the following expression inconsistently evaluate to 1970-01-01T00:00:00Z :

  • new Date(0)
  • new Date(31536000000) // Normally should give 1971-01-01T00:00:00Z

ISODate() helper and $toDate operator would give le same inconsistent result. This issue also leads to wrong results when performing calculations on datetime values. For instance, the following expression which is supposed to subtract one second from date 2019-01-01, wrongly results to date 2019-12-31 instead of date 2018-12-31 

  • {$subtract: [ISODate("2019-01-01"), 1000]}

 



 Comments   
Comment by Justin Seyster [ 02/May/19 ]

Sorry for the confusion! These outputs are all actually correct, but they look strange because the format specifiers mix the %G "week-numbered year" with the regular %m month and %d day specifiers. The phrase "ISO 8601 format" in the documentation refers to a way of specifying dates that breaks them up by week number and day number instead of the traditional month and day. Years in this format always begin on Monday, so on years where December 31 is not a Sunday, it gets treated as being part of the next year by the ISO 8601 calendar.

Using the example above:
> db.test.aggregate([{$project:{date: {$dateToString: {format:"%G-%V-%u %H:%M:%S.%L", date:{$toDate: 1546300799999}}}}}])

{ "_id" : ObjectId("5ccb2e277e56efe6e51741c8"), "date" : "2019-01-1 23:59:59.999" }

Here you can see this date is considered the first day (Monday) of the first week of 2019 by ISO 8601. Using the normal calendar (%Y-%m-%d) treats it as the 31st day of the 12th month of 2018, but it's still the same day.

Even though it has "ISO" in the name, the ISODate constructor uses the standard calendar for parsing, which is why parsing with ISODate and then formatting with %G can show different years for days near the end of the calendar year, as in the example here. The name "ISODate" refers to ISO standardized dates but not specifically to ISO 8601.

To avoid problems, users should avoid mixing the %G, %V, %u ISO 8601 format specifiers with other date specifiers like %m and %d. Week-numbered years are used in some fiscal and government applications, but most users will want the normal calendar year provided by %Y.

Wikipedia has a good explanation of week-numbered years: https://en.wikipedia.org/wiki/ISO_week_date

Comment by Faisan TIE [ 24/Apr/19 ]

Thanks a lot @Eric Sedor, %Y date format specifier works fine of course. And I should recon it's more intuitive than %G specifier. I preferred %G as it's ISO standard and also because zero padded year with %Y would look ugly to me. Yet, I'm not supposed to manage years back to the 21st century, so %Y is virtually fine for me too.  I'm happy. Thanks again!

Comment by Eric Sedor [ 23/Apr/19 ]

Thanks for clarifying, faisan; This appears introduced by the use of the %G format specifier with $dateToString specifically...

// Date()
> new Date(1546300799999)
ISODate("2018-12-31T23:59:59.999Z")
 
// $dateToString without format
> db.test.aggregate([{$project:{date: {$dateToString: {date:{$toDate: 1546300799999}}}}}])
{ "_id" : ObjectId("5cbf7ca5e176254eb1d8be0c"), "date" : "2018-12-31T23:59:59.999Z" }
 
// $dateToString with %G
> db.test.aggregate([{$project:{date: {$dateToString: {format:"%G-%m-%d %H:%M:%S.%L", date:{$toDate: 1546300799999}}}}}])
{ "_id" : ObjectId("5cbf7ca5e176254eb1d8be0c"), "date" : "2019-12-31 23:59:59.999" }
 
// $dateToString with %Y
> db.test.aggregate([{$project:{date: {$dateToString: {format:"%Y-%m-%d %H:%M:%S.%L", date:{$toDate: 1546300799999}}}}}])
{ "_id" : ObjectId("5cbf7ca5e176254eb1d8be0c"), "date" : "2018-12-31 23:59:59.999" }

We are looking into this.

Comment by Faisan TIE [ 22/Apr/19 ]

@Eric Sedor, I've just attached a Studio 3T screenshot showing a demonstration of the issue. Hopefully it should help. The query has been executed against a version 4.0.6 server.

Comment by Eric Sedor [ 22/Apr/19 ]

faisan, can you please provide shell output that demonstrates the incorrect results you are seeing? Initially, we aren't able to reproduce this.

Generated at Thu Feb 08 04:55:48 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.