Uploaded image for project: 'Core Server'
  1. Core Server
  2. SERVER-71212

$densify does not handle beginning / end of daylight saving time / summer time

XMLWordPrintableJSON

    • Type: Icon: Bug Bug
    • Resolution: Done
    • Priority: Icon: Major - P3 Major - P3
    • None
    • Affects Version/s: None
    • Component/s: None
    • None
    • ALL
    • Hide

      Add some documents with a date field to a collection:

      db.densifyDateExample.insertMany([
    {_id: "a", d: ISODate("2022-10-28T22:00:00Z")},
    {_id: "b", d: ISODate("2022-10-29T22:00:00Z")},
    {_id: "c", d: ISODate("2022-10-30T23:00:00Z")},
    {_id: "d", d: ISODate("2022-10-31T23:00:00Z")},
    {_id: "e", d: ISODate("2022-11-01T23:00:00Z")},
    {_id: "f", d: ISODate("2022-11-02T23:00:00Z")},
])

      Keep in mind that on 2022-10-30, in EU countires, the time switched from CEST to CET. SO those are the UTC representations of the beginning of the days from Oct 29th to Nov 3rd.

      Now, if we run the following pipeline:

      db.densifyDateExample.aggregate([{
    $densify: {
        "field": "d",
        "range": {
            "bounds": [ISODate("2022-10-25T22:00:00.000Z"), ISODate("2022-11-06T23:00:00.000Z")],
            "step": 1,
            "unit": "day"
        }
    }
}])

      We get the following result:

      { "d" : ISODate("2022-10-25T22:00:00Z") }
{ "d" : ISODate("2022-10-26T22:00:00Z") }
{ "d" : ISODate("2022-10-27T22:00:00Z") }
{ "_id" : "a", "d" : ISODate("2022-10-28T22:00:00Z") }
{ "_id" : "b", "d" : ISODate("2022-10-29T22:00:00Z") }
{ "d" : ISODate("2022-10-30T22:00:00Z") }
{ "_id" : "c", "d" : ISODate("2022-10-30T23:00:00Z") }
{ "d" : ISODate("2022-10-31T22:00:00Z") }
{ "_id" : "d", "d" : ISODate("2022-10-31T23:00:00Z") }
{ "d" : ISODate("2022-11-01T22:00:00Z") }
{ "_id" : "e", "d" : ISODate("2022-11-01T23:00:00Z") }
{ "d" : ISODate("2022-11-02T22:00:00Z") }
{ "_id" : "f", "d" : ISODate("2022-11-02T23:00:00Z") }
{ "d" : ISODate("2022-11-03T22:00:00Z") }
{ "d" : ISODate("2022-11-04T22:00:00Z") }
{ "d" : ISODate("2022-11-05T22:00:00Z") }
{ "d" : ISODate("2022-11-06T22:00:00Z") }

      While - after somehow telling $densify which timezone we are interested in - the following would be way more useful:

      { "d" : ISODate("2022-10-25T22:00:00Z") }
{ "d" : ISODate("2022-10-26T22:00:00Z") }
{ "d" : ISODate("2022-10-27T22:00:00Z") }
{ "_id" : "a", "d" : ISODate("2022-10-28T22:00:00Z") }
{ "_id" : "b", "d" : ISODate("2022-10-29T22:00:00Z") }
{ "_id" : "c", "d" : ISODate("2022-10-30T23:00:00Z") }
{ "_id" : "d", "d" : ISODate("2022-10-31T23:00:00Z") }
{ "_id" : "e", "d" : ISODate("2022-11-01T23:00:00Z") }
{ "_id" : "f", "d" : ISODate("2022-11-02T23:00:00Z") }
{ "d" : ISODate("2022-11-03T23:00:00Z") }
{ "d" : ISODate("2022-11-04T23:00:00Z") }
{ "d" : ISODate("2022-11-05T23:00:00Z") }
{ "d" : ISODate("2022-11-06T23:00:00Z") }

       

      Show
      Add some documents with a date field to a collection: db.densifyDateExample.insertMany([     {_id: "a" , d: ISODate( "2022-10-28T22:00:00Z" )},     {_id: "b" , d: ISODate( "2022-10-29T22:00:00Z" )},     {_id: "c" , d: ISODate( "2022-10-30T23:00:00Z" )},     {_id: "d" , d: ISODate( "2022-10-31T23:00:00Z" )},     {_id: "e" , d: ISODate( "2022-11-01T23:00:00Z" )},     {_id: "f" , d: ISODate( "2022-11-02T23:00:00Z" )}, ]) Keep in mind that on 2022-10-30, in EU countires, the time switched from CEST to CET. SO those are the UTC representations of the beginning of the days from Oct 29th to Nov 3rd. Now, if we run the following pipeline: db.densifyDateExample.aggregate([{     $densify: {         "field" : "d" ,         "range" : {             "bounds" : [ISODate( "2022-10-25T22:00:00.000Z" ), ISODate( "2022-11-06T23:00:00.000Z" )],             "step" : 1,             "unit" : "day"         }     } }]) We get the following result: { "d" : ISODate( "2022-10-25T22:00:00Z" ) } { "d" : ISODate( "2022-10-26T22:00:00Z" ) } { "d" : ISODate( "2022-10-27T22:00:00Z" ) } { "_id" : "a" , "d" : ISODate( "2022-10-28T22:00:00Z" ) } { "_id" : "b" , "d" : ISODate( "2022-10-29T22:00:00Z" ) } { "d" : ISODate( "2022-10-30T22:00:00Z" ) } { "_id" : "c" , "d" : ISODate( "2022-10-30T23:00:00Z" ) } { "d" : ISODate( "2022-10-31T22:00:00Z" ) } { "_id" : "d" , "d" : ISODate( "2022-10-31T23:00:00Z" ) } { "d" : ISODate( "2022-11-01T22:00:00Z" ) } { "_id" : "e" , "d" : ISODate( "2022-11-01T23:00:00Z" ) } { "d" : ISODate( "2022-11-02T22:00:00Z" ) } { "_id" : "f" , "d" : ISODate( "2022-11-02T23:00:00Z" ) } { "d" : ISODate( "2022-11-03T22:00:00Z" ) } { "d" : ISODate( "2022-11-04T22:00:00Z" ) } { "d" : ISODate( "2022-11-05T22:00:00Z" ) } { "d" : ISODate( "2022-11-06T22:00:00Z" ) } While - after somehow telling $densify which timezone we are interested in - the following would be way more useful: { "d" : ISODate( "2022-10-25T22:00:00Z" ) } { "d" : ISODate( "2022-10-26T22:00:00Z" ) } { "d" : ISODate( "2022-10-27T22:00:00Z" ) } { "_id" : "a" , "d" : ISODate( "2022-10-28T22:00:00Z" ) } { "_id" : "b" , "d" : ISODate( "2022-10-29T22:00:00Z" ) } { "_id" : "c" , "d" : ISODate( "2022-10-30T23:00:00Z" ) } { "_id" : "d" , "d" : ISODate( "2022-10-31T23:00:00Z" ) } { "_id" : "e" , "d" : ISODate( "2022-11-01T23:00:00Z" ) } { "_id" : "f" , "d" : ISODate( "2022-11-02T23:00:00Z" ) } { "d" : ISODate( "2022-11-03T23:00:00Z" ) } { "d" : ISODate( "2022-11-04T23:00:00Z" ) } { "d" : ISODate( "2022-11-05T23:00:00Z" ) } { "d" : ISODate( "2022-11-06T23:00:00Z" ) }  

      When using "day" as "unit" for a $densify pipeline stage on a date field, the date is always advanced of 24 hours. This is however not always the expected result in timezones in which the year has one 23-hour and one 25-hour long day.

      Would it be possible to support passing an optional timezone parameter in the $densify stage and, when present, account for these exceptions when appropriate?

       

      See "steps to reproduce" for an example.

            Assignee:
            yuan.fang@mongodb.com Yuan Fang
            Reporter:
            gianluca.nitti@voismart.it Gianluca Nitti
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

              Created:
              Updated:
              Resolved: