如何在脚本中处理MySQL时区 [英] How to handle MySQL timezone in script

问题描述

我正在开发移动应用程序。从应用程序调用到基于模式运行不同查询的Web服务（？mode = xx）

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

存储在MySQL数据库中的数据存储在GMT-7（加拿大山区时间）。

$ b $我还没有注册一个这个网络服务的域名/主机，但是当我允许我们说，它是在不同的城市，如多伦多（这是GMT - 5 - 2小时前）。然后在10:05 pm加拿大山区时间，用户使用该应用程序发送一个具有以下查询的Web请求呼叫：

  SELECT DATE（NOW（））
  

由于服务器托管在多伦多，这将返回明天的日期，甚至虽然用户是前一天，应用程序根据当天显示数据。

任何人都有任何想法？

编辑：

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

是运行查询的结果选择@@ time_zone，now（），utc_timestamp（）

查询处理日期（yyyy-mm-dd）和时间（hh：mm：ss）列类型。

解决方案

您在MySQL服务器上运行了这个时间诊断查询。

  select @@ time_zone，now（），utc_timestamp（）
  

从当地时间和utc tim可以清楚你的服务器机器的系统时区设置是加拿大/山，MySQL服务器软件没有自己的时区设置。

如果你拿起你的表格，并将其移动到附近时区的服务器，您可以更新软件，始终发出命令

  set time_zone = 加拿大/山;从您的软件连接后，
  

。这将使您的新MySQL连接行为像您当前的时区一样。如果您拥有MySQL服务器，则可以根据本页上的说明设置其默认时区。 http://dev.mysql.com/doc/refman /5.5/en/time-zone-support.html

以下是有关时间数据类型的故事。 DATE ， TIME 和 DATETIME 都是时区无知。存储日期/时间值后，即使您更改时区设置，也可以恢复相同的值。

TIMESTAMP 数据类型为时区敏感。这些数据项始终存储在 UTC，也称为Z，时间，以前称为格林尼治标准时间。它们在存储时总是转换为UTC，并且在被检索时总是转换回来。

内置函数获取当前日期和时间（ NOW（）和朋友）是时区敏感。他们会在当地时间产生价值。例外情况是以UTC时间为单位的 UTC _ 开始的三个函数。

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

1. 向每个用户询问用户偏好时区，或从其他一些有关用户的个人资料。 （电话将这些信息从网络中提供给他们。）将其存储为 zoneinfo-friendly 时间区域描述符（'America / New_York'，'Canada / Mountain'，'Europe / Vienna'等）。


2. 代表用户建立MySQL会话，设置用户的时区，使用 set time_zone 查询，如上所示。您应该在连接操作后立即执行此操作。


3. 将用户的日期和时间存入 TIMESTAMP 数据类型。它们将被存储时被转换为UTC。


4. 根据需要检索它们。他们将被转换回当地时间。

这个想法是，您的用户的时区是她上下文的一部分。这样做很好，因为如果用户A位于哈利法克斯的温哥华和用户B，并且由于某些原因，用户B查看用户A的时间数据，则将自动显示为大西洋时间的B。 b
$ b

这也很好，因为它透明地处理了全球变幻莫测的日光时间变化。去年夏天的时间戳将显示在去年夏天的当地时间。

许多用于全球使用的服务器管理器将其系统服务器时间或其MySQL默认时区设置为世界标准时间。 （你不会。）

另一种处理这一切的方式是您开始的方式。选择时区并存储相对于该时区的时间戳。在这种情况下，您最好选择不在白天和标准时间之间交替的时区。然后，当将时间存储到数据库中时，转换显示。您可以通过这样做来存储渥太华用户的时间。

  INSERT INTO tbl（appt）VALUES（'时间' -  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之后进入地狱当时时间超过32位的UTC UTC。

I am developing a mobile application. From the application calls are made to a web service which runs different queries based on mode (?mode=xx)

In some of those queries I use date functions like DATE(NOW()).

The data stored in the MySQL database is stored in GMT-7 (Mountain Time Canada).

I have yet to register a domain/host for this web service but when I do lets say it is hosted in a different city such as Toronto (which is GMT-5 - 2 hours ahead). Then at 10:05pm Mountain Time Canada a user uses the application to send a web request call which has a query like:

SELECT DATE(NOW()) 

Because the server is hosted in Toronto, that will return tomorrow's date, even though where the user is it is the day before and the application shows data based on the current day.

Anyone have any ideas on this?

Edit:

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

is the result of running the query select @@time_zone, now(), utc_timestamp()

The queries deal with date (yyyy-mm-dd) and time (hh:mm:ss) column type.

解决方案

You ran this time-diagnostic query on your MySQL server.

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

It's clear from your local time and utc time that your server machine's system time zone setting is 'Canada/Mountain', and the MySQL server software doesn't have its own timezone setting.

If you pick up your tables and move them unchanged to a server in some nearby timezone, you can update your software always to issue the command

set time_zone = 'Canada/Mountain';

right after you connect from your software. This will make your new MySQL connection behave like your current one does time-zone-wise. If you own the MySQL server you can set its default time zone according to the directions on this page. http://dev.mysql.com/doc/refman/5.5/en/time-zone-support.html

Now, here's the story about time data types. DATE, TIME, and DATETIME are all timezone-ignorant. Once you've stored a date/time value you'll get it back the same value even if you change your timezone settings.

The TIMESTAMP data type is timezone-sensitive. Those data items are always stored in UTC, also known as Z, time, formerly known as Greenwich Mean Time. They're always converted to UTC upon being stored, and always converted back upon being retrieved.

The builtin functions for getting current date and time (NOW() and friends) are timezone-sensitive. They'll yield values in local time. The exceptions are the three functions starting with UTC_ which yield values in UTC time.

Many MySQL multi-time-zone applications use the following operational discipline:

1. Ask each user for a user-preference time zone, or figure it out from some other bit of personal data about the user. (Telephones have this information provisioned into them from the network.) Store that as a zoneinfo-friendly time zone descriptor ('America/New_York', 'Canada/Mountain', 'Europe/Vienna', etc) on the user's behalf.
2. Upon establishing a MySQL session on behalf of the user, set the user's time zone with a set time_zone query like the one shown above. You should do this right after your connect operation.
3. Store dates and times for users into TIMESTAMP data types. They'll get converted to UTC as they're stored.
4. Retrieve them as needed. They'll get converted back to local time.

The idea is that your user's timezone is part of her context. This works well, because if user A is in Vancouver and user B in Halifax, and for some reason user B views user A's time data, it will be shown to B in Atlantic time more-or-less automatically.

It's also good because it deals transparently with the global vagaries of daylight-to-standard time changing. A timestamp from last summer will be displayed in last summer's local time.

Many managers of servers for global use set their system server time, or their MySQL default time zone, to UTC. (Yours doesn't.)

Another way to handle all this is the way in which you've started. Pick a time zone and store your timestamps with respect to that time zone. It's best if you pick a timezone that doesn't alternate between daylight and standard time in that case. Then, when storing times into the database, convert explicity. You'd store times from users in Ottawa by doing something like this.

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

and you'd get the values out the same way. This is error-prone but you can make it work.

Finally, you can do your conversions yourself. If you want to know how many minutes of offset there are between some arbitary timezone and UTC, try these two queries.

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

At this time of year that gives back -240, which is -4:00. You need to use minutes rather than hours because of half-hour or quarter-hour timezone offsets in some countries.

Finally, watch out. TIMESTAMP data types don't represent times before 1970. And, on my MariaDB 10.0 instance it appears to go to hell in a bucket right after 2038-01-19T03:14:07 UTC when the time rolls over out of 32 bits.

这篇关于如何在脚本中处理MySQL时区的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！