如何在脚本中处理MySQL时区

Question

我正在开发一个移动应用程序。从应用程序中调用web服务，该服务基于模式(?mode=xx)运行不同的查询。

在其中一些查询中，我使用日期函数，如DATE(NOW())。

存储在MySQL数据库中的数据存储在GMT-7 (加拿大山区时间)中.

我还没有为这个web服务注册一个域/主机，但是当我这样做的时候，让我说它是托管在另一个城市，如多伦多(这是格林尼治时间-5-2小时前)。然后，在加拿大山区时间晚上10:05分，一个用户使用该应用程序发送一个web请求调用，该请求具有如下查询：

SELECT DATE(NOW()) 

因为服务器托管在多伦多，这将返回明天的日期，即使用户所在的位置是前一天，并且应用程序显示基于当前日期的数据。

有人对此有什么想法吗？

编辑：

SYSTEM
2015-01-29 16:19:48
2015-01-29 23:19:48

是运行查询选择@@time_zone，now()，utc_timestamp()的结果

查询处理日期(Yyyy)和时间(hh:mm:ss)列类型。

Answer 1

您在您的MySQL服务器上运行了这次诊断查询。

select @@time_zone, now(), utc_timestamp()

从本地时间和utc时间可以清楚地看出，服务器机器的系统时区设置是“加拿大/山区”，而MySQL服务器软件没有自己的时区设置。

如果您拿起您的表并将它们不变地移动到附近某个时区的服务器上，您可以随时更新您的软件以发出命令。

set time_zone = 'Canada/Mountain';

就在你从你的软件连接之后。这将使您的新MySQL连接的行为与您当前的连接在时区上的行为类似.如果您拥有MySQL服务器，则可以根据此页面上的说明设置其默认时区。http://dev.mysql.com/doc/refman/5.5/en/time-zone-support.html

下面是关于时间数据类型的故事。timezone-ignorant.，DATE，TIME，DATETIME都是一旦存储了日期/时间值，即使更改了时区设置，也会得到相同的值。

timezone-sensitive.是TIMESTAMP数据类型。这些数据项总是存储在UTC，又称Z，time中，以前称为格林尼治标准时间。它们总是在存储时转换为UTC，在被检索时总是转换回UTC。

获取当前日期和时间的内建函数 (NOW()和朋友)为NOW()。他们会在当地时间获得价值。例外是从UTC_开始的三个函数，它们在UTC时间内产生值。

许多MySQL多时区应用程序使用以下操作规程：

1. 向每个用户询问一个用户偏好时区，或者从其他一些关于用户的个人数据中找出。(电话将这些信息从网络提供给他们。)代表用户将其存储为区域信息友好时区描述符(“America/New”、“Canada/ time”、“Europe/维也纳”等)。
2. 代表用户建立MySQL会话后，使用set time_zone查询设置用户的时区，如上面所示。您应该在connect操作之后立即执行此操作。
3. 将用户的日期和时间存储到TIMESTAMP数据类型中。当它们被储存起来时，它们会被转换成UTC。
4. 根据需要取回它们。他们会改回当地时间。

这个想法是你的用户的时区是她上下文的一部分。这很好，因为如果用户A在温哥华，用户B在哈利法克斯，并且由于某种原因，用户B查看用户A的时间数据，它将在大西洋时间或多或少自动显示给B。

这也是好的，因为它透明地处理全球变化无常的白天到标准时间的变化。去年夏天的一个时间戳将在去年夏天的当地时间展出。

许多用于全局使用的服务器管理器将其系统服务器时间或其MySQL默认时区设置为UTC。(你的不是。)

处理这一切的另一种方法是你已经开始的方式。选择一个时区并存储与该时区相关的时间戳。最好是选择一个时区，在这种情况下，这个时区不能在白天和标准时间之间交替。然后，当将时间存储到数据库中时，转换外植性。你可以在渥太华通过做这样的事情来存储用户的时间。

INSERT INTO tbl (appt) VALUES ( 'whatever-time' - INTERVAL 120 MINUTE)

你也会用同样的方式得到这些值。这是容易出错的，但你可以让它工作。

最后，您可以自己进行转换。如果您想知道某些任意时区和UTC之间有多少分钟的偏移量，请尝试这两个查询。

set time_zone = 'Canada/Atlantic';
select timestampdiff(minute, utc_timestamp(), now());

在每年的这个时候-240，也就是-4:00。你需要使用分钟，而不是小时，因为半小时或四分之一小时时区抵消在一些国家。

最后，小心点。TIMESTAMP数据类型并不表示1970年之前的时间。而且，在我的MariaDB 10.0的例子中，它似乎在2038-01-19T03:14:07 UTC之后的桶里下地狱，时间从32位开始滚动。