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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/h2-database/7a98b52c-dc29-4ea4-90b1-be40f3aa8886n%40googlegroups.com.
