1

(tomcat8, jdk8, 5.5.41-MariaDB, mysql-connector-java-5.1.40-bin.jar, windows10)

I have this DATETIME value in a table, webapp client and db server in a same machine using an Europe/Helsinki system timezone.

DST +1h was applied at 2017-03-26 02:59:59 EET -> 2017-03-26 04:00:00 EEST. My database table has a value: 2017-03-26 03:54:03

HeidiSQL and mysql.exe consoles display a datetime value as 2017-03-26 03:54:03. It's a correct UTC value, wallclock would be 2017-03-26 06:54:03 value.

Mysql query select @@time_zone, @@system_time_zone gives SYSTEM,FLE Daylight Time result.

Jdbc resultset gives a value as 2017-03-26 04:54:03, this happens using a rs.getString("created_utc") or rs.getTimestamp("created_utc") getters. Converting this value to a wallclock is 2017-03-26 07:54:03 which is correct but was expecting to get original 03:XX:XX not 04:XX:XX stamp.

Also this one gives 2017-03-26 04:54:03 value but was expecting to have 03:54:03 time part.

Calendar cal = Calendar.getInstance(TimeZone.getTimeZone("GMT"));
java.sql.Timestamp ts = rs.getTimestamp("created_utc", cal);

Other hour stamps are fine but just this 03:XX:XX hourstamp is returned +1h too much. So jdbc value 04:01:00 to wallclock 07:01:00 works as expected. I would have guess all the timestamps would be one hour wrong but they are not. 00:XX:XX-02:XX:XX and 04:XX:XX-23:XX:XX works as expected.

I was able to fix this using an ugly hack but sure this cannot be the final answer? 

byte[] buf=rs.getBlob("created_utc").getBytes(1, (int)rs.getBlob("created_utc").length());
String sval = new String(buf, "ISO-8859-1");  // "2017-03-26 03:54:03" utc string
Calendar cal = DateUtil.fromUTC(sval); // "2017-03-26 06:54:03" system timezone

EDIT This was marked as a duplicate so I edit here my solution, problem was solved using a linked topic and comments in this question.

  • Use jdbc:mysql://127.0.0.1:3306/mydb?useUnicode=true&characterEncoding=utf8&useLegacyDatetimeCode=false jdbc string
  • Use rs.getTimestamp("started_utc", sqlUTC) getter for datetime field
Whome
  • 10,181
  • 6
  • 53
  • 65
  • Please [edit] your question to tell us what you get when you give the command `select @@time_zone, @@system_time_zone` to MySQL. `DATETIME` objects in MySQL don't carry timezone information. `TIMESTAMP` objects are always converted according to the `@@time_zone` setting. `NOW()` is a `TIMESTAMP`-style object. Ooops, you got kvell-hammared. – O. Jones May 05 '17 at 15:28
  • The timestamp in your database is **without** timezone information, and JDBC requires that it is interpreted - by default - in the JVM timezone, this means that the value `03:54:03` is interpreted in your timezone and not in UTC, and given the DST change at that date, it is transformed to `04:54:03`. – Mark Rotteveel May 05 '17 at 15:28
  • Thx, added timezone,systemtimezone query results. @MarkRotteveel what I do not understand '2017-03-26 04:33:04'->'2017-03-26 07:33:04' is fine, '2017-03-05 02:59:24'->'2017-03-05 04:59:24' is fine. But this one hourstamp '2017-03-26 03:54:03'->'2017-03-26 07:54:03' is always wrong. Heidisql and mysql.exe reads a value as 03:xx:xx as was inserted by another application sql insert query. – Whome May 05 '17 at 15:43
  • Or do you mean this happens at the time of DST+1h transformation hour, 02:59->04:00 wallclock "skips" 03:xx:xx stamp. I am now looking for how to store and read it as expected. Db must store them as utc datetime value strings. – Whome May 05 '17 at 15:48
  • That is basic timezones for you: 03:54 **does not exist** in your timezone on that date, but it is leniently transformed to 04:54. If you want to always use UTC, either use the MySQL Connector/J connection property to interpret the time in UTC **or** use the method taking a `Calendar` object set to timezone UTC. – Mark Rotteveel May 05 '17 at 15:48
  • I've edited a solution to the end of this topic, trick was to add a new jdbc url property and use getTimestamp(string,calendar) getter. – Whome May 05 '17 at 16:38

0 Answers0