DATE_TRUNC_MILLIS to month not working properly with DST timezones. Bug?

SQL++
,
1

Hi all,

I have ran into some strange problem when trimming dates into months. I’m doing a report of all events in single month so I’m trimming time of event (UTC millis) with DATE_TRUNC_MILLIS(timestamp, ‘month’).

Everything ran ok until now. Server is deployed in Slovenia TZ is ZULU+1 and with DST ZULU+2.

I have discovered that DATE_TRUNC_MILLIS does not return correct value when DST change occurs.

1603670400000 (2020-10-26T00:00:00Z) is truncated into 1601506800000 (2020-09-30T23:00:00Z) - NOT OK
while
1603670399000 (2020-10-25T23:59:59Z) is truncated into 1601510400000 (2020-10-01T00:00:00Z) - OK

I would expect that all values from 1601510400000 to 1604188799000 would result in 1601510400000.
Looks like this is some DST problem, since this happens only with dates in October and March.

DATE_TRUNC_STR works properly.
Looks like DATE_TRUNC_MILLIS for some reason uses local timezone with calculation and does some math incorrect.

Can you please point me at what I’m doing wrong?
I’m using CB 6.0.0 community build 1693.

Please see below example for more details.

SELECT 
CLOCK_STR() _1_current_time,
[
1603670400000, 
MILLIS_TO_UTC(1603670400000),
DATE_TRUNC_MILLIS(1603670400000, 'month'),
MILLIS_TO_UTC(DATE_TRUNC_MILLIS(1603670400000, 'month')),
DATE_TRUNC_STR(MILLIS_TO_UTC(1603670400000), 'month')
] _2_wrong,
[
1603670399000, 
MILLIS_TO_UTC(1603670399000),
DATE_TRUNC_MILLIS(1603670399000, 'month'),
MILLIS_TO_UTC(DATE_TRUNC_MILLIS(1603670399000, 'month')),
DATE_TRUNC_STR(MILLIS_TO_UTC(1603670399000), 'month')
] _3_correct

which results in

[
  {
    "_1_current_time": "2020-11-06T16:04:38.64+01:00",
    "_2_wrong": [
      1603670400000,
      "2020-10-26T00:00:00Z",
      1601506800000,
      "2020-09-30T23:00:00Z",
      "2020-10-01T00:00:00Z"
    ],
    "_3_correct": [
      1603670399000,
      "2020-10-25T23:59:59Z",
      1601510400000,
      "2020-10-01T00:00:00Z",
      "2020-10-01T00:00:00Z"
    ]
  }
]

Thank you.

2

The conversion happens where query service running and based on time setting of the machine. cc @isha

3

But why is TZ even considered in this function? Since DATE_TRUNC_MILLIS takes millis and returns millis, I would expect this function to work only with millis (UTC). Am I wrong to assume that?

And why does DATE_TRUNC_STR (which takes string that could have TZ) work properly?
I would like to avoid DATE_TRUNC_STR because it means I have to transform data twice, just to get the correct value.