时区转换

跨时区数据处理是全球化应用中的常见需求，本篇介绍各数据库的时区转换方法。

📊 支持情况

1. MySQL 时区转换

CONVERT_TZ 函数

-- 设置时区
SET time_zone = '+08:00';

-- UTC转北京时间（UTC+8）
SELECT CONVERT_TZ('2024-01-14 10:00:00', 'UTC', '+08:00');  -- 2024-01-14 18:00:00

-- 北京时间转UTC
SELECT CONVERT_TZ('2024-01-14 18:00:00', '+08:00', 'UTC');  -- 2024-01-14 10:00:00

-- 纽约时间转北京时间
SELECT CONVERT_TZ('2024-01-14 08:00:00', 'America/New_York', 'Asia/Shanghai');
-- 2024-01-14 21:00:00（纽约-5，北京+8，相差13小时）

查看可用时区

SELECT * FROM mysql.time_zone_name;
SELECT @@system_time_zone;

实战：用户本地时间显示

-- 假设存储的是UTC时间，用户时区为America/New_York
SELECT
    order_id,
    order_time_utc,
    CONVERT_TZ(order_time_utc, 'UTC', 'America/New_York') AS order_time_ny
FROM orders;

2. Oracle 时区转换

AT TIME ZONE

-- 查看当前时区
SELECT SESSIONTIMEZONE FROM dual;
SELECT DBTIMEZONE FROM dual;

-- 时区转换
SELECT
    SYSTIMESTAMP AS utc_time,
    SYSTIMESTAMP AT TIME ZONE 'America/New_York' AS ny_time,
    SYSTIMESTAMP AT TIME ZONE 'Asia/Shanghai' AS sh_time
FROM dual;

-- 指定时间戳转换
SELECT
    TIMESTAMP '2024-01-14 10:00:00 UTC' AT TIME ZONE 'America/New_York' AS ny_time
FROM dual;

-- 从表字段转换
SELECT
    order_time,
    order_time AT TIME ZONE 'Asia/Shanghai' AS local_time
FROM orders;

时区区域名

-- Oracle支持的时区区域名
SELECT
    order_time,
    order_time AT TIME ZONE 'UTC' AS utc_time,
    order_time AT TIME ZONE 'Asia/Shanghai' AS sh_time,
    order_time AT TIME ZONE 'America/Los_Angeles' AS la_time
FROM orders;

3. ClickHouse 时区转换

toTimeZone

-- UTC转上海时间
SELECT toDateTime('2024-01-14 10:00:00', 'UTC') AS utc_time,
       toDateTime('2024-01-14 10:00:00', 'UTC') AS sh_time;

-- 使用toTimeZone
SELECT
    toDateTime('2024-01-14 10:00:00', 'UTC') AS utc_time,
    toDateTime('2024-01-14 10:00:00', 'UTC') AS sh_time;

-- 更简洁的方式
SELECT
    now() AS utc_now,
    now() AS sh_now;

timezone函数

-- 设置会话时区
SET timezone = 'Asia/Shanghai';

-- 查看当前时区
SELECT timezone();

4. Hologres / PostgreSQL 时区

AT TIME ZONE

-- UTC转指定时区
SELECT TIMESTAMP '2024-01-14 10:00:00' AT TIME ZONE 'Asia/Shanghai';

-- 表字段转换
SELECT
    created_at,
    created_at AT TIME ZONE 'Asia/Shanghai' AS local_time
FROM events;

-- 转换为UTC
SELECT
    created_at AT TIME ZONE 'UTC' AS utc_time
FROM events;

设置会话时区

SET timezone = 'America/New_York';
SHOW timezone;

5. Hive / MaxCompute 时区

Hive时区设置

-- 设置Hive会话时区
SET hive.cli.print.time=true;
SET hive.cli.timezone=Asia/Shanghai;

-- 时间戳转换（需要UDF）
SELECT from_utc_timestamp(timestamp '2024-01-14 10:00:00', 'Asia/Shanghai');
SELECT to_utc_timestamp(timestamp '2024-01-14 18:00:00', 'Asia/Shanghai');

MaxCompute时区

-- MaxCompute使用东8区时间
SET odps.sql.timezone = Asia/Shanghai;

-- 时区相关函数有限，建议在业务层处理

6. 常用时区代码

标准时区

7. 实战案例

场景1：全球用户访问日志

-- MySQL - 将UTC时间转为用户本地时间
SELECT
    user_id,
    access_time_utc,
    CONVERT_TZ(access_time_utc, 'UTC', user_timezone) AS local_time
FROM access_logs
JOIN users ON access_logs.user_id = users.id;

场景2：跨时区航班查询

-- Oracle - 航班时刻显示
SELECT
    flight_id,
    departure_time,
    departure_time AT TIME ZONE 'Asia/Shanghai' AS departure_shanghai,
    departure_time AT TIME ZONE 'America/Los_Angeles' AS departure_la
FROM flights;

场景3：定时任务调度

-- MySQL - 按用户时区发送通知
SELECT
    user_id,
    scheduled_time_utc,
    CONVERT_TZ(scheduled_time_utc, 'UTC', user_timezone) AS local_scheduled_time
FROM notifications
WHERE status = 'pending';

场景4：报表按不同时区展示

-- MySQL - 多时区报表
SELECT
    DATE(CONVERT_TZ(order_time, 'UTC', 'Asia/Shanghai')) AS cn_date,
    SUM(amount) AS cn_sales,
    DATE(CONVERT_TZ(order_time, 'UTC', 'America/New_York')) AS us_date,
    SUM(CASE WHEN region = 'US' THEN amount ELSE 0 END) AS us_sales
FROM orders
GROUP BY cn_date, us_date;

8. Unix时间戳时区

UTC时间戳转换

-- MySQL
SELECT FROM_UNIXTIME(1705225800);                         -- UTC时间
SELECT CONVERT_TZ(FROM_UNIXTIME(1705225800), 'UTC', '+08:00');  -- 北京时间

-- Oracle - 时间戳转UNIX
SELECT (SYSTIMESTAMP - TIMESTAMP '1970-01-01 00:00:00 UTC') / INTERVAL '1' SECOND
FROM dual;

-- ClickHouse
SELECT toDateTime(1705225800) AS utc_time;
SELECT toDateTime(1705225800) AS utc_time,
       toDateTime(1705225800) + INTERVAL 8 HOUR AS sh_time;

⚠️ 注意事项

1. 夏令时问题

-- 夏令时期间时间会有额外变化
-- 美国夏令时：3月第二个周日到11月第一个周日

-- Oracle 自动处理夏令时
SELECT
    TIMESTAMP '2024-03-10 02:30:00 America/New_York' AT TIME ZONE 'UTC'
FROM dual;
-- 结果会正确处理DST切换

2. 边界时间

-- 跨日期边界
SELECT CONVERT_TZ('2024-01-14 23:00:00', '+08:00', '-05:00');
-- 2024-01-14 10:00:00（跨日）

3. NULL值处理

-- 时区转换遇到NULL
SELECT CONVERT_TZ(NULL, 'UTC', '+08:00');  -- NULL

📝 练习题

-- 建表
CREATE TABLE global_orders (
    order_id INT,
    order_time_utc TIMESTAMP,
    user_id INT,
    amount DECIMAL(10,2)
);

INSERT INTO global_orders VALUES
(1, '2024-01-14 10:30:00', 101, 500),
(2, '2024-01-14 18:45:00', 102, 800),
(3, '2024-01-15 02:00:00', 103, 600);

要求：

1. 将UTC时间转换为北京时间（UTC+8）
2. 转换为纽约时间（EST/UTC-5）
3. 找出北京时间上午9点到12点之间的订单
4. 按北京日期分组统计

-- 参考答案（MySQL）
-- 1. UTC转北京时间
SELECT
    order_id,
    order_time_utc,
    CONVERT_TZ(order_time_utc, 'UTC', '+08:00') AS beijing_time
FROM global_orders;

-- 2. UTC转纽约时间
SELECT
    order_id,
    order_time_utc,
    CONVERT_TZ(order_time_utc, 'UTC', '-05:00') AS ny_time
FROM global_orders;

-- 3. 北京时间上午订单
SELECT *
FROM global_orders
WHERE CONVERT_TZ(order_time_utc, 'UTC', '+08:00') >= '2024-01-14 09:00:00'
  AND CONVERT_TZ(order_time_utc, 'UTC', '+08:00') < '2024-01-14 12:00:00';

-- 4. 按北京日期分组
SELECT
    DATE(CONVERT_TZ(order_time_utc, 'UTC', '+08:00')) AS beijing_date,
    COUNT(*) AS order_count,
    SUM(amount) AS total_sales
FROM global_orders
GROUP BY beijing_date;