6

Backstory

I recently came across a problem with saving a LocalTime to a TIME column in a MySQL database. Saving a value of 9:00 was causing 8:00 to be saved in the database.

This problem did not occur on my dev environment (Windows), but did occur on two Linux machines we tried.

My code was the following:

preparedStatement.setObject(parameterIndex, localTime, JDBCType.TIME);

After investigating, I eventually found that the MySQL JDBC driver was using java.sql.Time.valueOf(localTime), then using a SimpleDateFormat with its time zone set to the MySql server's system_time_zone system property (via time_zone = 'SYSTEM'), to print it as a string.

On the Linux machines, system_time_zone was GMT, and on the Windows machine, it was GMT Standard Time. (I am in the UK.)

So, the LocalTime 9:00 was converted to a Time of 9:00 Europe/London (actually modelled internally as 1st January 1970 9:00 Europe/London), and this was then written out using the GMT timezone, which is treated the same as UTC. Now, usually those two timezones have a zero offset from UTC in January, but the UK remained in daylight saving time for the whole year in 1969 and 1970. So an hour got subtracted.

Question

What is the best way to write a LocalTime to a MySQL database, avoiding this kind of problem? I just want 9:00, no time zone, no date, just the time of day. I don't want it to adjust for a difference in time zone, because it's just 9:00, not on any particular day or place.

At the moment I'm just printing the LocalTime as a string, and sending that to the database, but I feel there should be a better way. Is there?

Ole V.V.
  • 81,772
  • 15
  • 137
  • 161
PMah
  • 686
  • 5
  • 24
  • what happens if you save as string value `09:00` – Krish Dec 11 '18 at 15:02
  • When I use `preparedStatement.setObject(parameterIndex, "09:00:00");`, it saves as `9:00` in the database. – PMah Dec 11 '18 at 15:07
  • (That is the behaviour I want.) – PMah Dec 11 '18 at 15:47
  • I propose to have a function that returns string value for the local time using any formatter you use in Java – Krish Dec 11 '18 at 16:14
  • Ok, that's sort of what I'm doing; I'm calling a newly created function `TimeHelper.getTimeString(localTime)`. (Is that what you mean?) I just hoped there would be a way without converting to a string first... – PMah Dec 14 '18 at 09:35

0 Answers0