开发者

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

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.


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


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


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
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜