MySQL datetime fields and daylight savings time — how do I reference the “extra” hour?

by

I’ve got it figured out for my purposes. I’ll summarize what I learned (sorry, these notes are verbose; they’re as much for my future referral as anything else).

Contrary to what I said in one of my previous comments, DATETIME and TIMESTAMP fields do behave differently. TIMESTAMP fields (as the docs indicate) take whatever you send them in “YYYY-MM-DD hh:mm:ss” format and convert it from your current timezone to UTC time. The reverse happens transparently whenever you retrieve the data. DATETIME fields do not make this conversion. They take whatever you send them and just store it directly.

Neither the DATETIME nor the TIMESTAMP field types can accurately store data in a timezone that observes DST. If you store “2009-11-01 01:30:00” the fields have no way to distinguish which version of 1:30am you wanted — the -04:00 or -05:00 version.

Ok, so we must store our data in a non DST timezone (such as UTC). TIMESTAMP fields are unable to handle this data accurately for reasons I’ll explain: if your system is set to a DST timezone then what you put into TIMESTAMP may not be what you get back out. Even if you send it data that you’ve already converted to UTC, it will still assume the data’s in your local timezone and do yet another conversion to UTC. This TIMESTAMP-enforced local-to-UTC-back-to-local roundtrip is lossy when your local timezone observes DST (since “2009-11-01 01:30:00” maps to 2 different possible times).

With DATETIME you can store your data in any timezone you want and be confident that you’ll get back whatever you send it (you don’t get forced into the lossy roundtrip conversions that TIMESTAMP fields foist on you). So the solution is to use a DATETIME field and before saving to the field convert from your system time zone into whatever non-DST zone you want to save it in (I think UTC is probably the best option). This allows you to build the conversion logic into your scripting language so that you can explicitly save the UTC equivalent of “2009-11-01 01:30:00 -04:00” or “”2009-11-01 01:30:00 -05:00”.

Another important thing to note is that MySQL’s date/time math functions don’t work properly around DST boundaries if you store your dates in a DST TZ. So all the more reason to save in UTC.

In a nutshell I now do this:

When retrieving the data from the database:

Explicitly interpret the data from the database as UTC outside of MySQL in order to get an accurate Unix timestamp. I use PHP’s strtotime() function or its DateTime class for this. It can not be reliably done inside of MySQL using MySQL’s CONVERT_TZ() or UNIX_TIMESTAMP() functions because CONVERT_TZ will only output a ‘YYYY-MM-DD hh:mm:ss’ value which suffers from ambiguity problems, and UNIX_TIMESTAMP() assumes its input is in the system timezone, not the timezone the data was ACTUALLY stored in (UTC).

When storing the data to the database:

Convert your date to the precise UTC time that you desire outside of MySQL. For example: with PHP’s DateTime class you can specify “2009-11-01 1:30:00 EST” distinctly from “2009-11-01 1:30:00 EDT”, then convert it to UTC and save the correct UTC time to your DATETIME field.

Phew. Thanks so much for everyone’s input and help. Hopefully this saves someone else some headaches down the road.

BTW, I am seeing this on MySQL 5.0.22 and 5.0.27

More Related Contents:

  1. How to store repeating dates keeping in mind Daylight Savings Time
  2. Using MySQL’s TIMESTAMP vs storing timestamps directly
  3. PHP DateTime supported formats
  4. timestamp within a form [closed]
  5. Display which day it is? sunday or monday “php coding”? [closed]
  6. Php date() Date and time is stuck [closed]
  7. Convert from MySQL datetime to another format with PHP
  8. Timezone conversion in php
  9. Object of class DateTime could not be converted to string
  10. How to generate random date between two dates using php?
  11. How to store NULL values in datetime fields in MySQL?
  12. How to separate DATE and TIME from DATETIME in MySQL?
  13. PHP, MySQL and Time Zones
  14. Set timezone in PHP and MySQL
  15. NOW() function in PHP
  16. How to convert ISO8601 to Date format in php
  17. Date in a URL dd/mm/yyyy
  18. mysql – INSERT date range into date columns IF dates don’t overlap with existing ones
  19. adding 30 minutes to datetime php/mysql
  20. Formatting an SQL timestamp with PHP
  21. How to tell if a timezone observes daylight saving at any time of the year?
  22. Storing time information: Timezone required?
  23. adding 1 day to a DATETIME format value
  24. How to convert MySQL time to UNIX timestamp using PHP?
  25. Subtracting a certain number of hours, days, months or years from date
  26. How to detect Ambiguous and Invalid DateTime in PHP?
  27. How to apply bindValue method in LIMIT clause?
  28. mysql select query within a serialized array
  29. return one value from database with mysql php pdo
  30. How can I pass an array of PDO parameters yet still specify their types?

Leave a Comment