Best way to safely insert UTC time value into myESTQL TIMESTAMP variable?
My application wants to insert timestamp value into TIMESTAMP variable in mySQL database. The timestamp value is UTC time in the usual YYYY-MM-DD HH: MM: SS format. The problem is my SQL server is configured to use SYSTEM time ( SELECT @@global.time_zone
says SYSTEM
) and system timezone is Europe / London (server is running Ubuntu 14.04), so mySQL performs daylight saving time and keeps the value one hour away from what should be. (I think if I was in a different timezone like CST then I would have an unwanted timezone offset as well as daylight saving time).
To get mySQL to do the "Right Thing" I feel like I should hide the UTC timestamp at system time before inserting it so that mySQL can convert it from system time to UTC 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 work with this for now, but it seems like a bit of a palate, 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 | +-------------------------------------------------------+
It looks much cleaner. Any better ideas?
source to share
Perhaps you can use a function CONVERT_TZ
that uses the specified timezone to parse the date instead of the 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 is running. However, the UNIX timestamp value will be the same.
Then you can insert the local time into the timestamp column; MySQL will retain the value after conversion.
source to share