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.
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/h2-database/7a98b52c-dc29-4ea4-90b1-be40f3aa8886n%40googlegroups.com.
