PostgreSQL 数据类型格式化函数

PostgreSQL 提供了一组强大的格式化函数，用于在各种数据类型（日期/时间、整数、浮点数、数值）与格式化字符串之间进行转换。这些函数在数据展示、报表生成和数据清洗中发挥着重要作用。

格式化函数概述

所有格式化函数都遵循统一的调用约定：

第一个参数：要格式化的值
第二个参数：定义输出或输入格式的模板字符串

核心格式化函数

函数功能对比表

函数签名	功能描述	使用场景
`to_char(timestamp, text)`	时间戳转字符串	报表显示、日志格式化
`to_char(interval, text)`	时间间隔转字符串	持续时间显示
`to_char(numeric_type, text)`	数值转字符串	金额格式化、统计报表
`to_date(text, text)`	字符串转日期	数据导入、格式标准化
`to_number(text, text)`	字符串转数值	数据清洗、类型转换
`to_timestamp(text, text)`	字符串转时间戳	日志解析、数据同步

TIP

对于标准的日期/时间和数值格式，建议直接使用类型转换，更简单高效。格式化函数主要用于处理非标准格式的数据。

日期时间格式化

基础时间格式模式

时间组件模式

模式	描述	示例输出
`HH`	12 小时制小时 (01–12)	05
`HH24`	24 小时制小时 (00–23)	17
`MI`	分钟 (00–59)	31
`SS`	秒 (00–59)	12
`MS`	毫秒 (000–999)	066
`US`	微秒 (000000–999999)	066000

日期组件模式

模式	描述	示例输出
`YYYY`	4 位年份	2002
`MM`	月份编号 (01–12)	04
`DD`	日期 (01–31)	20
`DY`	星期几缩写	Sat
`MONTH`	完整月份名	APRIL

实际应用示例

示例 1：生成用户友好的时间显示

问题陈述：在电商系统中，需要为订单显示易读的时间格式。

解决方案：

sql

-- 订单时间格式化
SELECT
    order_id,
    created_at,
    to_char(created_at, 'YYYY年MM月DD日 HH24:MI') AS 显示时间,
    to_char(created_at, 'Dy') AS 星期几
FROM orders
WHERE order_id = 12345;

输入数据：

sql

order_id: 12345
created_at: 2024-03-15 14:30:25

预期输出：

order_id | created_at          | 显示时间           | 星期几
---------|--------------------|--------------------|--------
12345    | 2024-03-15 14:30:25| 2024年03月15日 14:30| Fri

分析过程：

YYYY年MM月DD日：创建中文日期格式
HH24:MI：使用 24 小时制显示时分
Dy：显示星期几的英文缩写

示例 2：时间间隔的人性化显示

问题陈述：显示任务执行时长，让用户直观了解处理耗时。

解决方案：

sql

-- 任务执行时长格式化
SELECT
    task_name,
    start_time,
    end_time,
    end_time - start_time AS duration_interval,
    to_char(end_time - start_time, 'HH24:MI:SS') AS 执行时长
FROM task_logs
WHERE DATE(start_time) = CURRENT_DATE;

输入数据：

sql

task_name: 数据备份
start_time: 2024-03-15 02:00:00
end_time: 2024-03-15 04:15:30

预期输出：

task_name | start_time          | end_time            | duration_interval | 执行时长
----------|--------------------|--------------------|-------------------|----------
数据备份   | 2024-03-15 02:00:00| 2024-03-15 04:15:30| 02:15:30         | 02:15:30

字符串转日期的应用

示例 3：处理多种日期格式的数据导入

问题陈述：从外部系统导入用户数据，日期格式不统一，需要标准化处理。

解决方案：

sql

-- 处理不同格式的日期字符串
WITH import_data AS (
    SELECT '05 Dec 2000' as date_str, 'DD Mon YYYY' as format_pattern
    UNION ALL
    SELECT '2000/12/05', 'YYYY/MM/DD'
    UNION ALL
    SELECT '12-05-2000', 'MM-DD-YYYY'
)
SELECT
    date_str AS 原始格式,
    format_pattern AS 格式模式,
    to_date(date_str, format_pattern) AS 标准日期
FROM import_data;

预期输出：

原始格式      | 格式模式      | 标准日期
-------------|-------------|----------
05 Dec 2000  | DD Mon YYYY | 2000-12-05
2000/12/05   | YYYY/MM/DD  | 2000-12-05
12-05-2000   | MM-DD-YYYY  | 2000-12-05

分析过程：

DD Mon YYYY：处理"日月名年"格式
YYYY/MM/DD：处理 ISO 标准斜杠分隔格式
MM-DD-YYYY：处理美式日期格式
所有格式最终转换为标准的YYYY-MM-DD格式

格式模式修饰符

修饰符功能说明

修饰符	功能	应用示例
`FM`	去除填充零和空格	`FMMonth` → `March`而非`March`
`TH`	添加序数后缀	`DDTH` → `15th`
`FX`	严格匹配格式	要求输入严格按模板格式
`TM`	本地化模式	根据`lc_time`设置显示本地语言

示例 4：灵活的日期格式输出

问题陈述：为不同地区用户提供本地化的日期显示。

解决方案：

sql

-- 多种格式的日期显示
SELECT
    CURRENT_DATE as 当前日期,
    to_char(CURRENT_DATE, 'Month DD, YYYY') AS 标准格式,
    to_char(CURRENT_DATE, 'FMMonth DD, YYYY') AS 无填充格式,
    to_char(CURRENT_DATE, 'DDth "of" FMMonth, YYYY') AS 序数格式,
    to_char(CURRENT_DATE, 'TMMonth DD, YYYY') AS 本地化格式;

分析过程：

标准格式：March 15, 2024（月份名用空格填充到 9 字符）
无填充格式：March 15, 2024（去除多余空格）
序数格式：15th of March, 2024（添加序数词和介词）
本地化格式：根据系统 locale 设置显示

WARNING

使用FX修饰符时，输入字符串必须严格匹配模板格式，包括空格和分隔符的数量。

数值格式化

数值格式模式

模式	功能	示例
`9`	数字位（可省略前导零）	`999` → `12`
`0`	数字位（保留前导零）	`000` → `012`
`,`	千位分隔符	`9,999` → `1,234`
`.`	小数点	`99.99` → `12.34`
`$`	货币符号	`$999` → `$123`
`PR`	负数括号表示	`999PR` → `<123>`
`S`	正负号	`S999` → `+123`

实际应用示例

示例 5：财务报表金额格式化

问题陈述：财务系统需要按照会计标准显示金额，包括千位分隔符、货币符号和负数处理。

解决方案：

sql

-- 财务金额格式化
WITH financial_data AS (
    SELECT 1234567.89 AS amount, '销售收入' AS item_type
    UNION ALL
    SELECT -45678.50, '销售退款'
    UNION ALL
    SELECT 0.05, '银行利息'
)
SELECT
    item_type AS 项目类型,
    amount AS 原始金额,
    to_char(amount, '$999,999,999.99') AS 标准格式,
    to_char(amount, '$999,999,999.99PR') AS 负数括号格式,
    to_char(amount, 'L999,999,999.99') AS 本地货币格式
FROM financial_data;

预期输出：

项目类型 | 原始金额    | 标准格式        | 负数括号格式      | 本地货币格式
--------|------------|----------------|------------------|----------------
销售收入 | 1234567.89 | $1,234,567.89  | $1,234,567.89    | ¥1,234,567.89
销售退款 | -45678.50  | $  -45,678.50  | $<45,678.50>     | ¥  -45,678.50
银行利息 | 0.05       | $        0.05  | $        0.05    | ¥        0.05

示例 6：科学计数法格式化

问题陈述：在科学计算或数据分析中，需要将大数值以科学计数法显示。

解决方案：

sql

-- 科学计数法格式化
SELECT
    12345678.9 AS 原始数值,
    to_char(12345678.9, '9.99EEEE') AS 科学计数法,
    to_char(0.000123, '9.99EEEE') AS 小数科学计数法;

预期输出：

原始数值      | 科学计数法   | 小数科学计数法
-------------|-------------|---------------
12345678.9   | 1.23E+07    | 1.23E-04

字符串转数值的应用

示例 7：清洗带格式的数值数据

问题陈述：从 CSV 文件导入的财务数据包含千位分隔符和货币符号，需要转换为数值类型进行计算。

解决方案：

sql

-- 带格式字符串转数值
WITH raw_data AS (
    SELECT '$12,454.80' AS amount_str, '99G999D99' AS format_pattern
    UNION ALL
    SELECT '12,454.8-', '99G999D9S'
    UNION ALL
    SELECT '(1,234.56)', '999G999D99PR'
)
SELECT
    amount_str AS 原始字符串,
    format_pattern AS 格式模式,
    to_number(amount_str, format_pattern) AS 转换后数值,
    to_number(amount_str, format_pattern) * 1.1 AS 加税后金额
FROM raw_data;

预期输出：

原始字符串   | 格式模式     | 转换后数值 | 加税后金额
------------|-------------|-----------|----------
$12,454.80  | 99G999D99   | 12454.80  | 13700.28
12,454.8-   | 99G999D9S   | -12454.8  | -13700.28
(1,234.56)  | 999G999D99PR| -1234.56  | -1358.02

分析过程：

G：千位分隔符（使用 locale 设置）
D：小数点（使用 locale 设置）
S：正负号标识
PR：括号表示负数

高级应用场景

复杂业务场景示例

示例 8：多时区时间处理

问题陈述：国际化应用需要将 UTC 时间转换为不同时区的本地时间显示。

解决方案：

sql

-- 多时区时间格式化
WITH timezone_data AS (
    SELECT
        '2024-03-15 08:30:00'::timestamp AS utc_time,
        'UTC' AS source_tz
)
SELECT
    utc_time AS UTC时间,
    to_char(utc_time AT TIME ZONE 'Asia/Shanghai', 'YYYY-MM-DD HH24:MI:SS TZ') AS 北京时间,
    to_char(utc_time AT TIME ZONE 'America/New_York', 'YYYY-MM-DD HH24:MI:SS TZ') AS 纽约时间,
    to_char(utc_time AT TIME ZONE 'Europe/London', 'YYYY-MM-DD HH24:MI:SS TZ') AS 伦敦时间
FROM timezone_data;

示例 9：动态报表格式化

问题陈述：根据数值大小动态选择最适合的显示格式。

解决方案：

sql

-- 动态数值格式化
WITH sales_data AS (
    SELECT 1250000 AS amount, '大额销售' AS category
    UNION ALL
    SELECT 1250 AS amount, '普通销售' AS category
    UNION ALL
    SELECT 12.5 AS amount, '小额销售' AS category
)
SELECT
    category AS 类别,
    amount AS 原始金额,
    CASE
        WHEN amount >= 1000000 THEN to_char(amount/10000, 'FM999.9') || '万'
        WHEN amount >= 1000 THEN to_char(amount, 'FM9,999')
        ELSE to_char(amount, 'FM999.99')
    END AS 格式化显示
FROM sales_data;

预期输出：

类别     | 原始金额 | 格式化显示
---------|---------|----------
大额销售 | 1250000 | 125万
普通销售 | 1250    | 1,250
小额销售 | 12.5    | 12.5

格式化函数的性能考虑

TIP

性能优化建议

大数据集处理：考虑在应用层而非数据库层进行格式化
索引利用：避免在 WHERE 子句中对索引列使用格式化函数
缓存策略：对于静态格式化结果，考虑使用缓存
批量处理：使用 CASE 语句进行条件格式化，减少函数调用次数

常见错误处理

格式不匹配错误

WARNING

常见错误

sql

-- 错误示例：格式不匹配
SELECT to_date('2024-13-45', 'YYYY-MM-DD');
-- ERROR: date/time field value out of range

-- 正确处理方式
SELECT
    CASE
        WHEN date_str ~ '^\d{4}-\d{2}-\d{2}$'
        THEN to_date(date_str, 'YYYY-MM-DD')
        ELSE NULL
    END AS parsed_date
FROM import_table;

数值溢出处理

sql

-- 安全的数值转换
CREATE OR REPLACE FUNCTION safe_to_number(
    input_text TEXT,
    format_text TEXT DEFAULT NULL
) RETURNS NUMERIC AS $$
BEGIN
    IF format_text IS NULL THEN
        RETURN input_text::NUMERIC;
    ELSE
        RETURN to_number(input_text, format_text);
    END IF;
EXCEPTION
    WHEN OTHERS THEN
        RETURN NULL;
END;
$$ LANGUAGE plpgsql;

总结

PostgreSQL 的格式化函数提供了强大而灵活的数据转换能力：

统一接口：所有函数采用相同的参数模式
丰富模板：支持详细的格式控制
本地化支持：自动适应不同 locale 设置
业务适用性：满足报表、数据清洗、国际化等需求

通过合理使用这些函数，可以实现高质量的数据展示和灵活的数据处理，但需要注意性能影响和错误处理。

INFO

最佳实践

优先使用标准类型转换，格式化函数用于特殊需求
在生产环境中添加适当的错误处理机制
考虑将复杂格式化逻辑封装为函数
定期检查和优化格式化相关的查询性能

PostgreSQL 数据类型格式化函数 ​

格式化函数概述 ​

核心格式化函数 ​

函数功能对比表 ​

日期时间格式化 ​

基础时间格式模式 ​

时间组件模式 ​

日期组件模式 ​

实际应用示例 ​

示例 1：生成用户友好的时间显示 ​

示例 2：时间间隔的人性化显示 ​

字符串转日期的应用 ​

示例 3：处理多种日期格式的数据导入 ​

格式模式修饰符 ​

修饰符功能说明 ​

示例 4：灵活的日期格式输出 ​

数值格式化 ​

数值格式模式 ​

实际应用示例 ​

示例 5：财务报表金额格式化 ​

示例 6：科学计数法格式化 ​

字符串转数值的应用 ​

示例 7：清洗带格式的数值数据 ​

高级应用场景 ​

复杂业务场景示例 ​

示例 8：多时区时间处理 ​

示例 9：动态报表格式化 ​

格式化函数的性能考虑 ​

常见错误处理 ​

格式不匹配错误 ​

数值溢出处理 ​

总结 ​

PostgreSQL 数据类型格式化函数

格式化函数概述

核心格式化函数

函数功能对比表

日期时间格式化

基础时间格式模式

时间组件模式

日期组件模式

实际应用示例

示例 1：生成用户友好的时间显示

示例 2：时间间隔的人性化显示

字符串转日期的应用

示例 3：处理多种日期格式的数据导入

格式模式修饰符

修饰符功能说明

示例 4：灵活的日期格式输出

数值格式化

数值格式模式

实际应用示例

示例 5：财务报表金额格式化

示例 6：科学计数法格式化

字符串转数值的应用

示例 7：清洗带格式的数值数据

高级应用场景

复杂业务场景示例

示例 8：多时区时间处理

示例 9：动态报表格式化

格式化函数的性能考虑

常见错误处理

格式不匹配错误

数值溢出处理

总结