Mysql将TIMESTAMP转换为INTEGER - 时区

I need to convert some TIMESTAMP fields to INT in our MySQL (InnoDB) DB. I realize that converting a TIMESTAMP to INT is unusual, but we still need to do it :)

我需要在MySQL(InnoDB)DB中将一些TIMESTAMP字段转换为INT。我意识到将TIMESTAMP转换为INT是不寻常的,但我们仍然需要这样做:)

It seems straight-forward enough to do, but there are some timezone and daylight saving errors.

看起来很简单,但有一些时区和夏令时错误。

I have a script that generates my SQL code per column. For example, it generates:

我有一个脚本,每列生成我的SQL代码。例如,它生成:

ALTER TABLE alarmLog ADD COLUMN started_tmp INT UNSIGNED;
UPDATE alarmLog SET started_tmp = UNIX_TIMESTAMP(started);
ALTER TABLE alarmLog DROP started;
alter TABLE alarmLog CHANGE started_tmp started INT UNSIGNED NULL DEFAULT 0;

If I compare the before and after data using select FROM_UNIXTIME(1291788036);, the result looks good.

如果我使用select FROM_UNIXTIME(1291788036);比较之前和之后的数据,结果看起来不错。

The idea is then to change all the client-side software to convert to UTC and use that INT when storing it. When retrieving, that INT is converted to the current time zone.

然后,我们的想法是将所有客户端软件更改为转换为UTC,并在存储时使用该INT。检索时,该INT将转换为当前时区。

But then the docs warn me about this scenario (daylight savings in CET):

但随后文档警告我这个场景(CET中的夏令时):

mysql> SELECT UNIX_TIMESTAMP('2005-03-27 02:00:00');
+---------------------------------------+
| UNIX_TIMESTAMP('2005-03-27 02:00:00') |
+---------------------------------------+
|                            1111885200 |
+---------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT UNIX_TIMESTAMP('2005-03-27 03:00:00');
+---------------------------------------+
| UNIX_TIMESTAMP('2005-03-27 03:00:00') |
+---------------------------------------+
|                            1111885200 |
+---------------------------------------+
1 row in set (0.00 sec)

How do API and OS's normally deal with daylight savings? I know my PC has its clock in UTC and in summer time, the OS adds two hours to it, and in winter time one. I assume it uses the UTC time to determine whether it's DST or not.

API和OS通常如何处理夏令时?我知道我的电脑有时钟在UTC和夏天,操作系统增加两个小时,冬天时间一个。我假设它使用UTC时间来确定它是否是DST。

So, how do I deal with this? Is the only solution to add a field to the database to specify DST offset?

那么,我该如何处理呢?是否是向数据库添加字段以指定DST偏移的唯一解决方案?

1 个解决方案

#1

You don't need to store the time in INT's. MySQL's TIMESTAMP type does that anyway (it uses standard unix timestamps to store the time) and they are always in UTC.

您不需要将时间存储在INT中。 MySQL的TIMESTAMP类型无论如何都会这样做(它使用标准的unix时间戳来存储时间)并且它们始终是UTC。

You only need to set the session timezone and all TIMESTAMP columns will be converted from/to your zone when you update/select them.

您只需设置会话时区,并且在更新/选择时,所有TIMESTAMP列都将从/转换为您的区域。

You can set the zone at connect/initialization time once:

您可以在连接/初始化时将区域设置一次:

SET time_zone = '+10:00';

And then you can select/update the time in your zone directly

然后,您可以直接选择/更新区域中的时间

SELECT timesamp_column FROM table ...

I'm not very familiar with datetime libs but I guess they use the timezone you provided and the time in question to determine timezone and daylight savings offsets.

我对datetime libs不是很熟悉,但我猜他们会使用你提供的时区和时间来确定时区和夏令时偏移量。

In the example you provided I think one of the values is actually invalid, because the clock is suppose to jump from 01:59:59 to 03:00:00 and 02:00:00 never actually happened. The UNIX_TIMESTAMP function probably returns the nearest second in that case.

在你提供的例子中,我认为其中一个值实际上是无效的,因为时钟假设从01:59:59跳到03:00:00和02:00:00从未实际发生过。在这种情况下,UNIX_TIMESTAMP函数可能返回最接近的秒。

#1