-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 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: 19
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: 1630
Location: Romania
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"/>


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: 19
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: 1630
Location: Romania
You need to use the MySQL Dialect which is at least 5.7:

- MySQL57InnoDBDialect
- MySQL57Dialect


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: 19
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: 19
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  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 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.