如何在MySql中将unix时间戳转换为UTC日期?

I've a bunch of data stored in a mysql database as a bunch of unix style timestamps (in UTC). Previously, these have been formatted via Perl's gmtime(). It seems that MySQL's FROM_UNIXTIME() function tries to be helpful, and correct for daylight savings, but I really don't want it to.

我有一堆数据存储在mysql数据库中，作为一堆unix风格的时间戳(UTC)。以前，这些都是通过Perl的gmtime()进行格式化的。MySQL的FROM_UNIXTIME()函数似乎很有用，并且对于日光节约是正确的，但我真的不想这样做。

Is there a nice handy method to convert the results from the system timezone (GMT/BST) to UTC or alternately determine if DST was in effect for a particular time?

是否有一种方便的方法将系统时区(GMT/BST)的结果转换为UTC，或者交替地确定DST是否在特定时间内有效?

1 个解决方案

#1

Have you tried:

你有试过:

mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2011-04-04 22:54:13 |
+---------------------+
1 row in set (0.00 sec)

mysql> set @var = unix_timestamp(NOW());
Query OK, 0 rows affected (0.00 sec)

mysql> SET time_zone='+00:00';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2011-04-04 20:54:23 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT FROM_UNIXTIME(@var);
+---------------------+
| FROM_UNIXTIME(@var) |
+---------------------+
| 2011-04-04 20:54:17 |
+---------------------+
1 row in set (0.02 sec)

If you want to use named timezones, you can see this page for more information, and how to fill the timezone tables on your system.

如果您想使用命名时区，可以在此页面查看更多信息，以及如何填充系统上的时区表。

#1