CODEWITHSUNDEEP
javamysqlsqlspring-bootspring-data-jpa
SilentStorm
SilentStorm

Reputation: 51

Best way to store timezone-independent time in JPA MySql

I can't find a good solution in JPA to store timezone-independent time in a mysql db.

I have a shift table, where I need to store the start and the end time for each shift.

+-------------+--------------+------+-----+----------+----------------+
| Field       | Type         | Null | Key | Default  | Extra          |
+-------------+--------------+------+-----+----------+----------------+
| id          | int(11)      | NO   | PRI | NULL     | auto_increment |
| name        | varchar(64)  | NO   |     | NULL     |                |
| start_time  | time         | NO   |     | 00:00:00 |                |
| end_time    | time         | NO   |     | 00:00:00 |                |
+-------------+--------------+------+-----+----------+----------------+

So, the start/end times retrived from mysql have to be treated as strings (without applying a particular timezone). But if I store them as varchar(8) I cannot use the following query (without applying any type conversion from varchar to time). 

select * from shift where ('21:30:00' BETWEEN start_time and end_time );

So, I want to preserve the ability to compare them as times.

I tried to model the shift table as following:

    @Entity
    public class Shift {
      @Id
      private Long id;

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

      @Column(name = "start_time")
      private LocalTime startTime;

      @Column(name = "end_time")
      private LocalTime endTime;

...

The start_time and the end_time are mapped as LocalTime because the LocalTime doesn't store the timezone info. But, Hibernate convert the java.util.Date of each record retrived to a LocalTime applying the system default timezone, as following: 

    if ( Date.class.isInstance( value ) ) {
        final Date ts = (Date) value;
        final Instant instant = Instant.ofEpochMilli( ts.getTime() );
        return LocalDateTime.ofInstant( instant, ZoneId.systemDefault() ).toLocalTime();
    }

So, the resulting start/end time is relative to the timezone of the server.

A solution is to store them as integers and split hours from minutes. In this way, the shift table will have four columns: start_hours, start_minutes, end_hours, end_minutes. But it's tedious to make query using them in JPA. There is a better solution?

UPDATE

I have to fetch the current shift using the timezone of the server. So, if I store times as UTC in the DB and then I query them converting the current server time to UTC doesn't work because during the Daylight Saving Time (DST) the server timezone increases by one. So, in the winter the 07:00:00+01 is converted to 06:00:00 UTC. But, in the summer 07:00:00+02 is converted to 05:00:00 UTC.

Upvotes: 5

Views: 1545

Answers (1)

Niraj Sonawane
Niraj Sonawane

Reputation: 11115

Welcome to SO

It's always better to store dates with UTC TimeZone in database and do the conversion on client-side. You can set below property to force JPA To use UTC

spring.jpa.properties.hibernate.jdbc.time_zone=UTC

Hope this hleps

You can take a look at this blog

Upvotes: 2

Related Questions