JPA / MySQL define Time zone for type TIME?

1

I have a front-end application in AngularJS and a back-end in Java, using Spring.

Technically I have the model of the application made with JPA, my conflicting attribute is the following:

@Column(name = "hora_inicio")
@Temporal(TemporalType.TIME)
private Date horaInicio;

horaInicio is a column in my table ( MySQL by the way) that only contains hh: mm: ss

At the time of making a new record for this table I have the following JSON

{  
    "a":"dsfsdfdsfsdfsdf",
    "hora":"07",
    "minuto":"00",
    "b":3,
    "c":2,
    "e":1,
    "horaInicio":25200000,
    "id":"140",
    "sh":9999,
    "co":"SOE"
}

In the JSON Start time is a time in milliseconds result of the calculation of the attributes hour and minute , I verify this with the following script , to see that the transformations are going well internally

<script>
    var x = "09:15:00";
    var arr = x.split(":")
    var hour = arr[0] * (3600 * 1000);
    var minute = arr[1] * (60 * 1000);
    var second = arr[2] * 1000;

    var answer = hour + minute + second;
    document.write("ms " + answer)

    //inverso: ms a hora

    var h = (answer - minute - second) / (3600 * 1000);

    if(h < 10)
        h = '0' + h; 
    document.write(", hora " + h)

    var m = (answer - hour - second) / (60 * 1000);

    if(m < 10)
        m = '0' + m;
    document.write(", minuto " + m)

    var x = (answer - hour - minute) / 1000;

    if(x < 10)
        x = '0' + x;
    document.write(", segundo " + x)

</script>

This script is tested in jso.do .

Well, the problem is that the time that is sent from is the form is not the same that is stored in the database. For example, if at the form level (front-end) I define the time 07:00:00 in the database, the time 02:00:00 is stored. I suppose that the distortion of the data occurs at the moment when the Controller receives the JSON and pauses it to my POJO .

@RequestMapping(value = "/maintenance/", method = RequestMethod.POST, consumes = { "application/json" })
public String save(@RequestBody Planificacion p) {
    try {
            logger.info("INPUT DATA >>> Hora de inicio desde formulario {}", p.getHoraInicio());
            // ....
        }
}

The output of the log at that moment is the following

INPUT DATA >>> Hora de inicio desde formulario Thu Jan 01 02:00:00 COT 1970

The team's time zone is as follows:

Result of this, 5 hours are extracted from the original date (07:00:00 - 5 hours = 02:00:00)

For JPA is there a way to strictly define the time zone for a @Column attribute?

I ask the question because it is not an option to define the time zone from the start (JVM), since the JVM is shared by other applications.

How can I resolve this detail at the code level without affecting the JVM?

    
asked by Rosendo Ropher 20.05.2016 в 23:51
source

1 answer

0

The cause is complex and not trivial in its solution. The origin is in JDBC.

Solutions

1.- In hibernate 5.2 it is possible to force the use of UTC time zone using the following configuration.

<property name="hibernate.jdbc.time_zone" value="UTC"/>

2.- As indicated in this page (in English) you can start the JVM with the next parameter, so when you force the time zone to UTC, there will be no deviation

java -Duser.timezone=UTC

Recommendation

In general it is a good practice to store and process the times always in UTC, and only use the local time for presentation to the user. This avoids many problems when the software must handle different time zones simultaneously or it is going to execute said softare in different zones.

    
answered by 23.11.2016 в 01:34