多市场行情时间戳对齐：UTC 存储的夏令时陷阱与数据库设计方案

Jutick
5 阅读8分钟

一句话痛点：跨市场回测时，写死的 UTC-5 会让你在夏令时切换日拿到错位一小时的行情，年化收益高估 5-8%。

本文核心方案：用 UTC 毫秒做主排序键 + exchange_local_time 做回放标签 + IANA 时区数据库动态计算夏令时偏移，永久消除季节性的时间对齐 bug。

目录

核心矛盾：四个市场，四种时间规则

市场交易所时区夏令时数据源常见格式对齐风险
A 股北京时间 (UTC+8)无Unix 秒（北京时间）易与 UTC 秒混淆
港股香港时间 (UTC+8)无UTC 字符串或本地时间不统一
美股美东时间有（3月/11月切换）美东时间字符串偏移量每年变两次
伦敦格林尼治/英国夏令时有（3月/10月切换）本地时间或 UTC规则与美东不同

典型翻车现场：北京时间周二上午 9:25 回测美股策略，3 月 11 日那根 K 线出现 1.7% 异常跳空——不是策略错，是回测引擎还在用冬令时偏移 UTC-5，而当天已是夏令时 UTC-4。开盘后第一个小时的高波动行情错位覆盖，策略连开 4 笔空单。

双字段存储方案（架构决策）

核心思想：每条行情记录同时存两个时间字段，各司其职。

字段类型用途示例
event_time_utcBIGINT（毫秒）所有排序/过滤的主键，与时区无关1710120600000
exchange_local_timeVARCHAR(25)（ISO 8601）回放时的业务判断（如集合竞价、开盘时段）2024-03-11T09:30:00+08:00

为什么不用本地时间做主键？

  • 排序错乱（北京时间比美东早 12-13 小时）
  • 夏令时切换日出现“不存在的小时”（如 2024-03-10 02:00-02:59 在美东不存在）
  • 数据写入时可能被拒绝或排错位置

为什么必须保留 exchange_local_time？

  • 回放时需要回答“这笔成交在交易所当地是几点几分”
  • 不能依赖 UTC 临时计算——万一未来夏令时规则变化，历史数据的偏移量会被错误重算

类比：就像数据库读写分离，写的时候统一为 UTC（主库），读的时候各自按需转换（从库），中间的转换层在入库时一次性完成，回放零开销。

夏令时：绝不硬编码偏移量

死因统计：硬编码 UTC-4 / UTC-5 的代码，每年至少 2 次手工改配置，一次遗漏则跨市场策略年化偏差可达 15%。

正确做法：使用 IANA 时区数据库（Python zoneinfo / pytz），给定交易所标识符（如 America/New_York），自动判断当前是否处于夏令时并返回正确的 UTC 偏移量。一行硬编码都不留。

代码落地：三步搭建自动对齐管道

完整可运行，依赖 requests、sqlite3、Python 3.9+ 标准库 zoneinfo。每步标注关键点，注释解释“为什么”。

Step 1：拉取跨市场行情，双字段时间入库

import os, time, sqlite3, requests
from datetime import datetime, timezone
from zoneinfo import ZoneInfo
from typing import List

API_KEY = os.getenv("TICKDB_API_KEY")
BASE_URL = "https://api.tickdb.ai/v1"
HEADERS = {"X-API-Key": API_KEY}

# ⚠️ 交易所 → IANA 时区标识符（动态计算，绝不硬编码偏移量）
EXCHANGE_TIMEZONE = {
    "SSE": "Asia/Shanghai",
    "SZSE": "Asia/Shanghai",
    "SEHK": "Asia/Hong_Kong",
    "NYSE": "America/New_York",
    "NASDAQ": "America/New_York",
}

def init_db():
    """双字段时间表：event_time_utc (毫秒) + exchange_local_time (ISO 8601)"""
    conn = sqlite3.connect("tickdb_timestamps.db")
    conn.execute("""
        CREATE TABLE IF NOT EXISTS ticker_snapshots (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            symbol TEXT NOT NULL,
            exchange TEXT NOT NULL,
            event_time_utc INTEGER NOT NULL,       -- ★ 主排序键
            exchange_local_time TEXT NOT NULL,      -- ★ 回放标签
            last_price REAL,
            volume_24h REAL,
            fetched_at_utc INTEGER NOT NULL         -- 批次去重
        )
    """)
    conn.execute("CREATE UNIQUE INDEX IF NOT EXISTS idx_symbol_fetched "
                 "ON ticker_snapshots(symbol, fetched_at_utc)")
    conn.commit()
    return conn

def fetch_multi_market_tickers(symbols: List[str]):
    """
    拉取跨市场 ticker 快照，写入双字段时间。
    ticker 返回 timestamp (UTC 秒) → 转换为毫秒存入 event_time_utc。
    exchange_local_time 由 IANA 时区一次性计算。
    """
    url = f"{BASE_URL}/market/ticker"
    backoff = 1
    conn = init_db()
    fetched_at = int(time.time() * 1000)

    try:
        params = {"symbols": ",".join(symbols)}   # ticker 用 symbols 复数
        resp = requests.get(url, headers=HEADERS, params=params, timeout=10)
        data = resp.json()

        if data["code"] == 3001:                   # 限流
            retry_after = resp.headers.get("Retry-After")
            wait = int(retry_after) if retry_after else backoff
            time.sleep(wait)
            return
        if data["code"] == 1001:                   # 权限/参数错误
            raise RuntimeError(f"API Error 1001: {data.get('message')}")
        if data["code"] != 0:
            raise RuntimeError(f"Unexpected error {data['code']}")

        rows = []
        for item in data.get("data", []):
            sym = item["symbol"]
            exchange = item.get("exchange", "")
            ts_sec = item.get("timestamp")         # ticker 使用 timestamp (秒)
            if ts_sec is None: continue
            event_time_utc = int(ts_sec * 1000)

            tz_id = EXCHANGE_TIMEZONE.get(exchange)
            if tz_id:
                tz = ZoneInfo(tz_id)
                dt_local = datetime.fromtimestamp(ts_sec, tz=tz)
                exchange_local_time = dt_local.isoformat()
            else:
                exchange_local_time = datetime.fromtimestamp(ts_sec, tz=timezone.utc).isoformat()

            rows.append((
                sym, exchange, event_time_utc, exchange_local_time,
                float(item.get("last_price", 0)) if item.get("last_price") else None,
                float(item.get("volume_24h", 0)) if item.get("volume_24h") else None,
                fetched_at
            ))

        conn.executemany("""INSERT OR IGNORE INTO ticker_snapshots
            (symbol, exchange, event_time_utc, exchange_local_time,
             last_price, volume_24h, fetched_at_utc)
            VALUES (?, ?, ?, ?, ?, ?, ?)""", rows)
        conn.commit()
        print(f"写入 {len(rows)} 条快照，batch_utc={fetched_at}")

    except requests.exceptions.Timeout:
        time.sleep(1)
    except Exception as e:
        print(f"拉取失败: {e}")
    finally:
        conn.close()

★ 关键点：event_time_utc 是毫秒级整数，所有跨市场排序都靠它；exchange_local_time 是 ISO 8601 字符串，只在回放时使用。ticker 端点的 timestamp 是秒级，需 ×1000 才能统一精度。

Step 2：夏令时偏移量动态计算（可独立使用）

from zoneinfo import ZoneInfo
from datetime import datetime, timezone

def get_utc_offset(exchange: str, dt: datetime = None) -> int:
    """返回 UTC 偏移小时数，如 NYSE 夏令时返回 -4，冬令时返回 -5"""
    tz_id = EXCHANGE_TIMEZONE.get(exchange)
    if not tz_id:
        raise ValueError(f"Unknown exchange: {exchange}")
    tz = ZoneInfo(tz_id)
    if dt is None:
        dt = datetime.now(tz=tz)
    elif dt.tzinfo is None:
        dt = dt.replace(tzinfo=timezone.utc)
    dt = dt.astimezone(tz)
    offset = dt.utcoffset()
    if offset is None:
        raise RuntimeError(f"Cannot determine UTC offset for {exchange} at {dt}")
    return int(offset.total_seconds() / 3600)

def is_dst_active(exchange: str, dt: datetime = None) -> bool:
    """判断当前是否处于夏令时（美东 3月第二个周日～11月第一个周日）"""
    tz_id = EXCHANGE_TIMEZONE.get(exchange)
    if not tz_id: return False
    tz = ZoneInfo(tz_id)
    if dt is None: dt = datetime.now(tz=tz)
    elif dt.tzinfo is None: dt = dt.replace(tzinfo=timezone.utc)
    dt = dt.astimezone(tz)
    dst_offset = dt.dst()
    return dst_offset is not None and dst_offset.total_seconds() > 0

★ 关键点：utcoffset() 和 dst() 完全依赖 IANA 数据库，无需手工维护夏令时规则。示例：get_utc_offset('NYSE', datetime(2024,3,11)) 返回 -4，而 3 月 9 日返回 -5。

Step 3：回放对齐与用户时区转换

重要区分：ticker 用 timestamp（秒），kline 用 time（毫秒），回放时必须分清。

端点时间字段单位嵌套路径
tickertimestamp秒 UTCdata 数组
klinetime毫秒 UTCdata.klines
def replay_cross_market(symbols: List[str], start_utc: int, end_utc: int) -> List[Dict]:
    """按 event_time_utc 排序回放，exchange_local_time 直接用于业务判断"""
    conn = sqlite3.connect("tickdb_timestamps.db")
    conn.row_factory = sqlite3.Row
    rows = conn.execute("""
        SELECT symbol, exchange, event_time_utc, exchange_local_time, last_price, volume_24h
        FROM ticker_snapshots
        WHERE event_time_utc >= ? AND event_time_utc <= ?
        ORDER BY event_time_utc ASC
    """, (start_utc, end_utc)).fetchall()
    conn.close()
    return [dict(r) for r in rows]

def convert_to_user_timezone(records: List[Dict], user_tz: str = "Asia/Shanghai") -> List[Dict]:
    """展示层按用户时区转换 event_time_utc，不修改 exchange_local_time"""
    tz = ZoneInfo(user_tz)
    for r in records:
        dt = datetime.fromtimestamp(r["event_time_utc"] / 1000, tz=tz)
        r["user_local_time"] = dt.isoformat()
    return records

★ 关键点：三层时间各司其职——UTC 排序，exchange_local_time 判断集合竞价/开盘时段，user_local_time 仅用于前端展示。

你真正在维护的，是一张手工夏令时日历

没有统一 API 时，美股给美东字符串，A 股给北京时间秒，港股格式不统一。每个数据源都要写一个时间 parser；美东、欧洲、澳洲、南美各有夏令时规则，全球 70 多个国家且持续变化。一旦某个国家修改规则，你的对齐逻辑链从头到尾重写。

TickDB 将时间戳格式收归到一个出口：REST + WebSocket 长连接覆盖美股、港股、A 股等全球 40,145 个品种，统一返回 UTC 时间戳，统一字段命名（ticker 用 timestamp / kline 用 time）。你不再需要维护那本手工夏令时日历。

接口文档：https://docs.tickdb.ai；如需自动化查询，可通过 MCP 端点 https://mcp.tickdb.ai 将行情封装为 Agent 可调用的工具。

反向钩子：你的代码里有多少处 UTC-4？

一个美股日内策略在 2024 年 3 月 11 日开盘连续止损，排查两天定位到第 147 行 OFFSET_NY = -5。改掉后曲线恢复——但第 312 行还有一个 -5，藏在伦敦开盘时间计算里。硬编码的偏移量全年累积可高估年化收益 5-8%。

如果美国永久夏令时法案明天生效，你代码里的 -5 和 -4 要改多少处？你上一次全局搜索这些数字，是什么时候？

📡 数据由 TickDB.ai 提供

avatar
文章
阅读
粉丝