Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Won't Do
-
3.0.4
-
None
Description
Hi,
moving from v2.7.5 to v3.0.4 raises a bug in our system where a queried TIMESTAMP column returns java.sql.Timestamp with wrong time zone.
Flow:
1. DB server runs with @@global.time_zone = SYSTEM and system_time_zone = UTC.
2. insert TIMESTAMP value using CURRENT_TIMESTAMP/Now() - when observing the DB using external tool (i.e. DBeaver which uses 2.7.1 driver) shows the timestamp column has correct value (the current time in UTC, i.e. 13:00:00)
3. from a machine/jvm with different time zone (i.e. GMT+2) and driver version 3.0.4:
a. query for the TIMESTAMP column using jdbcTemplate
b. observer the returned value (java.sql.Timestamp type) - the time is the same as in DB but with GMT+2 timezone (i.e. 13:00:00+0200)
4. repeating above but in step 3 using driver v2.7.5 returns the time as expected (i.e. 15:00:00+0200)
Observing the diff between v3.0.4 and v2.7.5 - it seems that 3.0.4 has a bug in Result.java:getObject(int columnIndex): the impl always pass cal: null which eventually cause the TimestampCodec to fill DB UTC value into local timezone colander.
In v2.7.5 the implementation is different and the SelectResultSet.java:getObject pass timeZone parameter correctly.
Attachments
Activity
Bhagya and what are your timezone settings ?
It would seems timezone is not set for 3.1.1, and it would have to be set, like '&timezone=GMT+5' or to value 'auto'
There is always some configuration to retrieved data right.
When having a client with a timezone that differ from server:
- With 2.7 connector, configuration can be "&useLegacyDatetimeCode=false" or even "&serverTimezone=UTC&useLegacyDatetimeCode=false"
- with 3.1 : "timezone=auto"
for example with a client in GMT+2 and an UTC server :
Server contains a table "test" with a single date value "2023-06-07 13:20:57", inserted directly from server.
- with 2.7 connector :
try (Connection con = DriverManager.getConnection("jdbc:mariadb://xx.xx.xx.xx/testj?user=xx&serverTimezone=UTC&useLegacyDatetimeCode=false")) { |
Statement stmt = con.createStatement();
|
ResultSet rs = stmt.executeQuery("SELECT * FROM test"); |
while (rs.next()) { |
System.out.println(rs.getTimestamp(1)); |
// 2023-06-07 15:20:57.0 |
System.out.println(rs.getTimestamp(1).toInstant()); |
// 2023-06-07T13:20:57Z |
}
|
}
|
using for connector 3.1.4 and replacing "&serverTimezone=UTC&useLegacyDatetimeCode=false" by "timezone=auto" will return the same exact values.
sorry for late response, i've missed the notification
1. Same scenario as explained in the bug
@@global.time_zone = SYSTEM and system_time_zone = UTC
2. Insert from spring using *.sql file (machine with different time zone (i.e. GMT+5))
INSERT INTO TEST (creation_time) VALUES ('2022-04-12 15:22:20.000');
3. From the same machine/jvm where insert was executed. On performing
jdbcTemplate.query("select * from TEST", (rs, rowNum) ->
new Tes().creationTime(rs.getTimestamp("creation_time").toInstant()));
2.7.x - Time stamp inserted according to session time zone
rs.getTimestamp("creation_time"): 2022-04-12 20:52:20.0
rs.getTimestamp("creation_time").toInstant(): 2022-04-12T15:22:20Z
3.1.1 - Time stamp not corrected according to insert time zone
rs.getTimestamp("creation_time"): 2022-04-12 15:22:20.0
rs.getTimestamp("creation_time").toInstant(): 2022-04-12T09:52:20Z
Shouldn't timestamp be corrected same as in 2.7? Is this not breaking backward?