时间戳与时区

概念定义

时间点是绝对的，挂钟读数是依赖地点的。数据库两种形态都有，选错就把数据与服务端或会话配置缠在一起。时刻型把输入按会话区归一化为 UTC 存储、按会话区渲染；朴素型原样存字段、不做任何解释。

工作机制

PostgreSQL 的 timestamptz 用会话时区把输入转成 UTC 存、输出时再转回；timestamp 原样保留字段。MySQL 的 TIMESTAMP 列按会话 time_zone 转换，DATETIME 原样保存。ORM 默认与驱动会话设置是隐藏变量——同一个字面量含义随它而变。

使用场景

事件时间、审计行、一切机器生成的时间戳：UTC 时刻、无时区逻辑、按用户渲染。人工输入的本地时间——九点站会——需要时区或夏令时规则列，墙钟才能在区法变化后幸存。

常见误区

timestamptz 不存储时区，只存归一化到 UTC 的时刻；会话时区影响的是渲染不是存储。时长必须用时刻差算并存成 interval，绝不用渲染后的本地字符串相减。朴素列的服务器时区迁移等于一次性平移全部数据——要排数据转换而不是改配置。跨夏令时、非整偏移时区的测试不可省略。

代码示例

const DIVISIONS = [
  { unit: "year", seconds: 365 * 24 * 60 * 60 },
  { unit: "month", seconds: 30 * 24 * 60 * 60 },
  { unit: "day", seconds: 24 * 60 * 60 },
  { unit: "hour", seconds: 60 * 60 },
  { unit: "minute", seconds: 60 },
  { unit: "second", seconds: 1 },
];

const formatters = new Map();

function getFormatter(locale) {
  let formatter = formatters.get(locale);
  if (formatter === undefined) {
    formatter = new Intl.RelativeTimeFormat(locale, { numeric: "auto" });
    formatters.set(locale, formatter);
  }
  return formatter;
}

export function formatRelativeTime(target, options = {}) {
  const { now = Date.now(), locale = "en" } = options;
  const targetTime =
    target instanceof Date ? target.getTime() : typeof target === "string" ? Date.parse(target) : target;
  if (!Number.isFinite(targetTime)) throw new TypeError("target must be a valid Date, ISO string or timestamp");
  if (!Number.isFinite(now)) throw new TypeError("now must be a finite timestamp");
  const deltaSeconds = Math.round((targetTime - now) / 1000);
  const magnitude = Math.abs(deltaSeconds);
  const division =
    DIVISIONS.find((item) => magnitude >= item.seconds) ?? DIVISIONS[DIVISIONS.length - 1];
  const value = Math.round(deltaSeconds / division.seconds);
  return getFormatter(locale).format(value, division.unit);
}

const now = Date.parse("2026-10-21T12:00:00Z");
console.log(formatRelativeTime("2026-10-21T10:00:00Z", { now, locale: "en" }));
console.log(formatRelativeTime(now + 90_000, { now, locale: "de" }));
console.log(formatRelativeTime(now - 45 * 24 * 60 * 60 * 1000, { now, locale: "zh-CN" }));

-- 每个邮箱保留 created_at 最新的一行，标记其余为重复
WITH ranked AS (
  SELECT
    id,
    email,
    created_at,
    ROW_NUMBER() OVER (
      PARTITION BY email
      ORDER BY created_at DESC, id DESC
    ) AS rn
  FROM subscribers
)
SELECT id, email, created_at, (rn = 1) AS is_primary
FROM ranked
ORDER BY email, rn;

-- 一次性清扫：删除全部非首行
WITH ranked AS (
  SELECT id,
         ROW_NUMBER() OVER (
           PARTITION BY email
           ORDER BY created_at DESC, id DESC
         ) AS rn
  FROM subscribers
)
DELETE FROM subscribers s
USING ranked r
WHERE s.id = r.id
  AND r.rn > 1;

-- 上游堵漏：唯一索引阻止重复再次写入
CREATE UNIQUE INDEX CONCURRENTLY idx_subscribers_email
  ON subscribers (email);