MySQL 可以将存储的 UTC 时间转换为本地时区吗？

MySQL 能否在普通的 select 语句中直接将存储的 UTC 时间转换为本地时区时间？

假设您有一些带有时间戳(UTC)的数据。

CREATE TABLE `SomeDateTable` (
`id`    int(11) NOT NULL auto_increment,
`value` float NOT NULL default '0',
`date`  datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY  (`id`)
)

然后当我

"select value, date from SomeDateTable";

当然，我得到的所有日期都是以它们存储的 UTC 格式表示的。

但是，让我们说，我希望他们在另一个时区(与 DST) , 然后，我可以添加一些魔术的选择查询，以便我得到所有的日期回到选定的时区？

"select value, TIMEZONE(date, "Europe/Berlin") from SomeDateTable";

或者我必须在顶部的其他层中执行这个操作，比如在某些 PHP 代码中？ (这似乎是大多数人解决这个问题的方法)。

如果您的 MySQL 安装允许您使用 CONVERT _ TZ，这是一个非常干净的解决方案, 这个例子展示了如何使用它。

SELECT CONVERT_TZ( '2010-01-01 12:00', 'UTC', 'Europe/Stockholm' )

然而，我不知道这是否是一个好方法，因为一些 MySQL 安装缺少这个功能，谨慎使用。

147966 次浏览
小开
最佳答案

Yup, there's the convert_tz function.

小开

I propose to use

SET time_zone = 'proper timezone';

being done once right after connect to database. and after this all timestamps will be converted automatically when selecting them.

小开

For those unable to configure the mysql environment (e.g. due to lack of SUPER access) to use human-friendly timezone names like "America/Denver" or "GMT" you can also use the function with numeric offsets like this:

CONVERT_TZ(date,'+00:00','-07:00')
小开 
select convert_tz(now(),@@session.time_zone,'+03:00')

For get the time only use:

time(convert_tz(now(),@@session.time_zone,'+03:00'))
小开

One can easily use 

CONVERT_TZ(your_timestamp_column_name, 'UTC', 'your_desired_timezone_name')

For example:

CONVERT_TZ(timeperiod, 'UTC', 'Asia/Karachi')

Plus this can also be used in WHERE statement and to compare timestamp i would use the following in Where clause:

WHERE CONVERT_TZ(timeperiod, 'UTC', '{$this->timezone}') NOT BETWEEN {$timeperiods['today_start']} AND {$timeperiods['today_end']}
小开

1. Correctly setup your server:

On server, su to root and do this:

# mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql mysql

(Note that the command at the end is of course mysql , and, you're sending it to a table which happens to have the same name: mysql.)

Next, you can now # ls /usr/share/zoneinfo .

Use that command to see all the time zone info on ubuntu or almost any unixish server.

(BTW that's the convenient way to find the exact official name of some time zone.)

2. It's then trivial in mysql:

For example

mysql> select ts, CONVERT_TZ(ts, 'UTC', 'Pacific/Tahiti') from example_table ;
+---------------------+-----------------------------------------+
| ts                  | CONVERT_TZ(ts, 'UTC', 'Pacific/Tahiti') |
+---------------------+-----------------------------------------+
| 2020-10-20 16:59:57 | 2020-10-20 06:59:57                     |
| 2020-10-20 17:02:59 | 2020-10-20 07:02:59                     |
| 2020-10-20 17:30:08 | 2020-10-20 07:30:08                     |
| 2020-10-20 18:36:29 | 2020-10-20 08:36:29                     |
| 2020-10-20 18:37:20 | 2020-10-20 08:37:20                     |
| 2020-10-20 18:37:20 | 2020-10-20 08:37:20                     |
| 2020-10-20 19:00:18 | 2020-10-20 09:00:18                     |
+---------------------+-----------------------------------------+

提交答案