PolarDB中的日期函数性能：NOW ()、CURRENT_TIMESTAMP与DATE_TRUNC对比

1. 当时间成为数字世界的标尺

在我们每天使用的订单系统、监控平台和日志分析工具中，时间戳就像数码世界的脉搏。作为PolarDB资深用户，我发现开发者在处理时间函数时常陷入选择的困惑：NOW()和CURRENT_TIMESTAMP真的完全相同吗？为什么有些团队严格禁止在索引字段使用DATE_TRUNC？今天我们就用实验数据来见证这三个时间函数的差异。

2. 技术特写

2.1 实时时钟组选手

NOW()：

-- 创建日志表（PolarDB MySQL兼容模式）
CREATE TABLE server_logs (
    log_id BIGINT AUTO_INCREMENT,
    event_time DATETIME(6),
    message TEXT,
    PRIMARY KEY(log_id)
) ENGINE=InnoDB;

-- 插入当前时间（精确到微秒）
INSERT INTO server_logs(event_time, message)
VALUES (NOW(6), '系统启动完成');  -- 参数6表示微秒精度

CURRENT_TIMESTAMP：

-- 构建金融交易表
CREATE TABLE financial_transactions (
    tx_id VARCHAR(36),
    amount DECIMAL(15,2),
    created_at TIMESTAMP(3) DEFAULT CURRENT_TIMESTAMP(3),
    PRIMARY KEY(tx_id)
);

-- 插入时自动记录时间戳
INSERT INTO financial_transactions(tx_id, amount)
VALUES ('TX20231128123456', 8888.88);  -- created_at自动填充微秒时间

2.2 时间整形师

DATE_TRUNC()：

-- 创建物联网传感器数据表
CREATE TABLE sensor_readings (
    device_id VARCHAR(20),
    reading_time TIMESTAMP,
    temperature FLOAT,
    INDEX (device_id, reading_time)
);

-- 按分钟聚合数据
SELECT 
    DATE_TRUNC('minute', reading_time) AS per_minute,
    AVG(temperature) AS avg_temp
FROM sensor_readings
WHERE device_id = 'TH-001'
GROUP BY DATE_TRUNC('minute', reading_time);

3. 性能赛道实测

3.1 百万级数据压力测试

-- 准备测试表（PolarDB PostgreSQL兼容模式）
CREATE TABLE perf_test (
    id SERIAL PRIMARY KEY,
    raw_ts TIMESTAMPTZ,
    trunc_hour TIMESTAMPTZ
);

-- 插入测试数据
INSERT INTO perf_test(raw_ts, trunc_hour)
SELECT 
    NOW() - (random() * 86400 || ' seconds')::interval,
    DATE_TRUNC('hour', NOW() - (random() * 86400 || ' seconds')::interval)
FROM generate_series(1,1000000);

-- 创建表达式索引
CREATE INDEX idx_trunc_hour ON perf_test (DATE_TRUNC('hour', raw_ts));

-- 测试查询1：原始字段查询
EXPLAIN ANALYZE
SELECT COUNT(*) 
FROM perf_test
WHERE raw_ts BETWEEN '2023-11-01' AND '2023-11-02';

-- 测试查询2：DATE_TRUNC查询
EXPLAIN ANALYZE
SELECT COUNT(*) 
FROM perf_test
WHERE DATE_TRUNC('hour', raw_ts) = '2023-11-01 12:00:00';

3.2 实验数据汇总

4. 技术选型指南

4.1 应用场景黄金定律

• 金融交易流水：优先选择CURRENT_TIMESTAMP默认值，搭配序列化时间戳确保事务顺序
• 物联网时序数据：入库时原始时间戳+预计算DATE_TRUNC存储双字段
• 实时监控看板：使用NOW()获取精确时间，配合B-tree索引快速定位

4.2 性能陷阱清单

1. 动态计算黑名单：避免在WHERE条件中使用DATE_TRUNC(field)
2. 时钟漂移防御：在高并发场景统一使用事务开始时间
3. 时区雷区：跨时区系统必须明确指定函数时区参数
4. 索引失效案例：

-- 错误示例：表达式索引的误用
CREATE INDEX idx_failure ON table1 (DATE_TRUNC('day', event_time));

-- 正确示例：配合存储过程预计算
CREATE MATERIALIZED VIEW daily_stats AS
SELECT DATE_TRUNC('day', event_time) AS stat_day,
       COUNT(*) AS total_events
FROM table1
GROUP BY 1;

5. 专家级优化秘籍

5.1 混合存储策略

-- 智能分区表示例
CREATE TABLE sales_records (
    region VARCHAR(10),
    sale_ts TIMESTAMPTZ,
    sale_day DATE GENERATED ALWAYS AS (sale_ts::DATE) STORED,
    amount NUMERIC(12,2)
) PARTITION BY RANGE (sale_day);

-- 自动创建分区
CREATE TABLE sales_202311 PARTITION OF sales_records
FOR VALUES FROM ('2023-11-01') TO ('2023-12-01');

5.2 异步预计算模式

-- 使用物化视图预聚合
CREATE MATERIALIZED VIEW hourly_metrics
AS
SELECT 
    device_id,
    DATE_TRUNC('hour', reading_time) AS metric_hour,
    AVG(value) AS avg_value
FROM iot_data
GROUP BY 1, 2
WITH DATA;

-- 定时刷新策略
REFRESH MATERIALIZED VIEW CONCURRENTLY hourly_metrics;

6. 应用场景深度剖析

在电商秒杀系统中，我们发现NOW()的时间精度决定了库存锁定的顺序：当两个请求在同一微秒到达时，使用CURRENT_TIMESTAMP可能导致时间相同，而NOW(6)的六位微秒精度可以更精准排序。但在用户可见的时间展示场景，过多的精度反而需要做DATE_TRUNC处理。

7. 技术优缺点矩阵

8. 关键注意事项

1. 时区敏感操作：在全球化系统中使用AT TIME ZONE显式转换
2. 冷热数据分离：对历史数据使用列式存储优化DATE_TRUNC查询
3. 参数预编译陷阱：避免在ORM框架中拼接时间表达式

9. 实践总结

通过这次深度探索，我们发现没有绝对的最优解。NOW()在需要固定事务时间的场景表现出色，CURRENT_TIMESTAMP适合实时系统，而DATE_TRUNC的优化需要结合预计算策略。在最近的PolarDB 3.0版本中，新增的生成列功能（Generated Columns）让这些时间函数的优化更加得心应手。