Problems with DATEADD, DATEDIFF and daylight savings time

[Hedley Proctor]

I have a database query that truncates milliseconds off database timestamp values using the following logic:

DATEADD(second, DATEDIFF(second, '2000-01-01', zas.tstamp), '2000-01-01')

This has been failing since Britain switched to daylight savings time at the weekend. In the expression, the date '2000-01-01' is just an arbitrary value. The expression calculates the difference between that date and the database column of interest, but only to seconds precision, then creates a new date by adding this difference back to the same arbitrary value. This should have the affect of truncating the milliseconds. I chose this because it is recommended on stack overflow as a reasonably portable way of writing this logic.

However, on H2, running this now (in daylight savings time) has the affect of adding an hour to the time! i.e. if I run

DATEADD(second, DATEDIFF(second, '2000-01-01', '2016-03-31'), '2000-01-01')

you would expect to get back

2016-03-31 00:00:00

but in fact you get back

2016-03-31 01:00:00

By contrast, it runs as expected on SQL server.

This looks like a bug to me?

Hedley

[Noel Grandin]

Could you figure out which part of the computation is returning bad data? The DATEDIFF or the DATEADD? Should be possible by testing near the DST switchover datetime.

Which version of H2 are you using? And are you running an up-to-date Java, so it's DST database is current?

​

[Florian Bastien-Dorville]

Hello,

I encountered a similar issue today, after France switched to daylight savings time yesterday, and I was only using DATEADD, not DATEDIFF.

I have tests that use a H2 db, and the data set is populated with timestamps relative to the current timestamp, using expressions such as DATEADD(DAY, -3, CURRENT_TIMESTAMP).

Then there are some assertions about timestamps returned by queries, however these assertions failed today because of the 1h shift.

Let's say current timestamp is 2023-03-27 18:00:00, and assertion expects a query to return a timestamp at same time of day 3 days ago, then the (computed) expected timestamp in Java would correctly be 2023-03-24 18:00:00, while the actual timestamp returned by H2 would incorrectly be 2023-03-24 17:00:00 (thus not taking into account the switch from CET to CEST on 2023-03-26, as if it were just subtracting the product of the number of days by the number of millis in a 24h day).

I'm using H2 version 2.0.206 and Java 8.

My Java's DST database seems to be up-to-date since the expected timestamp (instantiated in Java test) successfully takes into account DST (using standard Calendar class to compute it).

[Evgenij Ryazanov]

Hello!

I have tests that use a H2 db, and the data set is populated with timestamps relative to the current timestamp, using expressions such as DATEADD(DAY, -3, CURRENT_TIMESTAMP).

 

Let's say current timestamp is 2023-03-27 18:00:00

CURRENT_TIMESTAMP can't return this value, it returns a TIMESTAMP WITH TIME ZONE value. Most likely 2023-03-24 18:00:00+02 was returned.

 

, and assertion expects a query to return a timestamp at same time of day 3 days ago, then the (computed) expected timestamp in Java would correctly be 2023-03-24 18:00:00

DATEADD with TIMESTAMP WITH TIME ZONE argument also returns a TIMESTAMP WITH TIME ZONE value with the same time zone offset.

DATEADD(DAY, -3, TIMESTAMP WITH TIME ZONE '2023-03-27 18:00:00+02' returns 2023-03-24 18:00:00+02 (and this value is actually equal to 2023-03-24 17:00:00+01)

TimeZone.setDefault(TimeZone.getTimeZone("Europe/Paris"));

try (Connection c = DriverManager.getConnection("jdbc:h2:mem:1")) {

Statement s = c.createStatement();

s.execute("SET TIME ZONE 'Europe/Paris'");

ResultSet rs = s.executeQuery("VALUES DATEADD(DAY, -3, TIMESTAMP WITH TIME ZONE '2023-03-27 18:00:00+02')");

rs.next();

System.out.println(rs.getObject(1)); // Returns OffsetDateTime

System.out.println(rs.getObject(1, LocalDateTime.class)); // Implicit conversion to TIMESTAMP data type

System.out.println(rs.getTimestamp(1)); // Legacy method, shouldn't be used in modern applications, especially for TIMESTAMP WITH TIME ZONE data type

}

2023-03-24T18:00+02:00
2023-03-24T17:00
2023-03-24 17:00:00.0

If you want to perform date-time arithmetic with your local time zone, you need to use the TIMESTAMP data type instead. Current value of this data type is returned by the standard LOCALTIMESTAMP function.

TimeZone.setDefault(TimeZone.getTimeZone("Europe/Paris"));

try (Connection c = DriverManager.getConnection("jdbc:h2:mem:1")) {

Statement s = c.createStatement();

s.execute("SET TIME ZONE 'Europe/Paris'");

ResultSet rs = s.executeQuery("VALUES DATEADD(DAY, -3, TIMESTAMP '2023-03-27 18:00:00')");

rs.next();

System.out.println(rs.getObject(1, LocalDateTime.class));

System.out.println(rs.getTimestamp(1)); // Legacy method, shouldn't be used in modern applications

}

2023-03-24T18:00
2023-03-24 18:00:00.0

Please note that TIMESTAMP data type has a natural limitation, it cannot distinguish 2023-10-29 02:00:00 CEST and 2023-10-29 02:00:00 CET, because it doesn't have any time zone information.