PHP小码农

Appearance

TO_CHAR / TO_DATE / TO_TIMESTAMP ​

格式化与解析是日期函数里参数最密集的一组：to_char 用模板字符串把日期时间与数字渲染成文本，to_date / to_timestamp 则反向把文本解析成时间值。它们的模板字段多达四十余个（YYYY / MM / DD / HH24 / MI / FF6 / TZ …），还带 FM / TH / FX 等修饰符，写错一个字母就会得到静默错误的字符串。更要注意 to_timestamp 是双态函数：传字符串时按模板解析，传数字时按 Unix 纪元秒解释。

前置知识

阅读本节前，建议先了解：INTERVAL 运算

基础概念 ​

概念术语说明
模板Template / Pattern描述输出或输入格式的字符串，如 'YYYY-MM-DD'
模板字段Template Pattern模板中的占位记号，如 YYYY、HH24、FF1
修饰符Modifier改变字段行为的后缀，如 FMYEAR、DDTH
格式化Formatting时间值 → 文本，由 to_char 完成
解析Parsing文本 → 时间值，由 to_date / to_timestamp 完成
填充模式Fill Mode默认补空格/前导零，FM 可去掉填充
严格模式FX要求输入与模板逐字符匹配，禁止跳过空白

三巨头的签名：

text
to_char(timestamp, text)        → text       格式化时间戳
to_char(interval, text)         → text       格式化间隔
to_char(numeric, text)          → text       格式化数字
to_char(double precision, text) → text       格式化浮点数

to_date(text, text)             → date       按模板解析日期
to_timestamp(text, text)        → timestamptz 按模板解析时间戳
to_timestamp(double precision)  → timestamptz 按 Unix 纪元秒解析

to_number(text, text)           → numeric    按模板解析数字

to_timestamp 的双态是高频踩坑点：to_timestamp('2026-09-24 10:00', 'YYYY-MM-DD HH24:MI') 走模板解析；to_timestamp(1758672000) 走纪元秒解释。看参数数量就能区分。

常用模板字段速查（完整表见官方 Table 9.27）：

字段含义示例输出
YYYY4 位公历年2026
YY2 位公历年26
IYYY4 位 ISO 年2026
MM月份（01-12）09
MON月份缩写Sep
MONTH月份全称（补空格到 9 字符）September
DD月内日（01-31）24
DDD年内日（001-366）267
D周内日（1-7，周日为 1）5
IDISO 周内日（1-7，周一为 1）4
DY星期缩写Thu
DAY星期全称（补空格到 9 字符）Thursday
W月内周（1-5）4
WW年内周（1-53）39
IWISO 年内周（1-53）39
Q季度（1-4）3
HH / HH1212 小时制（01-12）03
HH2424 小时制（00-23）15
MI分钟（00-59）47
SS秒（00-59）33
MS毫秒（000-999）123
US微秒（000000-999999）123456
FF1 ~ FF6小数秒，精度 1~6 位1 … 123456
SSSS当日已过秒数（0-86399）56853
AM / PM上下午标记PM
TZ时区缩写CST
TZH时区小时偏移+08
TZM时区分钟偏移00
OF时区偏移（ISO 风格）+08
J儒略日2461308
CC世纪（2 位）21
RM罗马数字月IX

模板字段大小写敏感：MM 是月份，mm 是分钟；HH24 中的 HH 必须大写。官方文档明确「模板字段区分大小写」，小写形式虽在部分场景被接受，但不应依赖。

语法与用法 ​

sql
-- 1. 格式化
to_char(timestamp, text)          → text
to_char(interval, text)           → text
to_char(numeric, text)            → text
to_char(double precision, text)   → text

-- 2. 解析
to_date(text, text)               → date
to_timestamp(text, text)          → timestamptz
to_timestamp(double precision)    → timestamptz   -- Unix 纪元秒
to_number(text, text)             → numeric

最小可运行示例：

sql
-- 常见格式化
SELECT to_char(TIMESTAMP '2026-09-24 15:47:33.123456', 'YYYY-MM-DD HH24:MI:SS');
-- 结果：2026-09-24 15:47:33

-- 带星期与中文习惯分隔
SELECT to_char(TIMESTAMP '2026-09-24 15:47:33', 'YYYY"年"MM"月"DD"日" HH24:MI:SS');
-- 结果：2026年09月24日 15:47:33

-- 小数秒精度控制
SELECT
  to_char(TIMESTAMP '2026-09-24 15:47:33.123456', 'SS.MS')  AS ms,
  to_char(TIMESTAMP '2026-09-24 15:47:33.123456', 'SS.US')  AS us,
  to_char(TIMESTAMP '2026-09-24 15:47:33.123456', 'SS.FF3') AS ff3;
-- 结果：33.123 | 33.123456 | 33.123

-- 解析文本为日期
SELECT to_date('2026-09-24', 'YYYY-MM-DD');
-- 结果：2026-09-24

-- 解析文本为带时区时间戳
SELECT to_timestamp('2026-09-24 15:47:33 +08', 'YYYY-MM-DD HH24:MI:SS TZH');
-- 结果：2026-09-24 15:47:33+08

-- 双参 vs 单参 to_timestamp
SELECT
  to_timestamp('2026-09-24 15:47:33', 'YYYY-MM-DD HH24:MI:SS') AS by_template,
  to_timestamp(1789007253)                                     AS by_epoch;
-- 结果：2026-09-24 15:47:33+08 | 2026-09-10 14:27:33+08

详细说明 ​

模板修饰符：FM / TH / FX / TM / SP ​

修饰符紧跟在模板字段前面，改变该字段的填充、序数词或大小写行为：

修饰符作用示例
FM抑制填充（去掉空格与前导零）to_char(DATE '2026-09-04', 'FMMM/DD') → 9/4
TH序数词后缀to_char(DATE '2026-09-24', 'DDTH') → 24TH
FX严格匹配（对解析函数生效）to_date('2026-09-24', 'FXYYYY-MM-DD')
TM用自己的语言输出月份/星期全称to_char(DATE '2026-09-24', 'TMDay')
SP输出拼写形式（英文）to_char(4, 'SP') → four
sql
SELECT
  to_char(DATE '2026-09-04', 'FMMM/DD')          AS fm_example,   -- 9/4
  to_char(DATE '2026-09-04', 'MM/DD')            AS plain,        -- 09/04
  to_char(DATE '2026-09-24', 'DDTH')             AS ordinal,      -- 24TH
  to_char(DATE '2026-09-24', 'DDSPTH')           AS spelled,      -- TWENTY-FOURTH
  to_char(DATE '2026-09-24', 'TMDay, TMMonth')   AS localized;    -- Thursday, September

FM 的关键行为：官方文档特别指出 FM 只影响紧跟其后的那一个模板字段，这与 Oracle 中 FM 影响后续所有字段的行为不同。因此 'FMMM/DD' 只去掉 MM 的前导零，DD 仍会输出 04：

sql
SELECT
  to_char(DATE '2026-09-04', 'FMMM/DD')   AS fm_mm_only,  -- 9/04
  to_char(DATE '2026-09-04', 'FMMM/FMDD') AS fm_both;     -- 9/4

FX 只在解析时有效，它要求输入文本与模板逐字符对齐，不允许跳过多余空白。官方文档给出的例子：

sql
-- 默认宽松：宽度不足的部分会被吸收
SELECT to_date('2000-1-1', 'YYYY-MM-DD');   -- 2000-01-01

-- 严格模式：报错
SELECT to_date('2000-1-1', 'FXYYYY-MM-DD');
text
ERROR:  invalid value "1" for "MM"
DETAIL:  Value must be an integer.

严格模式下 MM 要求恰好两位数字，输入里的 1 无法匹配，因此报错。处理外部数据源（CSV、日志、第三方接口）时，建议加 FX，让格式不符的数据立即报错，而不是被静默解析成一个看似合理的错误日期。

数字格式化与 to_number ​

to_char 同样支持数字模板（官方 Table 9.29），常用记号：

记号含义
9数字位，不足补空格；无数字时该位不输出
0数字位，不足补 0
.小数点（按 lc_numeric 决定输出字符）
,千分位分隔符
D小数点的本地化表示
G分组分隔符的本地化表示
S符号（锚定在数字上）
L货币符号
MI负号放在数字后
PR负数用尖括号包围
RN / rn罗马数字
sql
SELECT
  to_char(1234567.891, '999G999G999D99') AS grouped,        -- 1,234,567.89
  to_char(1234567.891, 'FM999,999,999.00') AS fm_full,      -- 1,234,567.89
  to_char(-1234.5, '9999.99MI')            AS trailing_minus, -- 1234.50-
  to_char(-1234.5, '9999.99PR')            AS paren_negative, -- <1234.50>
  to_char(2026, 'FM9999')                  AS year_str;      -- 2026

9 与 0 的差别是「空位补什么」：to_char(5, '000') → 005，to_char(5, '999') → 5（前面补空格）。转成文本后如果再拼接，前导空格常是隐性 bug 来源——需要无填充输出时用 FM。

to_number 是解析方向，模板必须与输入匹配：

sql
SELECT
  to_number('1,234,567.89', '999G999G999D99') AS n1,   -- 1234567.89
  to_number('005', '000')                     AS n2,   -- 5
  to_number('<1234.50>', '9999.99PR')         AS n3;   -- -1234.50

解析时的默认值与缺失字段 ​

to_date 只给日期部分，缺失的时间字段一律取默认值（0 点 0 分 0 秒）；to_timestamp 的日期字段若缺失，则取当前年月的默认规则。理解这一点能避免「解析出的日期差了几十年」的问题。

sql
SELECT
  to_date('2026-09', 'YYYY-MM')                        AS missing_day,   -- 2026-09-01
  to_date('2026', 'YYYY')                              AS missing_md,    -- 2026-01-01
  to_timestamp('15:47:33', 'HH24:MI:SS')               AS missing_date;  -- 当前日期 + 15:47:33
text
 missing_day | missing_md |      missing_date
-------------+------------+-------------------------
 2026-09-01  | 2026-01-01 | 2026-09-24 15:47:33+08

上例中 missing_date 的日期部分是当前日期（示例运行日为 2026-09-24）。这类「补当前值」的行为会让单元测试在不同日期跑出不同结果，因此解析外部文本时务必给出完整模板。

to_date 与 to_timestamp 的一个已知限制：不能把公历字段与 ISO 周字段混用。官方文档明确说明 to_date / to_timestamp 会拒绝这类混用：

sql
-- 错误！公历的 YYYY 与 ISO 周字段 IW 混用
SELECT to_date('2026-39', 'IYYY-IW');
text
ERROR:  conflicting values for "YYYY" field

而 to_char 不会报错，它会按某一种解释输出，这就更容易埋下隐患。按 ISO 周解析时应统一使用 IYYY / IW / ID，并且不要混入 YYYY / MM / DD。

实战示例 ​

sql
-- 示例库：demo
DROP TABLE IF EXISTS raw_events;
CREATE TABLE raw_events (
  id          BIGSERIAL PRIMARY KEY,
  source      TEXT NOT NULL,
  event_date  TEXT NOT NULL,      -- 原始文本，格式不统一
  event_time  TEXT,
  amount      TEXT,
  payload     TEXT
);

INSERT INTO raw_events (source, event_date, event_time, amount) VALUES
  ('crm',   '2026-09-24',      '15:47:33', '1,234.50'),
  ('crm',   '2026-09-25',      '09:03:00', '2,000.00'),
  ('erp',   '20260926',        '184500',   '350.75'),
  ('erp',   '20260927',        '093000',   '980.10'),
  ('log',   '2026/09/28',      '23:59:59', '0.00'),
  ('crm',   '2026-09-29',      '00:00:01', '15.60'),
  ('erp',   '20260930',        '120000',   '-120.00'),
  ('log',   '2026/10/01',      '08:15:00', '77.77');

步骤 1：按来源分支解析成统一的时间戳

sql
SELECT
  id,
  source,
  event_date,
  CASE source
    WHEN 'crm' THEN to_timestamp(event_date || ' ' || event_time, 'YYYY-MM-DD HH24:MI:SS')
    WHEN 'erp' THEN to_timestamp(event_date || ' ' || event_time, 'YYYYMMDD HH24MISS')
    WHEN 'log' THEN to_timestamp(event_date || ' ' || event_time, 'YYYY/MM/DD HH24:MI:SS')
  END AS parsed_at
FROM raw_events
ORDER BY id;

预期输出：

text
 id | source | event_date |      parsed_at
----+--------+------------+-----------------------
  1 | crm    | 2026-09-24 | 2026-09-24 15:47:33+08
  2 | crm    | 2026-09-25 | 2026-09-25 09:03:00+08
  3 | erp    | 20260926   | 2026-09-26 18:45:00+08
  4 | erp    | 20260927   | 2026-09-27 09:30:00+08
  5 | log    | 2026/09/28 | 2026-09-28 23:59:59+08

步骤 2：找出解析失败的脏数据

sql
-- 用正则先做格式校验，再解析，避免整批报错
SELECT id, source, event_date, event_time
FROM raw_events
WHERE NOT (
  (source = 'crm' AND event_date ~ '^\d{4}-\d{2}-\d{2}$' AND event_time ~ '^\d{2}:\d{2}:\d{2}$')
  OR (source = 'erp' AND event_date ~ '^\d{8}$' AND event_time ~ '^\d{6}$')
  OR (source = 'log' AND event_date ~ '^\d{4}/\d{2}/\d{2}$' AND event_time ~ '^\d{2}:\d{2}:\d{2}$')
);
text
 id | source | event_date | event_time
----+--------+------------+------------
（无行）

步骤 3：解析金额并汇总

sql
SELECT
  source,
  count(*)                                        AS cnt,
  sum(to_number(amount, '999G999G999D99'))        AS total_amount
FROM raw_events
WHERE source IN ('crm', 'erp')
GROUP BY source
ORDER BY source;
text
 source | cnt | total_amount
--------+-----+--------------
 crm    |   3 |      3250.10
 erp    |   3 |      1210.85

步骤 4：按「中文习惯」格式化输出报表

sql
SELECT
  id,
  to_char(
    to_timestamp(event_date || ' ' || event_time, 'YYYY-MM-DD HH24:MI:SS'),
    'YYYY"年"MM"月"DD"日" (DAY) HH24:MI'
  ) AS formatted
FROM raw_events
WHERE source = 'crm'
ORDER BY id;
text
              formatted
---------------------------------------
 2026年09月24日 (Thursday) 15:47
 2026年09月25日 (Friday  ) 09:03
 2026年09月29日 (Tuesday ) 00:00

DAY 会补空格到固定宽度（9 字符），因此出现 Friday 这种尾部空格。需要紧凑输出请用 FMDay 或 DY：

sql
SELECT to_char(DATE '2026-09-25', 'FMDay');   -- Friday

步骤 5：用 FX 严格模式定位格式不符的行

sql
-- 严格解析，任何一行不符即报错，便于逐行排查
SELECT id, to_date(event_date, 'FXYYYY-MM-DD') AS parsed
FROM raw_events
WHERE source = 'crm';
text
ERROR:  invalid value "2026/09/28" for "YYYY"

严格模式在批量导入场景是双刃剑：它能快速暴露格式问题，但会让整条语句失败。生产环境更推荐「先正则校验、再解析」，把失败行单独导出，而不是让整批任务中断。

注意事项 ​

  1. 模板字段区分大小写，MM（月）与 mm（分）含义不同，统一使用大写形式：

    sql
    -- 不推荐
SELECT to_char(now(), 'yyyy-mm-dd hh24:mi:ss');

  2. FM 只影响紧跟其后的一个字段（与 Oracle 不同），多个字段都要去填充时必须逐个加：

    sql
    -- 只作用于 MM
SELECT to_char(DATE '2026-09-04', 'FMMM/DD');    -- 9/04
-- 逐个加才行
SELECT to_char(DATE '2026-09-04', 'FMMM/FMDD');  -- 9/4

  3. to_timestamp 是双态函数，传数字会按 Unix 纪元秒解释，极易误用：

    sql
    -- 错误！把 '20260924' 当纪元秒，得到 1970 年
-- 正确：显式给模板
SELECT to_timestamp('20260924', 'YYYYMMDD');

  4. to_char(numeric, text) 与 to_char(double precision, text) 是不同重载。numeric 走数字模板、double 走浮点模板，混用可能出现意外输出：

    sql
    SELECT to_char(0.1::float8 + 0.2::float8, 'FM990.999999');   -- 0.300000

  5. to_date 返回 date（无时间），要保留时间必须用 to_timestamp：

    sql
    SELECT pg_typeof(to_date('2026-09-24 15:47:33', 'YYYY-MM-DD HH24:MI:SS'));  -- date

  6. to_date 会静默丢弃模板外的输入内容，不会报错，容易漏掉格式不符的数据：

    sql
    SELECT to_date('2026-09-24 垃圾数据', 'YYYY-MM-DD');   -- 2026-09-24，无警告

  7. to_date / to_timestamp 拒绝公历与 ISO 周字段混用，而 to_char 不拒绝：

  8. DDD、WW、IW 的编号基准不同，WW 是「年内第几周（按 1 月 1 日为第 1 周起点）」，IW 是 ISO 周号，二者在跨年周会相差 1：

    sql
    SELECT to_char(DATE '2026-01-01', 'WW') AS ww, to_char(DATE '2026-01-01', 'IW') AS iw;

  9. to_char 输出中文不需要转义字母，但模板里的普通字母会被当字段解析，必须用双引号包裹字面量：

    sql
    -- 错误！T 与 M 会被当模板字段
SELECT to_char(now(), 'YYYY年MM月DD日 T');
-- 正确：字面量加双引号
SELECT to_char(now(), 'YYYY"年"MM"月"DD"日" "T"');

  10. to_char 对 interval 的模板与对 timestamp 不同，interval 模板主要用 HH24 / MI / SS 与 DAY / HOUR / MINUTE / SECOND 等字段：

    sql
    SELECT to_char(INTERVAL '3 days 04:05:06', 'DD HH24:MI:SS');   -- 03 04:05:06

  11. 时区缩写有歧义，CST 可能是中国标准时间也可能是美国中部时间。跨系统交换时用 TZH:TZM 或 OF 输出数字偏移：

    sql
    SELECT to_char(TIMESTAMPTZ '2026-09-24 15:47:33+08', 'TZH:TZM');   -- +08:00

  12. FX 只在解析函数中生效，to_char 中的 FX 被忽略，写上去不会报错但也没有作用。

最佳实践 ​

  1. 解析外部文本一律使用完整模板 + FX，并在入库前先用正则校验，做到「格式不符即刻可见」。
  2. 日期与时间分开存储的源数据，用字符串拼接后再解析时注意中间必须补空格分隔符，否则会拼出 2026-09-2415:47:33：
    sql
    to_timestamp(event_date || ' ' || event_time, 'YYYY-MM-DD HH24:MI:SS')
  3. 报表展示统一封装成视图或函数，把 to_char 模板集中一处管理，避免每个查询各写一套格式：
    sql
    CREATE VIEW v_orders_display AS
SELECT id, to_char(created_at AT TIME ZONE 'Asia/Shanghai', 'YYYY-MM-DD HH24:MI:SS') AS created_text
FROM orders;
  4. 模板一律用大写字段，并在团队规范里明确「禁止小写模板」，消除 MM/mm 的歧义风险。
  5. 需要去填充时逐字段加 FM，不要假设它会影响整串模板。
  6. 数字转文本后若要参与比较或排序，保留原始数值列，不要依赖格式化后的字符串排序：
    sql
    -- 不推荐：字符串排序会让 "10" 排在 "9" 之前
ORDER BY to_char(amount, 'FM9999')
  7. 时区输出用数字偏移（TZH:TZM / OF）而非缩写（TZ），缩写在不同地区有歧义。
  8. 解析时间戳时显式带上时区模板，避免落入会话时区这一隐式依赖：
    sql
    SELECT to_timestamp('2026-09-24 15:47:33 +08', 'YYYY-MM-DD HH24:MI:SS TZH');
  9. 单元测试要冻结当前时间，因为解析不完整模板时会补当前年月，导致测试随日期漂移。
  10. 金额解析统一用 to_number 而非先转 numeric 再替换逗号，to_number 能同时处理千分位、货币符号与括号负数。
  11. 小数秒按需要选择精度：MS（3 位）适合常规日志，US（6 位）适合需要微秒级的审计，FF3 与 MS 数值相同但语义更明确。
  12. 批量解析前先在临时表里试跑，把失败行单独落到错误表，而不是让整条导入语句因一行脏数据全部回滚。

下一节

继续学习：时区转换

参考链接 ​