时序数据库Tdengine时区设计方案
🎯 核心问题
问题1: 什么是"今天"？
场景：用户查询"设备今天的数据"
┌─────────────────────────────────────────────────────────┐
│ 用户在纽约 (UTC-5) │
│ ├─ "今天" = 2025-10-28 00:00 ~ 23:59 (纽约时间) │
│ └─ UTC = 2025-10-28 05:00 ~ 2025-10-29 04:59 │
└─────────────────────────────────────────────────────────┘
┌─────────────────────────────────────────────────────────┐
│ 设备在北京 (UTC+8) │
│ ├─ "今天" = 2025-10-28 00:00 ~ 23:59 (北京时间) │
│ └─ UTC = 2025-10-27 16:00 ~ 2025-10-28 15:59 │
└─────────────────────────────────────────────────────────┘
❓ 到底查询哪个"今天"？问题2: 时区不都是整小时吗？
❌ 常见误解：所有时区都是 UTC±整数小时
✅ 真实情况：
┌─────────────────┬─────────────┬──────────────┐
│ 国家/地区 │ 时区 │ UTC偏移 │
├─────────────────┼─────────────┼──────────────┤
│ 中国 │ Asia/Shanghai│ +8:00 │
│ 印度 🇮🇳 │ Asia/Kolkata │ +5:30 ⭐ │
│ 尼泊尔 🇳🇵 │ Asia/Kathmandu│ +5:45 ⭐ │
│ 伊朗 🇮🇷 │ Asia/Tehran │ +3:30 ⭐ │
│ 澳洲中部 🇦🇺 │ Australia/Adelaide│ +9:30 ⭐ │
│ 缅甸 🇲🇲 │ Asia/Yangon │ +6:30 ⭐ │
└─────────────────┴─────────────┴──────────────┘
💡 必须用分钟而不是小时存储偏移量！问题3: 夏令时怎么办？
美国纽约的时区偏移：
冬令时 (11月-3月)：
UTC-5:00 → offset = -300 分钟
夏令时 (3月-11月)：
UTC-4:00 → offset = -240 分钟
┌─────────────────────────────────────────────────┐
│ 2025年3月9日 凌晨2点，时钟拨快1小时 │
│ │
│ 01:59:59 → 03:00:00 │
│ ↓ │
│ 这一天只有23小时！ │
└─────────────────────────────────────────────────┘
⚠️ 偏移量不是固定的，需要动态更新！🎨 设计原则
原则图解
┌─────────────────┐
│ UTC时间戳 │
│ (唯一事实来源) │
└────────┬────────┘
│
┌────────▼────────┐
│ Single Source │
│ of Truth │
└────────┬────────┘
│
┌────────────────┼────────────────┐
│ │ │
┌───────▼──────┐ ┌──────▼──────┐ ┌─────▼──────┐
│ 存储层(MySQL)│ │时序层(TDengine)│ │ 展示层 │
│ │ │ │ │ │
│ • UTC时间 │ │ • UTC时间戳 │ │ • 本地时间 │
│ • 时区信息 │ │ • 派生字段 │ │ • 用户时区 │
│ │ │ - local_date │ │ │
└──────────────┘ │ - local_hour │ └───────────┘
│ - local_minute│
└─────────────────┘三层设计
┌─────────────────────────────────────────────────────────┐
│ 1️⃣ 存储层：UTC为王 │
│ ├─ 所有时间统一存储为UTC │
│ ├─ timezone: 时区标识 (Asia/Shanghai) │
│ └─ timezone_offset: 偏移分钟数 (480) │
└─────────────────────────────────────────────────────────┘
⬇️
┌─────────────────────────────────────────────────────────┐
│ 2️⃣ 计算层：预计算派生字段 │
│ ├─ local_date: 本地日期 │
│ ├─ local_hour: 本地小时 │
│ └─ local_minute: 本地分钟 │
│ │
│ 💡 空间换时间：查询速度提升 100-1000倍 │
└─────────────────────────────────────────────────────────┘
⬇️
┌─────────────────────────────────────────────────────────┐
│ 3️⃣ 展示层：灵活转换 │
│ ├─ 按设备时区显示 │
│ ├─ 按用户时区显示 │
│ └─ 按UTC显示 │
└─────────────────────────────────────────────────────────┘🗄️ 数据库架构
MySQL - 元数据存储
sql
┌─────────────────── devices ─────────────────────┐
│ │
│ device_id VARCHAR(64) ← 设备ID │
│ device_name VARCHAR(128) ← 设备名称 │
│ product_id VARCHAR(64) ← 产品ID │
│ │
│ ┌─────────────── 时区配置 ──────────────┐ │
│ │ timezone VARCHAR(64) │ │
│ │ "Asia/Shanghai" (IANA标准) │ │
│ │ │ │
│ │ timezone_offset INT │ │
│ │ 480 (+8:00 = 480分钟) │ │
│ │ 330 (+5:30 = 330分钟) 🇮🇳 │ │
│ │ 345 (+5:45 = 345分钟) 🇳🇵 │ │
│ │ │ │
│ │ dst_active BOOLEAN │ │
│ │ 是否处于夏令时 │ │
│ └────────────────────────────────────────┘ │
│ │
│ latitude DECIMAL(10,8) ← 纬度 │
│ longitude DECIMAL(11,8) ← 经度 │
│ status TINYINT ← 状态 │
│ last_online_time DATETIME ← 最后在线 │
│ created_at DATETIME ← 创建时间 │
│ updated_at DATETIME ← 更新时间 │
└──────────────────────────────────────────────────┘TDengine - 时序数据存储
sql
┌────────────────── device_data (超级表) ─────────────────┐
│ │
│ 📅 时间字段 │
│ ├─ ts TIMESTAMP ← UTC时间戳 (主字段) │
│ │ │
│ ├─ local_date DATE ← 派生：本地日期 │
│ ├─ local_hour TINYINT ← 派生：本地小时 │
│ └─ local_minute TINYINT ← 派生：本地分钟 │
│ │
│ 📊 业务数据 │
│ ├─ temperature FLOAT ← 温度 │
│ ├─ humidity FLOAT ← 湿度 │
│ ├─ pressure FLOAT ← 气压 │
│ └─ voltage FLOAT ← 电压 │
│ │
│ 🏷️ TAG字段 (不随时间变化) │
│ ├─ device_id NCHAR(64) ← 设备ID │
│ ├─ product_id NCHAR(64) ← 产品ID │
│ ├─ timezone NCHAR(64) ← 时区 │
│ └─ timezone_offset INT ← 偏移(分钟) │
└──────────────────────────────────────────────────────────┘表关系图
┌──────────────┐
│ products │
│ │
│ product_id │
└──────┬───────┘
│ 1
│
│ N
┌──────▼───────┐ ┌─────────────────┐
│ devices │ 1 N │ device_data │
│ ├─────────┤ (TDengine) │
│ device_id │ │ │
│ timezone │ │ ts (UTC) │
│ timezone_ │ │ local_date ⚡ │
│ offset │ │ local_hour ⚡ │
│ dst_active │ │ temperature │
└──────────────┘ └─────────────────┘
⚡ 派生字段：从 ts + timezone 计算得出⏰ 时区偏移详解
为什么用分钟而不是小时？
❌ 错误设计：用小时存储
timezone_offset_hours INT -- 只能是整数
问题：
• 中国 +8:00 ✅ 可以存储为 8
• 印度 +5:30 ❌ 无法存储（5.5小时）
• 尼泊尔 +5:45 ❌ 无法存储（5.75小时）✅ 正确设计：用分钟存储
timezone_offset INT -- 可以是任意整数
示例：
• 中国 +8:00 → 480 分钟 (8 × 60)
• 印度 +5:30 → 330 分钟 (5 × 60 + 30)
• 尼泊尔 +5:45 → 345 分钟 (5 × 60 + 45)
• 伊朗 +3:30 → 210 分钟 (3 × 60 + 30)
• 纽约 -5:00 → -300 分钟 (冬令时)
• 纽约 -4:00 → -240 分钟 (夏令时)时区偏移计算示例
案例：印度设备 🇮🇳
┌──────────────────────────────────────────────────┐
│ 时区：Asia/Kolkata │
│ 偏移：UTC+5:30 = 330分钟 │
└──────────────────────────────────────────────────┘
UTC时间：2025-10-28 10:00:00
↓
+ 5小时30分 (330分钟)
↓
本地时间：2025-10-28 15:30:00
┌─────────┬──────────────┬──────────────┐
│ 字段 │ 存储值 │ 说明 │
├─────────┼──────────────┼──────────────┤
│ ts │ 1730113200000│ UTC时间戳 │
│ │ (毫秒) │ │
├─────────┼──────────────┼──────────────┤
│ local_ │ 2025-10-28 │ 派生：本地日期│
│ date │ │ │
├─────────┼──────────────┼──────────────┤
│ local_ │ 15 │ 派生：本地小时│
│ hour │ │ │
├─────────┼──────────────┼──────────────┤
│ local_ │ 30 │ 派生：本地分钟│
│ minute │ │ │
└─────────┴──────────────┴──────────────┘全球时区偏移一览
🌍 全球时区偏移分布
-12h 0 (UTC) +12h +14h
│━━━━━━━━━━━━━━━│━━━━━━━━━━━━━━━━━━━│━━━━━│
│ │ │ │
▼ ▼ ▼ ▼
🇺🇸 纽约 🇬🇧 伦敦 🇨🇳 北京 🇳🇿 新西兰
UTC-5/-4 UTC+0 UTC+8 UTC+12
🇮🇳 印度 🇦🇺 阿德莱德
UTC+5:30 ⭐ UTC+9:30 ⭐
↑ ↑
非整小时偏移 (必须用分钟存储！)
特殊时区示例：
┌────────────┬──────────────┬────────────┐
│ 地区 │ 标准时区 │ 偏移(分钟) │
├────────────┼──────────────┼────────────┤
│ 印度 🇮🇳 │ UTC+5:30 │ +330 │
│ 尼泊尔 🇳🇵 │ UTC+5:45 │ +345 │
│ 伊朗 🇮🇷 │ UTC+3:30 │ +210 │
│ 阿富汗 🇦🇫 │ UTC+4:30 │ +270 │
│ 缅甸 🇲🇲 │ UTC+6:30 │ +390 │
│ 查塔姆群岛 🇳🇿│ UTC+12:45 │ +765 │
└────────────┴──────────────┴────────────┘🔄 数据流转
完整数据流程图
┌─────────────┐
│ 设备上报 │
│ (任意时区) │
└──────┬──────┘
│
│ {"timestamp": "2025-10-28T10:00:00+05:30",
│ "temperature": 25.5}
│
▼
┌──────────────────────────────────────────┐
│ Step 1: 解析时间戳 │
│ ├─ 识别时区：+05:30 (印度时间) │
│ └─ 转换为UTC: 2025-10-28T04:30:00Z │
└──────┬───────────────────────────────────┘
│
▼
┌──────────────────────────────────────────┐
│ Step 2: 查询设备时区配置 │
│ ├─ MySQL: SELECT timezone, timezone_ │
│ │ offset FROM devices │
│ │ WHERE device_id = 'xxx' │
│ └─ 结果: Asia/Kolkata, offset=330 │
└──────┬───────────────────────────────────┘
│
▼
┌──────────────────────────────────────────┐
│ Step 3: 计算派生字段 │
│ │
│ UTC: 2025-10-28 04:30:00 │
│ + 5小时30分 (330分钟) │
│ = 2025-10-28 10:00:00 (本地) │
│ │
│ 派生： │
│ ├─ local_date = 2025-10-28 │
│ ├─ local_hour = 10 │
│ └─ local_minute = 0 │
└──────┬───────────────────────────────────┘
│
▼
┌──────────────────────────────────────────┐
│ Step 4: 写入TDengine │
│ │
│ INSERT INTO device_data VALUES ( │
│ ts: 1730091000000, │
│ local_date: '2025-10-28', │
│ local_hour: 10, │
│ local_minute: 0, │
│ temperature: 25.5 │
│ ) │
└──────┬───────────────────────────────────┘
│
▼
┌──────────────────────────────────────────┐
│ Step 5: 查询展示 │
│ ├─ 设备时区：显示 2025-10-28 10:00 │
│ ├─ 用户时区：根据用户位置转换 │
│ └─ UTC：显示 2025-10-28 04:30 │
└──────────────────────────────────────────┘写入详细流程
java
// 写入时的计算过程
┌────────────────────────────────────────────────┐
│ 1. 接收设备数据 │
│ { │
│ "device_id": "device_india_001", │
│ "timestamp": "2025-10-28T10:00:00+05:30", │
│ "temperature": 25.5 │
│ } │
└────────────────────────────────────────────────┘
⬇️
┌────────────────────────────────────────────────┐
│ 2. 解析并规范化为UTC │
│ Instant utc = Instant.parse(timestamp); │
│ // 结果: 2025-10-28T04:30:00Z │
└────────────────────────────────────────────────┘
⬇️
┌────────────────────────────────────────────────┐
│ 3. 查询设备时区配置 │
│ Device device = getDevice(device_id); │
│ // timezone: "Asia/Kolkata" │
│ // timezone_offset: 330 │
└────────────────────────────────────────────────┘
⬇️
┌────────────────────────────────────────────────┐
│ 4. 计算本地时间字段 │
│ ZonedDateTime local = utc.atZone( │
│ ZoneId.of("Asia/Kolkata") │
│ ); │
│ // 结果: 2025-10-28T10:00:00+05:30 │
│ │
│ local_date = local.toLocalDate() │
│ = 2025-10-28 │
│ local_hour = local.getHour() │
│ = 10 │
│ local_minute = local.getMinute() │
│ = 0 │
└────────────────────────────────────────────────┘
⬇️
┌────────────────────────────────────────────────┐
│ 5. 写入TDengine（包含所有字段） │
│ { │
│ ts: 1730091000000, ← UTC │
│ local_date: "2025-10-28", ← 派生 │
│ local_hour: 10, ← 派生 │
│ local_minute: 0, ← 派生 │
│ temperature: 25.5, ← 业务 │
│ timezone: "Asia/Kolkata", ← TAG │
│ timezone_offset: 330 ← TAG │
│ } │
└────────────────────────────────────────────────┘🔍 查询场景
场景1: 按设备本地日期查询
sql
-- 需求：查询印度设备在2025-10-28的数据
┌─────────────────────────────────────────────────┐
│ ❌ 方法1：实时计算（慢，不推荐） │
│ │
│ SELECT │
│ ts, │
│ DATE(ADDTIME(ts, │
│ SEC_TO_TIME(330*60))) │
│ as local_date, │
│ temperature │
│ FROM device_data │
│ WHERE device_id = 'device_india_001' │
│ AND DATE(ADDTIME(ts, │
│ SEC_TO_TIME(330*60))) │
│ = '2025-10-28' │
│ │
│ 问题： │
│ • 每行都要计算时间转换 │
│ • 无法使用索引 │
│ • 查询时间：5-30秒 (百万级数据) │
└─────────────────────────────────────────────────┘
┌─────────────────────────────────────────────────┐
│ ✅ 方法2：使用预存字段（快，推荐） │
│ │
│ SELECT │
│ ts, │
│ local_date, │
│ local_hour, │
│ local_minute, │
│ temperature │
│ FROM device_data │
│ WHERE device_id = 'device_india_001' │
│ AND local_date = '2025-10-28' ← 直接过滤 │
│ │
│ 优势： │
│ • 直接使用索引 │
│ • 不需要计算 │
│ • 查询时间：50-200毫秒 │
│ • 速度提升：100-1000倍！ │
└─────────────────────────────────────────────────┘场景2: 按本地时间统计
sql
-- 需求：统计设备每天的平均温度
┌────────────────────────────────────────────────┐
│ 查询：按设备本地日期分组统计 │
│ │
│ SELECT │
│ local_date, │
│ AVG(temperature) as avg_temp, │
│ MAX(temperature) as max_temp, │
│ MIN(temperature) as min_temp, │
│ COUNT(*) as data_count │
│ FROM device_data │
│ WHERE device_id = 'device_india_001' │
│ AND local_date >= '2025-10-01' │
│ AND local_date <= '2025-10-31' │
│ GROUP BY local_date │
│ ORDER BY local_date │
│ │
│ 执行计划： │
│ 1. 使用 device_id 索引定位数据 │
│ 2. 使用 local_date 索引过滤范围 │
│ 3. 直接分组聚合（无需计算） │
│ │
│ 性能：毫秒级返回 ⚡ │
└────────────────────────────────────────────────┘场景3: 跨时区设备对比
需求：对比中国、印度、尼泊尔设备在各自"本地2025-10-28"的数据
┌──────────────┬──────────────┬─────────────────────┐
│ 设备位置 │ 时区 │ 本地10-28对应UTC │
├──────────────┼──────────────┼─────────────────────┤
│ 中国 🇨🇳 │ UTC+8:00 │ 10-27 16:00 ~ │
│ │ offset=480 │ 10-28 15:59 │
├──────────────┼──────────────┼─────────────────────┤
│ 印度 🇮🇳 │ UTC+5:30 ⭐ │ 10-27 18:30 ~ │
│ │ offset=330 │ 10-28 18:29 │
├──────────────┼──────────────┼─────────────────────┤
│ 尼泊尔 🇳🇵 │ UTC+5:45 ⭐ │ 10-27 18:15 ~ │
│ │ offset=345 │ 10-28 18:14 │
└──────────────┴──────────────┴─────────────────────┘
SQL查询：
┌────────────────────────────────────────────────┐
│ -- 每个设备用自己的local_date查询 │
│ │
│ SELECT │
│ device_id, │
│ timezone, │
│ AVG(temperature) as avg_temp, │
│ COUNT(*) as data_count │
│ FROM device_data │
│ WHERE device_id IN ( │
│ 'device_china', │
│ 'device_india', │
│ 'device_nepal' │
│ ) │
│ AND local_date = '2025-10-28' ← 统一条件 │
│ GROUP BY device_id, timezone │
│ │
│ 💡 虽然UTC时间范围不同，但local_date统一！ │
└────────────────────────────────────────────────┘
结果示例：
┌─────────────┬───────────┬──────────┬────────────┐
│ device_id │ timezone │ avg_temp │ data_count │
├─────────────┼───────────┼──────────┼────────────┤
│ device_china│ UTC+8:00 │ 22.5°C │ 1440 │
│ device_india│ UTC+5:30⭐│ 28.3°C │ 1440 │
│ device_nepal│ UTC+5:45⭐│ 18.7°C │ 1440 │
└─────────────┴───────────┴──────────┴────────────┘场景4: 24小时模式分析
sql
-- 需求：分析设备在"本地每个小时"的温度模式
SELECT
local_hour,
AVG(temperature) as avg_temp,
STDDEV(temperature) as std_temp,
COUNT(*) as sample_count
FROM device_data
WHERE device_id = 'device_india_001'
AND ts >= NOW - 30d -- 最近30天
GROUP BY local_hour
ORDER BY local_hour根据用户本地时区查询
核心原则
┌────────────────────────────────────────────────┐
│ 按用户时区查询的3个步骤 │
│ │
│ 1️⃣ 用户输入 → UTC时间范围 │
│ ├─ 应用层计算 │
│ ├─ 考虑用户时区偏移 │
│ └─ 考虑夏令时状态 │
│ │
│ 2️⃣ UTC范围 → 查询数据库 │
│ ├─ WHERE ts >= start AND ts < end │
│ ├─ 充分利用UTC索引 │
│ └─ 高性能查询 │
│ │
│ 3️⃣ 结果 → 用户时区展示 │
│ ├─ SELECT (ts + offset) as user_time │
│ ├─ 一次性计算 │
│ └─ 返回给前端 │
└────────────────────────────────────────────────┘SQL模板
sql
-- 模板1: 按用户本地日期查询
SELECT
ts,
CAST((ts + :user_offset * 60000) / 86400000 AS DATE) as user_date,
CAST(((ts + :user_offset * 60000) % 86400000) / 3600000 AS INT) as user_hour,
temperature
FROM device_data
WHERE device_id = :device_id
AND ts >= :start_utc_ms
AND ts < :end_utc_ms
ORDER BY ts;
-- 模板2: 按用户本地日期分组统计
SELECT
CAST((ts + :user_offset * 60000) / 86400000 AS DATE) as user_date,
AVG(temperature) as avg_temp,
COUNT(*) as count
FROM device_data
WHERE device_id IN (:device_ids)
AND ts >= :start_utc_ms
AND ts < :end_utc_ms
GROUP BY user_date
ORDER BY user_date;
-- 模板3: 按用户本地小时统计
SELECT
CAST(((ts + :user_offset * 60000) % 86400000) / 3600000 AS INT) as user_hour,
AVG(temperature) as avg_temp
FROM device_data
WHERE device_id = :device_id
AND ts >= :start_utc_ms
AND ts < :end_utc_ms
GROUP BY user_hour
ORDER BY user_hour;关键要点
✅ DO's
• 应用层计算UTC时间范围
• SQL用UTC时间戳查询（高效）
• SELECT中计算用户本地时间📊 性能对比
查询性能对比
测试场景：查询设备某天的数据
数据量：1000万条记录
┌────────────────────┬──────────┬────────────┬──────────┐
│ 方案 │ 查询时间 │ CPU占用 │ 索引 │
├────────────────────┼──────────┼────────────┼──────────┤
│ 方案1：实时计算 │ 5-30秒 │ 90-95% │ ❌ 无效 │
│ (每次转换时区) │ │ │ │
├────────────────────┼──────────┼────────────┼──────────┤
│ 方案2：预存字段 │ 50-200ms │ 10-20% │ ✅ 有效 │
│ (local_date) │ │ │ │
├────────────────────┼──────────┼────────────┼──────────┤
│ 性能提升 │ 100-600倍│ 5-10倍 │ --- │
└────────────────────┴──────────┴────────────┴──────────┘
┌──────────────────────────────────────────────┐
│ 详细对比 │
│ │
│ ❌ 方案1：实时计算 │
│ SELECT ... WHERE │
│ DATE(ADDTIME(ts, SEC_TO_TIME(offset*60)))│
│ = '2025-10-28' │
│ │
│ 执行计划： │
│ 1. 全表扫描 │
│ 2. 每行计算时间转换 │
│ 3. 过滤结果 │
│ │
│ 问题： │
│ • 函数破坏了索引 │
│ • CPU密集型计算 │
│ • 无法并行优化 │
│ │
│ ✅ 方案2：预存字段 │
│ SELECT ... WHERE │
│ local_date = '2025-10-28' │
│ │
│ 执行计划： │
│ 1. 使用索引直接定位 │
│ 2. 范围扫描 │
│ 3. 返回结果 │
│ │
│ 优势： │
│ • B树索引高效查找 │
│ • IO密集型（速度快） │
│ • 支持并行扫描 │
└──────────────────────────────────────────────┘存储成本对比
┌──────────────────────────────────────────────┐
│ 数据大小分析 (每条记录) │
│ │
│ 基础字段： │
│ ├─ ts (TIMESTAMP) : 8 字节 │
│ ├─ temperature (FLOAT) : 4 字节 │
│ ├─ humidity (FLOAT) : 4 字节 │
│ └─ 其他业务字段 : ~16 字节 │
│ 小计: 32 字节 │
│ │
│ 派生字段（新增）： │
│ ├─ local_date (DATE) : 4 字节 │
│ ├─ local_hour (TINYINT) : 1 字节 │
│ └─ local_minute (TINYINT): 1 字节 │
│ 小计: 6 字节 │
│ │
│ 存储增加：6 / 32 = 18.75% │
│ │
└──────────────────────────────────────────────┘✅ 最佳实践
设计检查清单
✅ 数据存储
├─ [✓] UTC时间戳作为唯一真相
├─ [✓] 使用分钟存储时区偏移
├─ [✓] 存储IANA时区标识符
├─ [✓] 预计算local_date, local_hour, local_minute
└─ [✓] 记录DST状态
✅ 数据写入
├─ [✓] 写入时计算所有派生字段
├─ [✓] 验证时区配置正确性
├─ [✓] 使用批量写入提升性能
└─ [✓] 记录详细日志便于排查
✅ 数据查询
├─ [✓] 优先使用预存的local字段
├─ [✓] 建立合适的复合索引
├─ [✓] 避免在WHERE中使用函数
└─ [✓] 支持多种时区模式
✅ 运维维护
├─ [✓] 定时更新设备时区偏移
├─ [✓] 监控夏令时切换
├─ [✓] 验证派生字段一致性
└─ [✓] 提供时区配置管理界面关键代码模板
java
// ✅ 正确的写入模式
public void saveDeviceData(String deviceId, DeviceDataDTO dto) {
// 1. 获取设备时区配置
Device device = getDevice(deviceId);
// 2. 规范化为UTC
Instant utc = parseToUtc(dto.getTimestamp());
// 3. 计算派生字段
ZonedDateTime local = utc.atZone(
ZoneId.of(device.getTimezone())
);
// 4. 一次性写入所有字段
DataPoint point = DataPoint.builder()
.ts(utc) // UTC主字段
.localDate(local.toLocalDate()) // 派生字段
.localHour(local.getHour()) // 派生字段
.localMinute(local.getMinute()) // 派生字段
.temperature(dto.getTemperature())// 业务字段
.build();
save(point);
}
// ✅ 正确的查询模式
public List<DataPoint> queryByLocalDate(
String deviceId,
LocalDate date) {
// 直接使用预存字段，不要计算！
String sql =
"SELECT * FROM device_data " +
"WHERE device_id = ? " +
" AND local_date = ? " + // ← 关键：预存字段
"ORDER BY ts";
return query(sql, deviceId, date);
}常见错误及解决方案
❌ 错误1：用小时存储偏移
timezone_offset_hours INT
问题：无法表示 +5:30, +5:45
✅ 解决：用分钟
timezone_offset INT -- 330, 345, etc.
---
❌ 错误2：只存UTC不存派生字段
CREATE TABLE (ts TIMESTAMP, ...)
问题：查询时实时计算，性能差
✅ 解决：预存local字段
CREATE TABLE (
ts TIMESTAMP,
local_date DATE,
local_hour TINYINT,
...
)
---
❌ 错误3：WHERE中使用函数计算
WHERE DATE(ts + offset) = '2025-10-28'
问题：无法使用索引
✅ 解决：用预存字段
WHERE local_date = '2025-10-28'
---
❌ 错误4：硬编码偏移量
int offset = 480; -- 固定+8小时
问题：无法处理夏令时
✅ 解决：动态获取
int offset = getCurrentOffset(timezone);
---
❌ 错误5：忘记处理夏令时切换
设备时区偏移never更新
问题：夏令时期间数据错误
✅ 解决：定时任务
@Scheduled(cron = "0 0 * * * ?")
public void updateTimezoneOffsets() {
// 检查并更新所有设备偏移
}性能优化建议
┌────────────────────────────────────────────────┐
│ 1️⃣ 索引优化 │
│ │
│ -- 设备+本地日期复合索引 │
│ CREATE INDEX idx_device_local_date │
│ ON device_data(device_id, local_date); │
│ │
│ -- 设备+本地小时复合索引 │
│ CREATE INDEX idx_device_local_hour │
│ ON device_data(device_id, local_date, │
│ local_hour); │
└────────────────────────────────────────────────┘
┌────────────────────────────────────────────────┐
│ 2️⃣ 分层查询 │
│ │
│ • 7天内：查原始表 (按小时) │
│ • 7-90天：查每日汇总表 │
│ • 90天以上：查每月汇总表 │
│ │
│ 优势：根据时间跨度自动选择最优粒度 │
└────────────────────────────────────────────────┘
┌────────────────────────────────────────────────┐
│ 3️⃣ 物化视图/流式计算 │
│ │
│ -- TDengine创建自动聚合流 │
│ CREATE STREAM device_data_daily AS │
│ SELECT │
│ _wstart as day, │
│ device_id, │
│ AVG(temperature) as avg_temp │
│ FROM device_data │
│ INTERVAL(1d, timezone_offset * 60); │
│ │
│ 优势：实时汇总，查询更快 │
└────────────────────────────────────────────────┘
┌────────────────────────────────────────────────┐
│ 4️⃣ 批量操作 │
│ │
│ • 批量写入：每次1000-5000条 │
│ • 批量更新：使用事务 │
│ • 避免逐条操作 │
│ │
│ 性能提升：10-100倍 │
└────────────────────────────────────────────────┘📝 总结
核心设计理念
┌─────────────┐
│ UTC is │
│ the Truth │
└──────┬──────┘
│
┌────────────────┼────────────────┐
│ │ │
┌────▼────┐ ┌──────▼──────┐ ┌────▼────┐
│ 唯一真相 │ │ 派生字段 │ │ 灵活展示 │
│ │ │ │ │ │
│ • UTC │ │ • local_date│ │ • 设备 │
│ 时间戳 │───▶│ • local_hour│──▶│ 时区 │
│ │ │ • local_ │ │ • 用户 │
│ • IANA │ │ minute │ │ 时区 │
│ 时区 │ │ │ │ • UTC │
│ │ │ 空间换时间 │ │ │
│ • 偏移 │ │ 100倍加速 │ │ 按需转换 │
│ (分钟) │ │ │ │ │
└─────────┘ └─────────────┘ └─────────┘三个关键原则
1️⃣ UTC为王
├─ 所有时间统一存储为UTC
├─ UTC是唯一的时间真相
└─ 派生字段可以重新计算
2️⃣ 空间换时间
├─ 预计算local_date, local_hour, local_minute
├─ 增加<20%存储，提升100-1000倍查询速度
└─ 投资回报率极高
3️⃣ 分钟为单位
├─ timezone_offset用分钟存储
├─ 支持+5:30, +5:45等非整小时时区
└─ 覆盖全球所有时区