4

I have a (java) appserver using timezone America/Los_Angeles, but a database that uses UTC. I'd like the dates in the database to reflect the appserver's timezone (that is where the dev team is, so it's helpful to have all the dates and times in their timezone). I'm using Hibernate to map java.util.Date fields to MySQL TIMESTAMP and DATETIME fields. I have create_date field (set by the app) and update_date field with this definition:

  `update_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

When I do inserts from the app, it works fine: if it's 9pm in America/Los_Angeles, the db shows 21:00 (even though the connection's timezone is UTC, which is weird but desirable). When I retrieve the item, create_date is still 21:00. Good so far. But when I do an update, the CURRENT_TIMESTAMP kicks in and shows 04:00 the next day. When the app retrieves the item, it still says 04:00.

I've tried many combinations of useTimezone=true, serverTimezone=America/Los_Angeles, useGMTMillisForDatetimes=true, and useLegacyDatetimeCode=false. I've also tried

  `update_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE 'CONVERT_TZ(CURRENT_TIMESTAMP,''+00:00'',''-07:00'')'

But the database hated that and I couldn't get that to work. I could just remove the ON UPDATE trigger but then I lose auditing if someone is updating the db directly.

Strangely, even if I set serverTimezone=UTC, when I do inserts from the app the create_date still appears as 21:00, rather than as UTC. Somehow there appears to be a timezone conversion on the appserver side. My JPA mapping annotations on create_date are

@Column(name='create_date', updatable=false)
@Temporal(TemporalType.TIMESTAMP)
Date createDate

Is there any way to get this to work properly?

I am using ConnectorJ 5.1.29 and MySQL server 5.6.13.

Ken DeLong
  • 341
  • 2
  • 5
  • 16
  • I hope this link helps you. http://www.inmotionhosting.com/support/website/databases/how-to-change-mysql-server-time-zone – Ram Sep 04 '14 at 05:54
  • @Ken DeLong does [this](http://stackoverflow.com/questions/508019/jpa-hibernate-store-date-in-utc-time-zone) helps you – Ankur Singhal Sep 04 '14 at 06:53
  • possible duplicate of [How to set time zone of mysql?](http://stackoverflow.com/questions/930900/how-to-set-time-zone-of-mysql) – DavidPostill Sep 04 '14 at 07:05
  • 1
    Eventually, I had to use useLegacyDatetimeCode=false&serverTimezone=America/Los_Angeles, for what it's worth. I had to debug into the driver code to understand what was going on. – Ken DeLong Apr 24 '15 at 15:23

0 Answers0