lundi 29 juin 2015

How to handle java date with mysql datetime column

I have a system that consumes a third-party WebService. This WebService gives me a lot of fields about a ticket. Three of these fields are dates with Unix Timestamp format (below I'll give examples with values..). I save these fields in MySQL datetime columns using MyBatis.

My problem is when I display the time to the user, for example:

I receive this value from the webservice: 1435618800 , using this site to convert to date I get this value: Mon, 29 Jun 2015 23:00:00 GMT.

Inside my application, I get the value in minutes (1435618800) from the web service and I convert to Date using :

Date date = Date.from( Instant.ofEpochSecond( Long.parseLong(StringValueFromWS)));

I set this date to a ticket object, then I pass this ticket object to MyBatis to save to my table. When I look inside the table, I see the value 2015-06-29 20:00:00. When I show this value, the users see the following time : 2015-06-29 17:00:00, they should be seeing 2015-06-29 20:00:00.

Here is the create table:

CREATE TABLE `ticket` (
  `id_ticket` int(11) NOT NULL AUTO_INCREMENT,
  `open_date` datetime DEFAULT NULL,
  `callback_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id_ticket`)
);

Here is the xml from my insert and select from mybatis:

<select id="getTicket" parameterType="PaginationFilter" resultType="Ticket">
    <![CDATA[
        SELECT
            t.id_ticket as 'id',
            t.open_date as 'openDate',
            t.callback_date as 'callbackDate'

        from ticket as t
        WHERE t.id_ticket = XXX 
    ]]>
</select>

<insert id="insertTicket" parameterType="Ticket" useGeneratedKeys="true" keyProperty="id">
    INSERT INTO ticket 
    (
        `open_date`,
        `callback_date`
    ) 
    VALUES 
    (
        #{openDate}, 
        #{callbackDate}
    );
</insert>

My doubt is what`s the most elegant solution for this. Should I add/discount the GMT offset to the date field before inserting into MySQL to have the GMT 00:00 inside my database or is there any better solution?

I solved this problem adding 3 hours to my dates, but I would like to know a better solution 'cause I didn't like this one.

Some additional information:

  • Java version 1.8.0_45
  • Mybatis 3.0.5
  • MySQL 5.5.15
  • I'm at GMT -03:00 (Brazil - Brasilia)

Aucun commentaire :

Enregistrer un commentaire