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

2022-01-16 00:00:00 sql timezone mysql convert-tz

MySQL 可以直接在普通的 select 语句中将存储的 UTC 时间转换为本地时区时间吗?

Can MySQL convert a stored UTC time to local time-zoned time directly in a normal select statement?

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

Let's say you have some data with a timestamp (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 格式获取所有日期.

I of course get all the dates as in their stored UTC form.

但是假设我想让它们在另一个时区(使用 DST)，然后我可以在选择查询中添加一些魔法，以便我在所选时区中获取所有日期吗?

But let's say that I would like to have them in another timezone (with DST), can I then add some magic to the select query so that I get all the dates back in the selected timezone?

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

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

Or must I do this in some other layer on top, like in some php code? (it seems to be how most people have solved this problem).

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

If your MySQL installation allows you to use CONVERT_TZ it is a very clean solution, this example shows how to use it.

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

但是我不知道这是否是一个好方法，因为某些 MySQL 安装缺少此功能，请谨慎使用.

However I don't know if this is a good way since some MySQL installation is missing this function, use with care.

推荐答案

是的，有 convert_tz 函数.

Yup, there's the convert_tz function.

相关文章