跳到主要内容

CONVERT_TZ

描述

转换 datetime 值,从 from_tz 给定时区转到 to_tz 给定时区,并返回结果值,时区设置请查看 时区管理 文档。

该函数与 mysql 中的 convert_tz 函数 行为一致

语法

CONVERT_TZ(<date_or_time_expr>, <from_tz>, <to_tz>)

参数

参数说明
<date_or_time_expr>需要被转换的值,为 datetime 或者 date 类型,具体 datetime 和 date 格式请查看 datetime 的转换date 的转换)
<from_tz>dt 的原始时区,该参数为 varchar 类型
<to_tz>需要转换的时区,该参数为 varchar 类型

返回值

  • 转换后的值,类型为 datetime
  • 返回的 scale 跟输入的 scale 相同
    • 不带有 scale 的 datetime 输入,返回结果也不带有 scale
    • 带有 scale 的输入,返回的结果带有相同的 scale

特殊情况:

  • 如果任何参数为 NULL。返回 NULL。
  • 当输入的时区不合法的时候,返回错误,时区的设置参考 时区管理
  • 输入为 date 类型,时间部分自动转换为 00:00:00

示例


---中国上海时间转换到美国洛杉矶
mysql> select CONVERT_TZ(CAST('2019-08-01 13:21:03' AS DATETIME), 'Asia/Shanghai', 'America/Los_Angeles');
+---------------------------------------------------------------------------+
| CONVERT_TZ('2019-08-01 13:21:03', 'Asia/Shanghai', 'America/Los_Angeles') |
+---------------------------------------------------------------------------+
| 2019-07-31 22:21:03 |
+---------------------------------------------------------------------------+

---将 东八区(+08:00)的时间 '2019-08-01 13:21:03' 转换为 美国洛杉矶
select CONVERT_TZ(CAST('2019-08-01 13:21:03' AS DATETIME), '+08:00', 'America/Los_Angeles');

+--------------------------------------------------------------------+
| convert_tz('2019-08-01 13:21:03', '+08:00', 'America/Los_Angeles') |
+--------------------------------------------------------------------+
| 2019-07-31 22:21:03 |
+--------------------------------------------------------------------+

---输入为 date 类型,输出为 datetime 类型,时间部分自动转换为 00:00:00
mysql> select CONVERT_TZ(CAST('2019-08-01 13:21:03' AS DATE), 'Asia/Shanghai', 'America/Los_Angeles');
+-------------------------------------------------------------------------------------------+
| CONVERT_TZ(CAST('2019-08-01 13:21:03' AS DATEV2), 'Asia/Shanghai', 'America/Los_Angeles') |
+-------------------------------------------------------------------------------------------+
| 2019-07-31 09:00:00 |
+-------------------------------------------------------------------------------------------+

---转换时间为NULL,输出NULL

mysql> select CONVERT_TZ(NULL, 'Asia/Shanghai', 'America/New_York');
+-------------------------------------------------------+
| CONVERT_TZ(NULL, 'Asia/Shanghai', 'America/New_York') |
+-------------------------------------------------------+
| NULL |
+-------------------------------------------------------+

---任一时区为NULL,返回NULL
mysql> select CONVERT_TZ('2019-08-01 13:21:03', NULL, 'America/Los_Angeles');
+----------------------------------------------------------------+
| CONVERT_TZ('2019-08-01 13:21:03', NULL, 'America/Los_Angeles') |
+----------------------------------------------------------------+
| NULL |
+----------------------------------------------------------------+

mysql> select CONVERT_TZ('2019-08-01 13:21:03', '+08:00', NULL);
+---------------------------------------------------+
| CONVERT_TZ('2019-08-01 13:21:03', '+08:00', NULL) |
+---------------------------------------------------+
| NULL |
+---------------------------------------------------+

---带有 scale 的时间
mysql> select CONVERT_TZ('2019-08-01 13:21:03.636', '+08:00', 'America/Los_Angeles');
+------------------------------------------------------------------------+
| CONVERT_TZ('2019-08-01 13:21:03.636', '+08:00', 'America/Los_Angeles') |
+------------------------------------------------------------------------+
| 2019-07-31 22:21:03.636 |
+------------------------------------------------------------------------+

---当输入的时区不合法的时候,返回错误
select CONVERT_TZ(CAST('2019-08-01 13:21:03' AS DATETIME), '+08:00', 'America/Los_Anges');
ERROR 1105 (HY000): errCode = 2, detailMessage = (10.16.10.3)[INVALID_ARGUMENT][E33] Operation convert_tz invalid timezone: America/Los_Anges