Date_diff, shows negative time diff when not supposed to, because of different time zones? #9673
Description
What happens?
I have two columns populated by timestamps with time zones, starttime, recordtime. I want to find the difference between recordtime and starttime in minutes, and I notice that I give several columns with a negative value even though every recordtime is supposed to be equal or after starttime. It can seem like datediff does not handle time zones that well.
Note: These two columns are in different time zones
To Reproduce
#Setting up table
CREATE TABLE datediffTest(starttime TIMESTAMPTZ, recordtime TIMESTAMPTZ);
INSERT INTO datediffTest VALUES ('2022-10-30 02:17:00+02', '2022-10-30 02:00:21+01');
INSERT INTO datediffTest VALUES ('2021-10-31 02:39:00+02', '2021-10-31 02:38:20+01');
#Selecting the dates as GMT for readability
SELECT starttime AT TIME ZONE 'GMT' as starttime, recordtime AT TIME ZONE 'GMT' as recordtime, starttime>recordtime, date_diff('minute', starttime, recordtime) FROM datediffTest
│ starttime │ recordtime │ (starttime > recor… │ date_diff('minute', … │ (recordtime - starttime) │
│ timestamp │ timestamp │ boolean │ int64 │ interval │
├─────────────────────┼─────────────────────┼──────────────────────┼────────────────────────┼──────────────────────────┤
│ 2022-10-30 00:17:00 │ 2022-10-30 01:00:21 │ false │ -17 │ 00:43:21 │
│ 2021-10-31 00:39:00 │ 2021-10-31 01:38:20 │ false │ -1 │ 00:59:20 │
Obviously it cant both be true that starttime is not larger than recordtime and there is a negative amount of minutes between starttime and recordtime. Calculating recordtime-starttime also shows a positive value, proving that date_diff seems timezone unaware.
If I repeat the wery but manipulate the strings to have the same time zone I get the same results for date_diff - which is now correct. This indicates that date_diff is not handling the time zone difference between the two columns.
#Setting up table
CREATE TABLE datediffTest(starttime TIMESTAMPTZ, recordtime TIMESTAMPTZ);
INSERT INTO datediffTest VALUES ('2022-10-30 02:17:00+02', '2022-10-30 02:00:21+02');
INSERT INTO datediffTest VALUES ('2021-10-31 02:39:00+02', '2021-10-31 02:38:20+02');
#Selecting the dates as GMT for readability
SELECT starttime AT TIME ZONE 'GMT' as starttime, recordtime AT TIME ZONE 'GMT' as recordtime, starttime>recordtime, date_diff('minute', starttime, recordtime), recordtime-starttime FROM datediffTest
│ starttime │ recordtime │ (starttime > recor… │ date_diff('minute', … │ (recordtime - starttime) │
│ timestamp │ timestamp │ boolean │ int64 │ interval │
├─────────────────────┼─────────────────────┼──────────────────────┼────────────────────────┼──────────────────────────┤
│ 2022-10-30 00:17:00 │ 2022-10-30 00:00:21 │ true │ -17 │ -00:16:39 │
│ 2021-10-31 00:39:00 │ 2021-10-31 00:38:20 │ true │ -1 │ -00:00:40 │
Date_diff seems completely time zone unaware without this being stated in the documentation.
Alternative solution to mitigate this issue:
For everyone who might have the same problem, here is a solution that seem to give the correct result. Note that I have not tested this thoroughly, but for this case it seems to work. **Be aware that this gives you the minute part of the difference, not the total difference in minutes. If it is 1 day and 5 minutes between your dates, you will get 5 minutes by using this. To get the full difference in minutes the best might be to extract all the dateparts between your two dates and convert them to minutes.
CREATE TABLE datediffTest(starttime TIMESTAMPTZ, recordtime TIMESTAMPTZ);
INSERT INTO datediffTest VALUES ('2022-10-30 02:17:00+02', '2022-10-30 02:00:21+01');
INSERT INTO datediffTest VALUES ('2021-10-31 02:39:00+02', '2021-10-31 02:38:20+01');
SELECT date_diff('minute', recordtime, starttime), recordtime-starttime, date_part('minute', recordtime-starttime) FROM datediffTest
│ date_diff('minute', recordtime, startti… │ (recordtime - starttime) │ date_part('minute', (recordtime - starttime)) │
│ int64 │ interval │ int64 │
│ 17 │ 00:43:21 │ 43 │
│ 1 │ 00:59:20 │ 59 │
OS:
Ubuntu x64
DuckDB Version:
v0.9.1
DuckDB Client:
Python
Full Name:
Bjørnar Brende Smestad
Affiliation:
NTNU
Have you tried this on the latest main branch?
I have tested with a main build
Have you tried the steps to reproduce? Do they include all relevant data and configuration? Does the issue you report still appear there?
- Yes, I have