My DB server (running MySql 5.5) is set to UTC, and dates are stored as Unix timestamps in the database using UNSIGNED INT. The database is primarily used for storing tasks which are run at a specific time (exec_time).
I insert tasks by creating a timestamp in PHP using the timezone of the user logged in (BST in this instance). For example, I have a task set to run at 1351396800 which is for tomorrow morning at 4am GMT.
I pluck tasks out of the database with the following query:
SELECT * FROM tasks WHERE exec_time <= UNIX_TIMESTAMP();
When the clocks roll back one hour tomorrow at 2am will this setup be ok?
Update: PHP is converting the dates fine. With PHP timezone set to Europe/Dublin (Currently BST) Two events added for 12 midnight and then 4am are stored as follows:
mysql> select exec_time, FROM_UNIXTIME(exec_time) from tasks order by id desc limit 2;
+-------------+----------------------------+
| exec_time | FROM_UNIXTIME(exec_time) |
+-------------+----------------------------+
| 1351378800 | 2012-10-27 23:00:00 |
| 1351396800 | 2012-10-28 04:00:00 |
In answer to your question, it depends how critical the time fields are, and whether the server's local time will change or not. If it's UTC then it probably won't change.
The temporal types in MySQL aren't timezone aware. You'll have to implement timezones yourself, perhaps by always storing a UTC timestamp/datetime and a separate timezone column which contains an interval offset from +12 to -12 hours for how much time to add or subtract to the UTC timestamp for the timezone.
The actual handling of what value to put in the timezone field and the work needed to retrieve a timestamp adjusted for the timezone are up to you, unfortunately.
If switching to Postgres is an option then you can always use the TIMESTAMP WITH TIMEZONE type that Postgres supplies.
tl;dr You should be fine as long as your exec_time column has a TIMESTAMP data type.
There isn't an explicit UNIX_TIMESTAMP column datatype. There is a TIMESTAMP column data type. Values for columns of this data type are converted automatically from your client connection's time zone to UTC (a/k/a Z or Zulu time, f/k/a Greenwich Mean Time) when being converted from a date/time string and from UTC to your client connection's time zone upon conversion to a date/time string.
So, if you're storing your exec_time column as a TIMESTAMP, you should be able to use the clause you propose:
WHERE exec_time <= UNIX_TIMESTAMP()
This will work because both your exec_time values and the result of the UNIX_TIMESTAMP() function call are handled in UTC on the server side. Your exec_time values will be stored in UTC.
If you're storing exec_time as an UNSIGNED INT or a similar numeric data type, you won't have been able to take advantage of the automatic conversion to UTC before storing.
You can mess with the display conversion behavior by setting your client connection time_zone as follows:
SET time_zone='SYSTEM' /* for your system's local time */
or
SET time_zone='+0:00' /* for UTC */
or
SET time_zone'America/New_York' /* or 'Europe/Vienna' or whatever */
Once you've issued one of these SET operations, do
SELECT exec_time, FROM_UNIXTIME(exec_time)
to get a sense of how your values are stored server side and translated.
If you want to see what will happen eight days on, try this:
SELECT 3600*24*8+exec_time, FROM_UNIXTIME(3600*24*8+exec_time)
http://dev.mysql.com/doc/refman/5.5/en//time-zone-support.html