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:

Store all dates in UTC
Pass all dates to client in UTC
Let client apply offset for the user

When creating new appointments:

convert date / time to UTC based on users offset
store in UTC
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 :)

possible duplicate of Organizing dates with times and timezones. (Converting to accurate timestamp.) — vascowhite
– vascowhite, Commented Dec 26, 2013 at 21:05

eggyal · Accepted Answer · 2013-12-26 20:51:40Z

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.

answered Dec 26, 2013 at 20:51

eggyal

127k18 gold badges221 silver badges245 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PHP MYSQL convert timezone aware dates to UTC

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related