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

发布于 2024-08-19 16:43:23 字数 858 浏览 4 评论 0原文

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 形式获取所有日期。

但假设我想让它们位于另一个时区（采用夏令时），然后我可以在选择查询中添加一些魔法，以便我获得所选时区中的所有日期吗？

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

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

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

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

不过我不知道这是否是一个好方法，因为有些MySQL安装缺少这个功能，请小心使用。

原文

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

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`)
)

Then when I

"select value, date from SomeDateTable";

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

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";

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).

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' )

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

失而复得 2024-08-26 16:43:24

对于那些无法配置 mysql 环境（例如由于缺乏 SUPER 访问权限）以使用人类友好的时区名称（如“America/Denver”或“GMT”）的人，您还可以使用带有数字偏移量的函数，如下所示：

CONVERT_TZ(date,'+00:00','-07:00')

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')

回复收藏 0 原文

顾忌 2024-08-26 16:43:24

可以很容易地使用

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

例如：

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

Plus 这也可以在 WHERE 子句中使用以在转换时区后进行比较（但最好只在 UTC 时区进行比较并在显示时间戳时转换为本地时区）

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

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 clause to compare after the timezone is converted (but its always better to just compare in UTC timezones and convert to local timezone while displaying the timestamp)

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

回复收藏 0 原文

彩扇题诗 2024-08-26 16:43:24

select convert_tz(now(),@@session.time_zone,'+03:00')

为了获取时间仅使用：

time(convert_tz(now(),@@session.time_zone,'+03: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'))

回复收藏 0 原文

情话难免假 2024-08-26 16:43:24

1.正确设置您的服务器：

在服务器上，su到root并执行以下操作：（

# mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql mysql

请注意，最后的命令当然是mysql，并且，您正在发送它到一个恰好具有相同名称的表：mysql。）

接下来，您现在可以# ls /usr/share /zoneinfo 。

使用该命令可以查看 ubuntu 或几乎所有 unixish 服务器上的所有时区信息。

（顺便说一句，这是查找某个时区的确切官方名称的便捷方法。）

2. 这在 mysql 中就很简单了：

例如

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                     |
+---------------------+-----------------------------------------+

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                     |
+---------------------+-----------------------------------------+

回复收藏 0 原文