How to handle java date with mysql datetime column

Question

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)
• Why I'm using Date.from(Instant.ofEpochSecond(timeInSeconds)) here

Answer 1

The issue might be that you're serializing the date object when giving it to your MyBaits class. I'm not sure what MyBaits receives and outputs.

When you serialize it I'll assume the serializer uses the toString() method on the date which causes the time to be shown in local time. A workaround in this case would be to set the TimeZone by doing the following:

Date d = Date.from(Instant.ofEpochSecond(1435618800));
TimeZone.setDefault(TimeZone.getTimeZone("UTC"));

You should also find out the timezone of mysql by doing the following:

mysql> SELECT @@global.time_zone, @@session.time_zone;

Answer 2

In MySQL, you should be using the TIMESTAMP data type rather than DATETIME. As the doc says, TIMESTAMP will adjust incoming and outgoing data to UTC.

This is generally best practice regardless of MyBatis and your issue: Do all your business logic and data storage in UTC.

I don't know MyBatis well, so I don't know if this will solve your problem. But it might.