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.
- you have to use datetime column, so value must be
2010-03-26 10:13:04
without any timezone steeings - 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
精彩评论