Hibernate Books

All times are UTC - 5 hours [ DST ]



Post new topic Reply to topic  [ 6 posts ] 
Author Message
 Post subject: Hibernate + MySQL: strange behavior with timestamps
PostPosted: Thu Mar 02, 2017 8:45 pm 
Newbie

Joined: Wed Feb 22, 2017 4:35 pm
Posts: 14
I'm using Hibernate 5.2.8. I have an entity with a timestamp:
Quote:
@NotNull
@Column(nullable = false)
private Timestamp created;


The database definitions for it are...

in postgres: "timestamp without time zone"
in mysql: "datetime"

To my knowledge, these properties should all line up fine. But the discrepancy occurs if I do something like this before persisting the entity:

Code:
created = new Timestamp(0L)


Here's the discrepancy: what I would expect from the above is essentially a timestamp at epoch time, i.e. zero. In postgres I get this result. However, in mysql, the result is apparently a timestamp of the current time, rather than epoch time. This is apparent if I select unix_timestamp(created) .

Seems like there is (and has been since the 2000s) a lot of discussion online regarding confusion in how timestamps are stored by hibernate for mysql vs. others, but unfortunately a lot of it is the blind leading the blind, with a whole lot of misunderstanding about the difference between Java's Timestamp and Date objects. As such I haven't found a fix yet. People are claiming that a DateTime or Date object should be used in the entity instead of Timestamp, which seems wrong and required a @Temporal annotation anyway.

Any ideas? Or am I doing the entity field wrong?

Although this bug from 2005 has to do with generating SQL from entities, it sounds kinda similar:

https://hibernate.atlassian.net/browse/HB-265


Top
 Profile  
 
 Post subject: Re: Hibernate + MySQL: strange behavior with timestamps
PostPosted: Fri Mar 03, 2017 3:30 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1386
If you want to get consistent behavior, I advise you to store all Timestamps in UTC by setting the hibernate.jdbc.time_zone Hibernate property:

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

_________________
If you liked my answer, you are going to love my High-Performance Java Persistence book and my blog as well.


Top
 Profile  
 
 Post subject: Re: Hibernate + MySQL: strange behavior with timestamps
PostPosted: Fri Mar 03, 2017 8:00 pm 
Newbie

Joined: Wed Feb 22, 2017 4:35 pm
Posts: 14
I tried including this property in my persistence.xml, but epoch time still isn't saved correctly for mysql. That prop also changes the time that is stored for non-mysql (obviously) so I wouldn't be able to use it without some major code rewriting besides.


Top
 Profile  
 
 Post subject: Re: Hibernate + MySQL: strange behavior with timestamps
PostPosted: Sat Mar 04, 2017 1:30 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1386
You need to use the MySQL Dialect which is at least 5.7:

- MySQL57InnoDBDialect
- MySQL57Dialect

_________________
If you liked my answer, you are going to love my High-Performance Java Persistence book and my blog as well.


Top
 Profile  
 
 Post subject: Re: Hibernate + MySQL: strange behavior with timestamps
PostPosted: Mon Mar 06, 2017 10:03 pm 
Newbie

Joined: Wed Feb 22, 2017 4:35 pm
Posts: 14
I'm afraid I don't have control over the mysql install version.

Should 5.6 and below be advised against for this reason?


Top
 Profile  
 
 Post subject: Re: Hibernate + MySQL: strange behavior with timestamps
PostPosted: Mon Mar 06, 2017 10:08 pm 
Newbie

Joined: Wed Feb 22, 2017 4:35 pm
Posts: 14
I guess this really is just an issue with mysql:

https://dev.mysql.com/doc/refman/5.7/en ... ation.html

Quote:
By default, the first TIMESTAMP column has both DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP if neither is specified explicitly. To suppress automatic properties for the first TIMESTAMP column, use one of these strategies


Damn it that's dumb. Usually mysql gets it right, but why did they think I would automagically want this?


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 6 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.