PHP MYSQL convert timezone aware dates to UTC

Question

I am working on an existing scheduling application that stores appointment date/time in mysql as "2013-12-25 09:00:00". No timezone is accounted for, as until now, that time is simply sent to the front end and displayed in some basic fashion. Now, I would like to build in some functionality like "send a reminder X hours before the appt". Obviously to do this I would need to know the time zone to determine if I am X hours before the appt.

I'm not really sure where to start now. How would I convert my existing dates to UTC without knowing the timezone? If I did collect the timezone info from my users, how would I go about converting existing mysql datetime format to UTC? I also have questions about the general roundtrip workflow of working with UTC dates. Here is how I would think it works:

1. Store all dates in UTC
2. Pass all dates to client in UTC
3. Let client apply offset for the user

When creating new appointments:

1. convert date / time to UTC based on users offset
2. store in UTC
3. restart process with #1

Is this accurate? Thanks for any help, would like to do this properly, but not sure where to start with all these existing appts, and no timezone info :)

Answer 1

MySQL's TIMESTAMP datatype (as opposed to its DATETIME one) is internally stored in UTC. Translation between that and the client's time_zone is performed upon receiving data from / sending data to the client: one merely needs to set that variable at the start of the session:

SET time_zone = 'GMT';

As for converting existing times, you can utilise MySQL's CONVERT_TZ() function:

UPDATE my_table SET my_column = CONVERT_TZ(my_column, 'GMT', 'MET');

Read more under MySQL Server Time Zone Support.