OceanBase 时间类型：timestamp 与 timestamptz 选择与时区处理

一、时间类型的双胞胎兄弟

在数据库里，timestamp和timestamptz就像一对双胞胎，长得像但性格迥异。简单来说：

• timestamp是个耿直boy，存什么时间就显示什么时间，不带时区信息
• timestamptz是个贴心暖男，会自动处理时区转换

举个生活中的例子：你约纽约的朋友明天10点视频，如果用timestamp只存"10:00"，对方得自己算时差。而timestamptz会存"10:00+8时区"，自动转换成纽约时间。

-- OceanBase示例：创建包含两种时间类型的表
CREATE TABLE meeting_schedule (
    event_id NUMBER,
    local_time TIMESTAMP,         -- 不带时区的时间
    global_time TIMESTAMP WITH TIME ZONE  -- 带时区的时间
);

-- 插入数据（假设当前时区是东八区）
INSERT INTO meeting_schedule VALUES (
    1, 
    TO_TIMESTAMP('2023-06-15 14:30:00', 'YYYY-MM-DD HH24:MI:SS'),
    TO_TIMESTAMP_TZ('2023-06-15 14:30:00 +08:00', 'YYYY-MM-DD HH24:MI:SS TZH:TZM')
);

二、时区处理的魔法时刻

timestamptz最神奇的能力是自动时区转换。来看个实际场景：

-- 修改会话时区为纽约（西五区）
ALTER SESSION SET TIME_ZONE = 'America/New_York';

-- 查询数据
SELECT 
    local_time,                          -- 显示原样：2023-06-15 14:30:00
    global_time,                         -- 自动转换：2023-06-15 02:30:00 -04:00
    SYS_EXTRACT_UTC(global_time) AS utc  -- 提取UTC时间：2023-06-15 06:30:00
FROM meeting_schedule;

注意三个细节：

1. local_time原封不动显示插入的值
2. global_time自动减了12小时（因为纽约比北京时间晚12小时）
3. 用SYS_EXTRACT_UTC可以提取标准的UTC时间

三、选型决策指南

场景1：固定时间记录

比如"用户注册时间"，用timestamp更合适：

CREATE TABLE users (
    user_id NUMBER,
    username VARCHAR2(50),
    register_time TIMESTAMP  -- 注册时间不需要时区转换
);

场景2：跨国业务系统

比如"全球会议系统"，必须用timestamptz：

CREATE TABLE international_meetings (
    meeting_id NUMBER,
    start_time TIMESTAMP WITH TIME ZONE,  -- 自动适配参会者时区
    end_time TIMESTAMP WITH TIME ZONE
);

-- 插入伦敦时间下午3点的会议
INSERT INTO international_meetings VALUES (
    1,
    TO_TIMESTAMP_TZ('2023-06-20 15:00:00 Europe/London', 'YYYY-MM-DD HH24:MI:SS TZR'),
    TO_TIMESTAMP_TZ('2023-06-20 16:30:00 Europe/London', 'YYYY-MM-DD HH24:MI:SS TZR')
);

性能考量

• timestamp存储空间更小（通常少8字节）
• timestamptz在查询时需要计算时区，有轻微性能开销

四、避坑实践手册

坑1：时区设置不一致

-- 错误示范：客户端和服务端时区不同步
-- 服务端时区：Asia/Shanghai
-- 客户端时区：UTC

INSERT INTO log_events VALUES (
    1,
    SYSTIMESTAMP,  -- 服务端时间：2023-06-15 16:00:00 +08:00
    '系统启动'
);

-- 客户端查询会显示错误时间：2023-06-15 08:00:00

解决方案：

-- 明确指定时区
ALTER SESSION SET TIME_ZONE = 'Asia/Shanghai';
-- 或使用统一UTC时间
INSERT INTO log_events VALUES (1, SYS_EXTRACT_UTC(SYSTIMESTAMP), '系统启动');

坑2：时间计算错误

-- 计算两个时区的时间差（错误方式）
SELECT 
    end_time - start_time AS duration  -- 错误！时区不同直接相减
FROM international_meetings;

-- 正确方式：先转为epoch秒再计算
SELECT 
    (EXTRACT(EPOCH FROM end_time AT TIME ZONE 'UTC') - 
     EXTRACT(EPOCH FROM start_time AT TIME ZONE 'UTC')) / 60 AS duration_minutes
FROM international_meetings;

五、最佳实践总结

1. 存储策略：

   • 纯本地业务用timestamp
   • 跨时区业务用timestamptz

2. 查询技巧：

   -- 按本地时区查询
   SELECT * FROM events 
   WHERE event_time AT TIME ZONE SESSIONTIMEZONE > SYSDATE - INTERVAL '1' DAY;
   
   -- 按特定时区查询
   SELECT * FROM events 
   WHERE event_time AT TIME ZONE 'Europe/Paris' > TO_TIMESTAMP_TZ('2023-06-01 00:00:00 Europe/Paris', 'YYYY-MM-DD HH24:MI:SS TZR');
   

3. 迁移建议：
   从其他数据库迁移时，注意时区默认值：

   -- MySQL的timestamp实际会转UTC存储，类似timestamptz
   -- 迁移到OceanBase时需要显式指定类型
   

记住：时间数据就像玻璃杯，处理不好容易碎（出错）。选对类型、明确时区、统一处理，才能让时间数据成为可靠的朋友。