PHP/MySQL time zone migration
- by El Yobo
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).
Is there anything that I'm missing here, or does anyone have better suggestions for the approach?
