3

Intuitively these two intervals represent the same amount of time. But the difference shows around daylight-saving time changes, in which case "1 day" can mean either "23 hours" in the spring or "25 hours" in the autumn.

I tested with PostgreSQL and there these two intervals don't mean the same amount of time:

set timezone TO 'CET';
SELECT timestamp with time zone'2020-03-29 0:00 Europe/Bratislava' + INTERVAL '1' DAY,
       timestamp with time zone'2020-03-29 0:00 Europe/Bratislava' + INTERVAL '24' HOUR;

returns this:

2020-03-29T22:00:00.000Z    2020-03-29T23:00:00.000Z

The client's time zone was UTC, that's why the returned values are in UTC. But important is that they are not the same.

I also tried with MySQL but seems to me that it doesn't support time zones, only time zone offsets. And zone offsets don't have DST changes so a day is always 24 hours.

On the other hand Apache Calcite, that backs many SQL implementations such as Apache Drill, Apache Flink, Apache Beam and many more, represents interval literals as java's BigDecimal: the day-second interval is converted to milliseconds and day is assumed to always be 24 hours.

My question is: which approach is correct according to the SQL standard?

EDIT:

Checked more DBs:

  • Oracle: SELECT INTERVAL '1' DAY FROM DUAL returns +01 00:00:00. Adding either 1 day or 24 hours to 2020-03-29 0:00 CET provides the same result: 24 hours are added.
  • SQL Server and DB2: as far as I can tell, only time zone offsets are supported. So same case as MySql: they don't support time zones with DST changes.

Conclusion: PostgreSQL seems to be the only exception to have 1 day different from 24 hours.

Oliv
  • 10,221
  • 3
  • 55
  • 76
  • Very good question +1 – The Impaler Apr 29 '20 at 15:15
  • The SQL standard mentions timestamp with "time zones". I don't recall it mentioning "time offsets". Considering this, I would say those two expressions should be considered different. It seems the Calcite, Drill, and others reduce the time to a UTC time, or similar. That's still good, as long as we all understand that they don't pretend to handle time zones. – The Impaler Apr 29 '20 at 15:21
  • @TheImpaler An example of time zone is "CET" (Central-European Time). Its offset is +1 in winter and +2 in summer. It's a property of the zone when the offset changes. If your timestamp contains a zone like this, it should take the changes into account. If it contains just the offset, then no changes over the year occur. Java has `ZoneId` and `ZoneOffset` classes to represent this. `ZoneOffset` is a subclass of `ZoneId` so each `ZoneOffset` is a `ZoneId`, but not the other way around. – Oliv Apr 30 '20 at 07:14
  • 1
    @GordonLinoff: yes, but if the day only has 23 hours adding 24 hours will yield a result that is "one hour later" the next day (slightly equivalent to adding 25 hours). If I start a timer at `2020-03-29 00:00` and wait 24 hours, the clock will then show `2020-03-30 01:00` (at least where I live) –  Apr 30 '20 at 07:53
  • Things like that are exactly the reason why we should keep all timestamps in UTC and only apply a timezone on the client side. Try `SELECT ('2020-03-29 0:00 Europe/Bratislava'::timestamptz AT TIME ZONE 'UTC' + INTERVAL '24' HOUR) AT TIME ZONE 'Europe/Bratislava', ('2020-03-29 0:00 Europe/Bratislava'::timestamptz AT TIME ZONE 'UTC' + INTERVAL '1' DAY) AT TIME ZONE 'Europe/Bratislava'` – IVO GELOV Apr 30 '20 at 07:55
  • @IVOGELOV which is exactly what Postgres' `timestamp WITH time zone` type does (and which Oliv used in the question) –  Apr 30 '20 at 08:52
  • I am a Calcite committer, and I know that Calcite treats 1 day as 24 hours. (For both, the value is stored internally as 86,400,000 milliseconds.) The more important question is, should Calcite treat them the same? In Calcite we try to stick to the SQL standard, so what does the standard say? It would be great if you log a Calcite case to track this. – Julian Hyde Apr 30 '20 at 18:08
  • @a_horse_with_no_name The point is that at "Europe/Bratislava" some days are shorter than 24hr and some days are longer while at UTC all days are 24hr long. So if you are at "Europe/Bratislava" and want to add 24hr to a timestamp but do not want DST to skew your counting - you will have to do the calculation in UTC and then return back to "Europe/Bratislava". – IVO GELOV May 01 '20 at 17:11

1 Answers1

3

I think your answer lies in this resource: https://www.postgresql.org/docs/9.1/functions-datetime.html

When adding an interval value to (or subtracting an interval value from) a timestamp with time zone value, the days component advances (or decrements) the date of the timestamp with time zone by the indicated number of days. Across daylight saving time changes (with the session time zone set to a time zone that recognizes DST), this means interval '1 day' does not necessarily equal interval '24 hours'. For example, with the session time zone set to CST7CDT, timestamp with time zone '2005-04-02 12:00-07' + interval '1 day' will produce timestamp with time zone '2005-04-03 12:00-06', while adding interval '24 hours' to the same initial timestamp with time zone produces timestamp with time zone '2005-04-03 13:00-06', as there is a change in daylight saving time at 2005-04-03 02:00 in time zone CST7CDT.

kate93422
  • 31
  • 3