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屋!