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
– The Impaler, Commented Sep 20, 2019 at 18:40
In which timezone you app is running? and in which time zone your database is there? — Ryuzaki L
– Ryuzaki L, Commented Sep 20, 2019 at 18:52
I suppose you should consider -5 hours of time (zone) difference instead of +19 hours. — Barbaros Özhan
– Barbaros Özhan, Commented Sep 20, 2019 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
– DanielRead, Commented Sep 20, 2019 at 19:31

Christian Meyer · Accepted Answer · 2019-09-20 23:55:22Z

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.

Collectives™ on Stack Overflow

Value from MySQL TIME to Java Date not translating correctly

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related