I am creating an application that based on scheduling appointments, in an environment where a user can select their desired timezone.
After doing some research, I was convinced to use MYSQL's TIMESTAMP to store all dates. I thought that, when using TIMESTAMP, all dates would be converted and stored as UTC. Then, a TIMESTAMP column would convert to the timezone of MYSQL when pulling the date from the database.
Below is some example code of my current setup:
On each page, the timezones are set for PHP and MYSQL (Based on user selection. PST for this example):
date_default_timezone_set('US/Pacific');
SET time_zone = '-08:00'
When putting a time into MYSQL, I would just convert a timestamp to the proper format.
date("Y-m-d H:i:s", $unix_timestamp);
When pulling form the database, I would simply use MYSQL to convert to UNIX.
SELECT UNIX_TIMESTAMP(created_at) as created_at FROM `table` WHERE id=1
This was elegant, and seemed to work perfectly. MySQL was doing all the heavy lifting of timezone conversion for me, just by using TIMESTAMP. That is, Until a Daylight Savings Time happened. Now, anytime a non-UTC timezone is selected, the time is an hour off.
What am I doing, wrong? Please help!
