[SERVER-71212] $densify does not handle beginning / end of daylight saving time / summer time Created: 09/Nov/22  Updated: 21/Nov/22  Resolved: 18/Nov/22

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

Type: Bug Priority: Major - P3
Reporter: Gianluca Nitti Assignee: Yuan Fang
Resolution: Done Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Operating System: ALL
Steps To Reproduce:

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") }

 

Participants:

 Description   

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.



 Comments   
Comment by Gianluca Nitti [ 21/Nov/22 ]

Hello,

 

thanks for your response, I now posted the feature suggestion here.

Comment by Yuan Fang [ 18/Nov/22 ]

Hi gianluca.nitti@voismart.it,

Thank you for your report. This seems to be a feature/enhancement request on handling time increment in the $densify pipeline stage on a date field. I can see how this behavior would be valuable for handling time zone change along with Daylight Saving Time. However, this JIRA project is for bug reports, but you can help get this on the radar by submitting a feature request to our MongoDB Feedback Engine.

You may also want to search and post on the MongoDB Developer Community Forums, as it's possible there are others who have guidance on how to satisfy your use case.

Thank you!

Regards,
Yuan

Generated at Thu Feb 08 06:18:21 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.