Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>The short answer is:</p> <ul> <li>add "default-time-zone=utc" to my.cnf</li> <li>in your code, always "think" in UTC, except when displaying dates for your users</li> <li><p>when getting/setting dates or timestamps with JDBC, always use the Calendar parameter, set to UTC:</p> <p>resultset.getTimestamp("my_date", Calendar.getInstance(TimeZone.getTimeZone("UTC")));</p></li> <li>either synchronize your servers with NTP, or rely only on the database server to tell you what time it is.</li> </ul> <hr> <p>The long answer is this:</p> <p>When dealing with dates and timezones in any database and with any client code, I usually recommend the following policy:</p> <ol> <li><p><strong>Configure your database to use UTC timezone</strong>, instead of using the server's local timezone (unless it is UTC of course).</p> <ul> <li><p>How to do so depends on your database server. Instructions for MySQL can be found here: <a href="http://dev.mysql.com/doc/refman/5.0/en/time-zone-support.html" rel="noreferrer">http://dev.mysql.com/doc/refman/5.0/en/time-zone-support.html</a>. Basically you need to write this in my.cnf: default-time-zone=utc</p></li> <li><p>This way you can host your database servers anywhere, change your hosting location easily, and more generally manipulate dates on your servers without any ambiguity.</p></li> <li>If you really prefer to use a local timezone, I recommend at least turning off Daylight Saving Time, because having ambiguous dates in your database can be a real nightmare. <ul> <li><em>For example, if you are building a telephony service and you are using Daylight Saving Time on your database server then you are asking for trouble: there will be no way to tell whether a customer who called from "2008-10-26 02:30:00" to "2008-10-26 02:35:00" actually called for 5 minutes or for 1 hour and 5 minutes (supposing Daylight Saving occurred on Oct. 26th at 3am)!</em></li> </ul></li> </ul></li> <li><p><strong>Inside your application code, always use UTC dates, except when displaying dates to your users.</strong></p> <ul> <li>In Java, when reading from the database, always use:</li> </ul> <p>Timestamp myDate = resultSet.getTimestamp("my_date", Calendar.getInstance(TimeZone.getTimeZone("UTC")));</p> <ul> <li>If you do not do this, the timestamp will be assumed to be in your local TimeZone, instead of UTC.</li> </ul></li> <li><p><strong>Synchronize your servers or only rely on the database server's time</strong></p> <ul> <li><p>If you have your Web server on one server (or more) and your database server on some other server, then I strongly recommend you synchronize their clocks with NTP.</p></li> <li><p>OR, only rely on one server to tell you what time it is. Usually, the database server is the best one to ask for time. In other words, avoid code such as this:</p></li> </ul> <p>preparedStatement = connection.prepareStatement("UPDATE my_table SET my_time = ? WHERE [...]");<br> java.util.Date now = new java.util.Date(); // local time! :-(<br> preparedStatement.setTimestamp(1, new Timestamp(now.getTime()));<br> int result = preparedStatement.execute();</p> <ul> <li>Instead, rely on the database server's time:</li> </ul> <p>preparedStatement = connection.prepareStatement("UPDATE my_table SET my_time = NOW() WHERE [...]");<br> int result = preparedStatement.execute();</p></li> </ol> <p>Hope this helps! :-)</p>
    singulars
    1. This table or related slice is empty.
    1. POHow to store a java.util.Date into a MySQL timestamp field in the UTC/GMT timezone?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USMiniQuark
    1. USMiniQuark
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POHow to store a java.util.Date into a MySQL timestamp field in the UTC/GMT timezone?
      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. COTwo additions: 1. You should use "default-time-zone=GMT" (case-sensitive) because the MySQL isn't smart enough to map "utc" to "GMT" on its own and Java only recognizes the latter. 2. You should pass the driver "useLegacyDatetimeCode=false" to fix this bug: http://bugs.mysql.com/bug.php?id=15604
      singulars
      1. PO
      1. USGili
    2. COvery good points! Bug I have some other points to raise. You didn't consider the option to set the session timezone on each connection to db, doing that you don't need to change which timezone your MySQL has started. I'm having a lot of problems while storing 'new java.util.Date' on db. The quick solution is set my JVM to start on UTC too (-Duser.timezone=UTC). These points shall be good to add in your post!
      singulars
      1. PO
      1. USrafa.ferreira
    3. COAccording to the MySQL documentation on the [time_zone](http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_time_zone) system variable, the my.cnf variable name is default_time_zone (underscores as opposed to the dashes shown in your post).
      singulars
      1. PO
      1. USScott D. Strader
 

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