CODEWITHSUNDEEP
mysqldatetimedate
SandyBr
SandyBr

Reputation: 11979

MySQL datetime: insert a date with timezone offset

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?

Upvotes: 10

Views: 23118

Answers (1)

Charlie
Charlie

Reputation: 1082

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

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));

Upvotes: 10

Related Questions