MySql UNIX_TIMESTAMP（）以UTC的形式存储，夏令时即将来临

Question

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        |

Answer 1

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.