Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <blockquote> <p>However, when hibernate actually persists to the database, the date is converted to local time and stored) ex. the value has been set in the entity setter as "2009-09-09 00:08:08" - GMT the actual value persisted to the database is "2009-09-08 08:08:08" - eastern time US.</p> </blockquote> <p>Ok, first, whatever column type are you using to store your date in MySQL (TIMESTAMP or DATETIME), <strong>neither stores the time zone</strong>. From <a href="http://forums.mysql.com/read.php?20,269092,269150#msg-269150" rel="nofollow noreferrer">Re: Storing timezone with datetime</a>:</p> <blockquote> <ul> <li><p>TIMESTAMP is seconds since 1970, sitting in 4 bytes. It is stored in GMT. That is, the TZ offset is applied as you store a value, then reapplied when you fetch it. (...)</p></li> <li><p>DATETIME is an 8-byte string of digits "yyyymmddhhmmss". (...) </p></li> </ul> </blockquote> <p>And second, unless a <a href="http://bugs.mysql.com/bug.php?id=15604" rel="nofollow noreferrer">buggy behavior</a>, my understanding is that the conversion is supposed be done either by the server or by the JDBC driver depending on the <a href="http://dev.mysql.com/doc/refman/5.1/en/time-zone-support.html" rel="nofollow noreferrer">the server time zone</a> settings so that you don't get inconsistent data.</p> <p>In both cases, my point is that storing <em>"2009-09-09 00:08:08" - GMT</em> or <em>"2009-09-08 08:08:08" - eastern time US</em> <strong>from Java</strong> should <strong>yield to the same value</strong> in the database. </p> <p>However, it looks like a different conversion is done when <strong>displaying</strong> them. This begs the question: how did you actually check the value of the "persisted date". Does the "problem" occur in your SQL client? In Java code?</p> <h3>References</h3> <ul> <li><a href="http://dev.mysql.com/doc/refman/5.1/en/time-zone-support.html" rel="nofollow noreferrer">9.6. MySQL Server Time Zone Support</a></li> <li><a href="http://dev.mysql.com/doc/refman/5.1/en/connector-j-reference-configuration-properties.html" rel="nofollow noreferrer">21.3.4.1. Driver/Datasource Class Names, URL Syntax and Configuration Properties for Connector/J</a></li> <li><a href="http://bugs.mysql.com/bug.php?id=15604" rel="nofollow noreferrer">Bug #15604: TimeZone discarded storing java.util.Calendar into DATETIME</a> </li> </ul> <hr> <blockquote> <p>MySQL documentation for DateTime says "MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format". That means mysql converts the 'milliseconds since epoch' to the above format. So now my question becomes, is timezone info also stored in mysql?</p> </blockquote> <p>I've updated my initial answer (which was not totally accurate/exhaustive). Whether you're using DATETIME or TIMESTAMP, the answer is no.</p> <blockquote> <p>Another observation I made is, the above date 'conversion' issue exists only when Im setting the date in the Java application. If I create a mysql trigger to update/set date using 'UTC_TIMESTAMP()', the date is displayed in the 'UTC' time. </p> </blockquote> <p>The <a href="http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_utc-timestamp" rel="nofollow noreferrer">UTC_TIMESTAMP()</a> function <strong>always</strong> returns the current <strong>UTC</strong> date and time.</p> <hr> <p>What I'd like to know is:</p> <ul> <li>How did you "reveal" the problem? With a SQL client or from Java?</li> <li>What is the local time zone of the JVM?</li> <li>What is the MySQL Server time zone?</li> <li>What is the version of the MySQL JDBC Driver?</li> <li>Could you do a test with raw JDBC?</li> </ul>
    singulars
    1. This table or related slice is empty.
    1. POHibernate date conversion
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USPascal Thivent
    1. USPascal Thivent
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POHibernate date conversion
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COMySQL documentation for DateTime says "MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format". That means mysql converts the 'milliseconds since epoch' to the above format. So now my question becomes, is timezone info also stored in mysql? Another observation I made is, the above date 'conversion' issue exists only when Im setting the date in the Java application. If I create a mysql trigger to update/set date using 'UTC_TIMESTAMP()', the date is displayed in the 'UTC' time.
      singulars
      1. PO
      1. USmissionE46
    2. COHow did you "reveal" the problem? With a SQL client or from Java? mysql client What is the local time zone of the JVM? JVM is on US eastern time What is the MySQL Server time zone? MySQL is on eastern time as well What is the version of the MySQL JDBC Driver? dont have the info yet. But I believe we are using 5.x Could you do a test with raw JDBC? saves in Eastern time.
      singulars
      1. PO
      1. USmissionE46
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload