日期格式转换

数据库和前端的世界经常在日期长啥样这事上意见不合。PostgreSQL 能把日期存成 DATE、TIMESTAMP 甚至 TIMESTAMPTZ，但这种格式不一定适合直接给用户看。比如，标准的 2023-10-01 12:30:45，设计师可能更想看到 2023年10月01日, 12:30。有时候还得给报表或者 API 格式化日期。

在 PostgreSQL 里，把日期转成字符串或者反过来用的就是 TO_CHAR() 和 TO_DATE() 这俩函数。

TO_CHAR() 函数

TO_CHAR() 就是你处理时间数据变成可读字符串的好帮手。它接收一个日期或时间戳，然后按你给的格式模板输出字符串。

语法

TO_CHAR(value, format)

• value —— 你要转换的日期或时间戳。
• format —— 格式模板字符串，决定你想怎么显示日期。

常用格式模板

完整格式列表可以看 PostgreSQL 官方文档。

TO_CHAR() 用法例子

报表日期格式化

SELECT TO_CHAR(NOW(), 'DD.MM.YYYY') AS formatted_date;
-- 结果: '09.10.2023'

12小时制时间显示

SELECT TO_CHAR(NOW(), 'HH12:MI AM') AS formatted_time;
-- 结果: '03:45 PM'

月份英文输出

SELECT TO_CHAR(NOW(), 'Month') AS month_name;
-- 结果: 'October '

注意：PostgreSQL 会在后面加个空格。这不是 bug，是 feature！想去掉空格可以用 TRIM()：

SELECT TRIM(TO_CHAR(NOW(), 'Month')) AS trimmed_month_name;

自定义格式

SELECT TO_CHAR(NOW(), 'YYYY/MM/DD HH24:MI:SS') AS custom_format;
-- 结果: '2023/10/09 15:45:30'

用户界面友好格式

SELECT TO_CHAR(NOW(), 'DD "月" YYYY 年') AS user_friendly_date;
-- 结果: '09 月 2023 年'

TO_DATE() 函数

TO_DATE() 干的是反过来的事：它把字符串转成 DATE 类型。为啥要这样？比如用户输入了 01-10-2023 这种格式，PostgreSQL 得“看懂”这是什么日期。

语法

TO_DATE(value, format)

• value —— 包含日期的字符串。
• format —— 描述字符串格式的模板。

TO_DATE() 用法例子

字符串转日期

SELECT TO_DATE('01-10-2023', 'DD-MM-YYYY') AS date_value;
-- 结果: '2023-10-01' (数据类型: DATE)

字符串日期和表里日期比对

假设有个表 appointments，有个 appointment_date 列，类型是 DATE。用户输入日期字符串：

SELECT *
FROM appointments
WHERE appointment_date = TO_DATE('2023-10-09', 'YYYY-MM-DD');

格式不对的例子

注意：如果字符串格式和模板不一致，会报错！比如：

SELECT TO_DATE('01/10/2023', 'DD-MM-YYYY');
-- 错误: 输入格式不正确

校验用户数据

假设我们建了个订单表，日期是用户输入的：

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    order_date DATE
);

-- 插入数据时把字符串转成日期
INSERT INTO orders (order_date)
VALUES (TO_DATE('10-09-2023', 'MM-DD-YYYY'));

实用例子

报表格式化. 表 sales 里有个 sale_date 列（类型 TIMESTAMP）。要输出报表，日期格式成 DD.MM.YYYY。

-- 示例数据
CREATE TABLE sales (
    sale_id SERIAL PRIMARY KEY,
    sale_date TIMESTAMP
);

INSERT INTO sales (sale_date)
VALUES
    ('2023-10-01 15:30:00'),
    ('2023-10-02 10:15:00'),
    ('2023-10-03 12:45:00');

-- 报表
SELECT sale_id,
       TO_CHAR(sale_date, 'DD.MM.YYYY') AS formatted_date
FROM sales;

用户数据转换. 假如用户输入的日期是 MM/DD/YYYY 格式。要把它转成 DATE 存进系统。

INSERT INTO sales (sale_date)
VALUES (TO_TIMESTAMP('10/01/2023 15:30:00', 'MM/DD/YYYY HH24:MI:SS'));

常见坑和建议

格式不对。经常遇到的坑是字符串格式和模板不一致。比如用户输入 01-10-2023，但模板写成 MM/DD/YYYY，PostgreSQL 就会报错。建议：把用户输入先校验好再传 SQL。

TO_CHAR() 格式里的空格。有些格式，比如 MONTH，会自动加空格。如果不想要，用 TRIM()。

字符串解析出错。字符串里有奇怪字符或者格式不对，PostgreSQL 就转不了。建议：插库前用正则或者别的方式多做点校验。

时间格式用错。比如你拿 TIMESTAMP 用 DATE 的模板处理。建议：确保你用的数据类型和需求匹配。

TO_CHAR() 和 TO_DATE() 这俩函数让你玩转时间数据。你可以自定义报表格式、转换用户输入，让 SQL 查询更易读。实际开发里，这些函数经常用来做数据可视化、报表、系统集成和用户界面准备。