PHP/MySQL time zone migration

Question

I have an application that currently stores timestamps in MySQL DATETIME and TIMESTAMP values. However, the application needs to be able to accept data from users in multiple time zones and show the timestamps in the time zone of other users. As such, this is how I plan to amend the application; I would appreciate any suggestions to improve the approach.

Database modifications

• All TIMESTAMPs will be converted to DATETIME values; this is to ensure consistency in approach and to avoid having MySQL try to do clever things and convert time zones (I want to keep the conversion in PHP, as it involves less modification to the application, and will be more portable when I eventually manage to escape from MySQL).
• All DATETIME values will be adjusted to convert them to UTC time (currently all in Australian EST)

Query modifications

• All usage of NOW() to be replaced with UTC_TIMESTAMP() in queries, triggers, functions, etc.

Application modifications

• The application must store the time zone and preferred date format (e.g. US vs the rest of the world)
• All timestamps will be converted according to the user settings before being displayed
• All input timestamps will be converted to UTC according to the user settings before being input

Additional notes

• Converting formats will be done at the application level for several main reasons
  • The approach to converting time zones varies from DB to DB, so handing it there will be non-portable (and I really hope to be migrating away from MySQL some time in the not-to-distant future).
  • MySQL TIMESTAMPs have limited ranges to the permitted dates (~1970 to ~2038)
  • MySQL TIMESTAMPs have other undesirable attributes, including bizarre auto-update behaviour (if not carefully disabled) and sensitivity to the server zone settings (and I suspect I might screw these up when I migrate to Amazon later in the year).
  • The choice to change all the current datetime values, and to use UTC_TIMESTAMP() instead of NOW() is to avoid any problems with server / connection time zone setups, which would modify NOW() but leave UTC_TIMESTAMP() alone; see below for an example.

Example of UTC_TIMESTAMP() vs NOW()

mysql> set time_zone = 'Australia/Canberra';
Query OK, 0 rows affected (0.06 sec)

mysql> select now(), utc_timestamp();
+---------------------+---------------------+
| now()               | utc_timestamp()     |
+---------------------+---------------------+
| 2010-06-06 14:31:36 | 2010-06-06 04:31:36 |
+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> set time_zone = 'America/Los_Angeles';
Query OK, 0 rows affected (0.00 sec)

mysql> select now(), utc_timestamp();
+---------------------+---------------------+
| now()               | utc_timestamp()     |
+---------------------+---------------------+
| 2010-06-05 21:31:43 | 2010-06-06 04:31:43 |
+---------------------+---------------------+
1 row in set (0.00 sec)

Is there anything that I'm missing here, or does anyone have better suggestions for the approach?

Answer 1

Is there anything that I'm missing here, or does anyone have better suggestions for the approach?

You might be doing too much work.

As long as you know the time zone of the timestamp stored, you can easily convert it to another time zone. If you're using a modern version of PHP, the built-in DateTime class includes comprehensive time zone management.

You don't necessarily need to convert all your timezones in the database if they're already considered local times and it's been properly configured to use a local time zone, i.e. they're already self-consistent. All you need to do is simply convert the local times to the user's time zone when rendering the time to them, and store the as-recorded timezone with the timestamp (for other users viewing the time in the timezone of the timestamp owner).

Otherwise, your plan seems well-thought-out and complete.