MySQL datetime: insert a date with timezone offset

Question

I tried to insert a timestamp into 'dates' with:

INSERT INTO dates VALUES (4, "2011-10-04 12:58:36 -0600")

4 is just an ID. In the table it is inserted as:

2011-10-04 12:58:36 or 0000-00-00 00:00:00

So my problem is, that the time difference -0600 is lost. How can I insert it, too?

Answer 1

You could use SUBSTR() to chop it off and CONVERT_TZ() to convert it.

• http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_substr
• http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_convert-tz

Something like this

INSERT INTO table_name  CONVERT_TZ(SUBSTR('2011-10-04 12:58:36 -0600',1,19),'+00:00',SUBSTR('2011-10-04 12:58:36 -0600',20));