Better way to safely insert UTC time value into mySQL TIMESTAMP variable?

Question

My application wants to insert a timestamp value into a TIMESTAMP variable in a mySQL database. The timestamp value is a UTC time in the usual "YYYY-MM-DD HH:MM:SS" format. The problem is that my SQL server is set to use SYSTEM time (SELECT @@global.time_zone says SYSTEM), and the system timezone is Europe/London (the server is running Ubuntu 14.04), so mySQL does a daylight-saving conversion and stores the value one hour off from what it ought to be. (I guess if I was in another timezone e.g. CST then I'd have an unwanted timezone offset as well as the daylight saving hour).

To get mySQL to do The Right Thing it appears that I need to covert the UTC timestamp into system time before I insert it so that mySQL can convert it from system time to UTC time before it stores it internally. This bit of code has the desired effect:-

mysql> select timestamp("2015-05-06 12:34:56")+CURRENT_TIMESTAMP-UTC_TIMESTAMP;

+------------------------------------------------------------------+
| timestamp("2015-05-06 12:34:56")+CURRENT_TIMESTAMP-UTC_TIMESTAMP |
+------------------------------------------------------------------+
|                                            20150506133456.000000 |
+------------------------------------------------------------------+

I'm going to run with this for now, but it seems like a bit of a palaver, so is there a better way?

[edit] Another round of RTFM'ing gave me this idea...

mysql> select CONVERT_TZ("2015-05-06 12:34:56", "+00:00", "SYSTEM");

+-------------------------------------------------------+
| CONVERT_TZ("2015-05-06 12:34:56", "+00:00", "SYSTEM") |
+-------------------------------------------------------+
| 2015-05-06 13:34:56                                   |
+-------------------------------------------------------+

That looks a lot cleaner. Any better ideas?

Answer 1

You can probably use the CONVERT_TZ function which uses the specified timezone to parse the date instead of system timezone:

SELECT CONVERT_TZ('2015-05-06 12:34:56','+00:00','SYSTEM') AS `Local Time`,
       UNIX_TIMESTAMP(CONVERT_TZ('2015-05-06 12:34:56', '+00:00', 'SYSTEM')) AS `UNIX Timestamp`;

+---------------------+----------------+
| Local Time          | UNIX Timestamp |
+---------------------+----------------+
| 2015-05-06 17:34:56 |     1430915696 |
+---------------------+----------------+

The local time value will differ depending on which system the query is run. However, the UNIX timestamp value will be same.

You can then insert the local time value in the timestamp column; MySQL will store the value after conversion.