在PHP/MySQL中将日期时间存储为UTC [英] Storing datetime as UTC in PHP/MySQL
问题描述
我所读到的有关将时间转换为用户时区的任何地方都说，最好的方法是将日期和时间存储在UTC中，然后只需将用户的时区偏移量添加到该时间即可.
Everywhere I read about converting time to a user's timezone says that the best method is to store a date and time in UTC then just add the user's timezone offset to this time.
如何以UTC时间存储日期?我使用MySQL DATETIME字段.
How can I store a date in UTC time? I use the MySQL DATETIME field.
在我的PHP代码中向MySQL添加新记录时，我将使用now()插入MySQL DATETIME.
When adding a new record to MySQL in my PHP code I would use now() to insert into MySQL DATETIME.
我需要使用不同于now()的东西来存储UTC时间吗?
Would I need to use something different than now() to store UTC time?
推荐答案
MySQL: UTC_TIMESTAMP()
返回当前UTC日期和时间 作为"YYYY-MM-DD HH:MM:SS"中的值或 YYYYMMDDHHMMSS.uuuuuu格式， 取决于功能是否 在字符串或数字上下文中使用
Returns the current UTC date and time as a value in 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS.uuuuuu format, depending on whether the function is used in a string or numeric context
PHP: gmdate()
PHP: gmdate()
PHP date_default_timezone_set() 是在PHP中用于设置脚本的当前时区.您可以将其设置为客户时区，以便所有格式设置功能都以当地时间返回时间.
Also PHP date_default_timezone_set() is used in PHP to set the current time zone for the script. You can set it to the client time zone so all the formatting functions return the time in his local time.
实际上，尽管我很难做到这一点，但总是偶然发现一些陷阱.例如.从MySQL返回的时间信息未格式化为'UTC'，因此 strtotime 转换如果您不小心，则将其转换为本地时间.我很想知道是否有人对此问题有一个可靠的解决方案，这种解决方案在日期跨越媒体边界时不会中断(HTTP-> PHP-> MySQL和MySQL-> PHP-> HTTP )，还考虑了XML和RSS/Atom.
In truth though I had a hard time getting this to work and always stumble into some gotcha. Eg. time information returned from MySQL is not formatted as 'UTC' so strtotime transforms it into a local time if you are not careful. I'm curious to hear if someone has a reliable solution for this problem, one that doesn't break when dates traverse media boundaries (HTTP->PHP->MySQL and MySQL->PHP->HTTP), also considering XML and RSS/Atom.
这篇关于在PHP/MySQL中将日期时间存储为UTC的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
