Databases
StackOverflow2013
Postsdbo
POHow to store a datetime in MySQL with timezone info
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to store a datetime in MySQL with timezone info
    primarykey
    data
    text
    <p>I have thousands of photos that were taken in Tanzania and I want to store the date and time each photo was taken in a MySQL database. The server, however, is located in the U.S. and I run into problems when I try to store a Tanzanian date-time that falls within the "invalid" hour during spring Daylight Savings time (in the U.S.). Tanzania doesn't do DST, so the time is an actually valid time. </p> <p>Additional complications are that there are collaborators from many different timezones who will need to access the date-time values stored in the database. I want them to <em>always</em> come out as Tanzanian time and not in the local times that various collaborator are in. </p> <p>I'm reluctant to set session times because I know that there will be problems when someone sometime forgets to set a session time and gets the times out all wrong. And I do not have authority to change anything about the server.</p> <p>I've read: <a href="https://stackoverflow.com/questions/2532729/daylight-saving-time-and-time-zone-best-practices?rq=1">Daylight saving time and time zone best practices</a> and <a href="https://stackoverflow.com/questions/1646171/mysql-datetime-fields-and-daylight-savings-time-how-do-i-reference-the-extra">MySQL datetime fields and daylight savings time -- how do I reference the &quot;extra&quot; hour?</a> and <a href="https://stackoverflow.com/questions/1349280/storing-datetime-as-utc-in-php-mysql">Storing datetime as UTC in PHP/MySQL</a></p> <p>But none of them seems to address my particular problem. I'm not an SQL expert; is there a way to specify timezone when setting DATETIMEs? I haven't seen one. Otherwise, any suggestions on how to approach this issue is greatly appreciated.</p> <p>Edit: Here's an example of the problem I'm running into. I send the command:</p> <pre><code>INSERT INTO Images (CaptureEvent, SequenceNum, PathFilename, TimestampJPG) VALUES (122,1,"S2/B04/B04_R1/IMAG0148.JPG","2011-03-13 02:49:10") </code></pre> <p>And I get the error:</p> <pre><code>Error 1292: Incorrect datetime value: '2011-03-13 02:49:10' for column 'TimestampJPG' </code></pre> <p>This date and time exists in Tanzania, but not in the U.S., where the database is.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. USmkosmala
    plurals
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    3. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POHow to store a datetime in MySQL with timezone info
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POHow to store a datetime in MySQL with timezone info
      1. USÁlvaro González
      1. VTFavorite
    1. COYou shouldn't want to store time zone info in database. Store all date/time data as UTC and always make time zone offset adjustment on the application layer.
      singulars
      1. POHow to store a datetime in MySQL with timezone info
      1. USmarekful
    2. CO@MarcellFülöp: I keep seeing people say "store it as UTC" but I don't understand what that means. How can I store a date time "as UTC"? As far as I can tell all I can do is insert something of the form YYYY-MM-DD HH:MM:SS. Where do I tell the database that it's UTC?
      singulars
      1. POHow to store a datetime in MySQL with timezone info
      1. USmkosmala
    3. COYou don't. MySQL by default will use the system time zone internally but it's possible to define a different time zone for the MySQL server globally or even per transaction. When you insert a date, it is not possible to define the time zone along with the date string in MySQL. A date like '2013-11-10 00:00' refers to a point in time since the epoch. You store it and you know what time zone your server is in. Then when you retrieve it, you can make the necessary adjustments to transform that date form the server's time zone to the client's.
      singulars
      1. POHow to store a datetime in MySQL with timezone info
      1. USmarekful
 

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