Value from MySQL TIME to Java Date not translating correctly

Question

In my MySQL database I have a table with three columns like below

id | name | notification_time
-----------------
1  | name1 | 00:30:00
2  | name2 | 01:30:00
3  | name3 | 01:00:00

notification_time is a datatype of TIME.

In my Spring-boot app, to model the table I have

@Entity
@Table(name = "table")
public class TableEntity implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private Integer id;

    @Column(name = "name")
    private String name;

    @Column(name = "notification_time")
    @Temporal(TemporalType.TIME)
    private Date notificationTime;

And to retrieve the values I simply retrieve all entries through my repository

tableRepository.findAll();

When I retrieve the notification_time from the database, the value I see for each is

19:30:00
20:30:00
20:00:00

so it appears to being offsetting the time from the value in my table but 19 hours? I'd like to preserve the values from my table instead (ie 00:30:00, 01:30:00, 01:00:00). Is there a reason for this behavior, and a way to correct this?

Your app and your MySQL database are running on different time zones. What's the type of the column? `DATETIME` or `TIMESTAMP`? — The Impaler, Sep 20 '19 at 18:40
In which timezone you app is running? and in which time zone your database is there? — Ryuzaki L, Sep 20 '19 at 18:52
I suppose you should consider -5 hours of time (zone) difference instead of +19 hours. — Barbaros Özhan, Sep 20 '19 at 18:56
As a rule of thumb I always keep my database timezone to UTC then convert to local time when displaying it on the app — DanielRead, Sep 20 '19 at 19:31
The MySQL Connector/J JDBC driver automatically does timezone conversion, when the timezone of the database server differs from the timezone of the client. personally, i think the JDBC connection is the *wrong* place in the application to be handling timezone conversions. Somewhat related discussion found in this question: https://stackoverflow.com/questions/29727852/retrieving-utc-datetime-field-from-mysql-in-java-when-server-timezone-is-not-utc — spencer7593, Sep 20 '19 at 20:42
JDBC timezone behavior varies by version of Connector/J. Older versions of Connector/J supported properties `noTimezoneConversionForTimeType`, `useJDBCCompliantTimezoneShift`, `useLegacyDatetimeCode`, `useTimezone` et al. most of which don't appear to be documented in the latest release. (We converted from the old MySQL Connector/J to MariaDB JDBC driver... also with differences in timezone behavior. We managed to craft JDBC connection properties and MyBatis configuration to get the JDBC driver to *not* do timezone conversion. JDBC spec is loose enough for a variety of behaviors.) — spencer7593, Sep 20 '19 at 20:52
https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-reference-configuration-properties.html — spencer7593, Sep 20 '19 at 20:58

score 0 · Answer 1 · answered Sep 20 '19 at 23:55

What's happening is that the date is being stored to MSSQL as UTC. This is typically desirable since, when running instances of your application from multiple regions, you don't want to see three 7 p.m. times in your database in the case that LocalDateTime is 6pm in one time zone, 7pm in another, and 8 p.m in another. If you have reason to prefer seeing the local time, then you can either specify a Zone in Java, subtract the difference in hours between local time and utc time from your time field, or convert data in your database whenever you need to perform SELECT queries.

Say you know your local time zone is East US, you could do something like this in Java:

timeNow.setHours(LocalDateTime.now().getHours() - ChronoUnit.HOURS.between(LocalDateTime.now(), LocalDateTime.now(ZoneOffset.UTC)));

Or something like this in SQL:

SELECT CONVERT(TIME, a_time AT TIME ZONE 'US Eastern Standard Time') FROM    
   schema.your_table;

I'd definitely avoid the Java impl, just giving an example in case your interested.

Value from MySQL TIME to Java Date not translating correctly

1 Answers1