MySQL可以将存储的UTC时间转换为本地时区吗? [英] Can MySQL convert a stored UTC time to local timezone?

问题描述

MySQL是否可以在正常的select语句中直接将存储的UTC时间转换为本地时区:ed时间?

Can MySQL convert a stored UTC time to local timezon:ed 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.

推荐答案

是的，有

Yup, there's the convert_tz function.

这篇关于MySQL可以将存储的UTC时间转换为本地时区吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！