how to store "2010-03-26 10:13:04 Etc/GMT" mysql?

Question

Hi All,

I would like to store "2010-03-26 10:13:04 Etc/GMT" value in column of type datetime.

when i try to insert it i got exception

SQLException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '10:13:04 Etc/GMT', at line 1

how to insert data time with time zone.

Thanks in advance. please help me its urgent.

Answer 1

MySQL's date/time formats don't support time zones. You would have to "normalize" the time to one specific time zone (usually UTC or the time zone the server is located in), or store the time zone in a different field and calculate the offsets by yourself.

Check out the alternative presented in this blog entry: Storing Times in mySQL it's a bit dated but I think what it says still applies. Apparently, Wordpress stores local and GMT times in two different DATETIME fields.

Related:

• mySQL Server timezone support
• Dealing with PHP server and MySQL server in different time zones

Answer 2

You can do that in a char field... but not in a datetime field. Look here for more information about timezones in mysql:

http://dev.mysql.com/doc/refman/5.1/en/time-zone-support.html

and for changing timezones in the database:

http://dev.mysql.com/doc/refman/5.1/en/time-zone-upgrades.html

Answer 3

1. you have to use datetime column, so value must be 2010-03-26 10:13:04 without any timezone steeings
2. Any string literal must be delimited with quotes

so, the query must be look like

INSERT INTO table set dtime='2010-03-26 10:13:04';