在 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()

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 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屋！