如何在 PostgreSQL 中格式化日期？

在本指南中，您将了解如何将 DATE 值格式化为多种不同的格式，如何将 DATE 值插入到表中，等等。

概括

您可以在Postgres 中以多种不同格式将日期值插入 DATE 列 ，但推荐的格式是 YYYY-MM-DD 的 ISO 格式。

当您使用 TO_CHAR 函数显示日期时，您可以在 Postgres 中格式化日期，例如 TO_CHAR(order_date, 'MM-DD-YYYY')。

Postgres 日期数据类型

我们将 在本指南中使用 DATE 数据类型。

Postgres 中的 DATE 数据类型 捕获没有时间分量的日期 。

它可以存储一系列值：从公元前 4,713 年到公元 5,874,897 年。

要定义具有 DATE 数据类型的列，只需指定数据类型：

CREATE TABLE cust_order (
  order_id INT,
  order_date DATE
);

order_date 列存储为 DATE 值。

让我们在这个表中插入一些数据。

您可以在我的 GitHub 存储库 中找到本指南中使用的所有 SQL 脚本 。

在表格中插入日期

在许多其他数据库中，您必须使用某种默认格式插入日期值，或使用函数将输入格式转换为所需格式。

但是，在 Postgres 中，您可以插入许多不同类型的日期数据，并且可以正常工作。

推荐的格式是符合 ISO 8601 标准的格式，即 YYYY-MM-DD。

下面是一些在 Postgres 中将 数据 插入 DATE 列的示例。

INSERT INTO cust_order (order_id, order_date) VALUES (1, '2022-10-13');
INSERT INTO cust_order (order_id, order_date) VALUES (2, '14-OCT-2022');
INSERT INTO cust_order (order_id, order_date) VALUES (3, '20221015');

所有这三行都应该毫无问题地插入。

但是，我们可以尝试运行这个语句：

INSERT INTO cust_order (order_id, order_date) VALUES (4, '16-10-2022');

我们会得到一个错误：

ERROR:  date/time field value out of range: "16-10-2022"
LINE 1: ...INTO cust_order (order_id, order_date) VALUES (4, '16-10-202...
HINT:  Perhaps you need a different "datestyle" setting.
SQL state: 22008
Character: 58

发生这种情况是因为我们指定的格式对数据库来说不清楚哪个字段是日期，哪个字段是月份。 我们通过查看它知道 16 是一天，因为没有 16 月份的编号。

但是，数据库不知道这一点。

如错误消息中的提示所述，解决此问题的一种方法是使用不同的 DateStyle 设置。 您可以在此处的 Postgres 文档 中找到更多信息 。

我们可以从这个表中选择并查看结果。

SELECT order_id, order_date
FROM cust_order;

结果 :

order_id	order_date
1	2022-10-13
2	2022-10-14
3	2022-10-15

此处显示了插入的三行。

使用 TO_CHAR 以特定格式显示日期

当我们从表中选择 DATE 列时，我们可以看到显示的格式：

SELECT order_id, order_date
FROM cust_order;

结果：

order_id	order_date
1	2022-10-13
2	2022-10-14
3	2022-10-15

日期值的格式为 YYYY-MM-DD。

如果我们想以不同的格式显示它怎么办？

您可以在 Postgres 中使用 TO_CHAR 函数。

TO_CHAR 函数如下所示：

TO_CHAR (date_value, output_format)

参数是：

• date_value ：您希望以不同格式显示的值。
• output_format ：要显示的格式

output_format 必须使用单引号并使用下表中的模式。

例如，您的 YYYY 函数中的值在显示时将被替换为四位数的年份。

这是适用于日期的输出格式值的列表。

Pattern	Description
Y,YYY	year (4 or more digits) with a comma
YYYY	year (4 or more digits)
YYY	last 3 digits of year
YY	last 2 digits of year
Y	last digit of year
IYYY	ISO 8601 week-numbering year
IYY	last 3 digits of ISO 8601 week-numbering year
IY	last 2 digits of ISO 8601 week-numbering year
I	last digit of ISO 8601 week-numbering year
BC, bc, AD, or ad	era indicator without periods
B.C., b.c., A.D. or a.d.	era indicator with periods
MONTH	full upper case month name (blank-padded to 9 characters)
Month	full capitalised month name (blank-padded to 9 characters)
month	full lower case month name (blank-padded to 9 characters)
MON	abbreviated upper case month name
Mon	abbreviated capitalised month name
mon	abbreviated lower case month name
MM	month number (01-12)
DAY	full upper case day name (blank-padded to 9 characters)
Day	full capitalised day name (blank-padded to 9 characters)
day	full lower case day name (blank-padded to 9 characters)
DY	abbreviated upper case day name (blank-padded to 9 characters)
Dy	abbreviated capitalised day name (blank-padded to 9 characters)
dy	abbreviated lower case day name (blank-padded to 9 characters)
DDD	day of year (001-366)
IDDD	day of ISO 8601 week-numbering year (001-371). Day 1 of the year is Monday of the first ISO week
DD	day of month (01-31)
D	day of the week, Sunday (1) to Saturday (7)
ID	ISO 8601 day of the week, Monday (1) to Sunday (7)
W	week of the month (1-5). The first week starts on the first day of the month
WW	week number of the year (1-53). The first week starts on the first day of the year
IW	week number of ISO 8601 week-numbering year (01-53). The first Thursday of the year is in week 1.
CC	2 digit century (the 21st century starts on 2001-01-01)
J	Julian Date (number of days since Nov 24, 4714 BC)
Q	quarter
RM	month in upper case Roman numerals (I-XII, I=January)
rm	month in lower case Roman numerals (i-xii, i=January)

让我们看一些例子。

示例 1 – DD/MM/YYYY

这是以 DD/MM/YYYY 格式格式化日期的示例。

SELECT
order_date, 
TO_CHAR(order_date, 'DD/MM/YYYY') AS formatted_date
FROM cust_order;

结果：

order_date	formatted_date
2022-10-13	13/10/2022
2022-10-14	14/10/2022
2022-10-15	15/10/2022

示例 2 – 名称

这是一个使用日期和月份名称的示例。 我们还添加了日期相同部分的两个示例：Day（日期名称）和 DD（日期编号）。

SELECT
order_date, 
TO_CHAR(order_date, 'Day, DD Month, YYYY') AS formatted_date
FROM cust_order;

我们可以看到日期、日期、月份名称和年份的名称。

示例 3 – 其他属性

这是获取日期的其他属性的示例。

SELECT
order_date, 
TO_CHAR(order_date, 'MM') AS month_num,
TO_CHAR(order_date, 'DDD') AS day_of_year,
TO_CHAR(order_date, 'ID') AS day_of_week,
TO_CHAR(order_date, 'WW') AS week_of_year,
TO_CHAR(order_date, 'J') AS julian_date
FROM cust_order;

结果：

我们可以在这里看到日期值的一些不同属性。

经常问的问题

如何在 PostgreSQL 中将日期转换为字符串？

您可以使用 TO_CHAR 函数将日期值转换为字符串。 这个函数的输出是一个字符串。

如上面的示例所示，您可以根据需要定义输出格式。

如何将日期时间转换为 PostgreSQL 中的日期？

要将 DATETIME 转换为 DATE（或删除时间组件），您可以使用 DATE 函数或 ::DATE 运算符。

您可以将 DATETIME 包含在 DATE 函数中。 或者，您可以在 DATETIME 值之后添加 ::DATE。

这是一个使用 NOW() 函数的示例，该函数以 DATETIME 格式返回当前日期和时间。

SELECT
NOW(),
NOW()::DATE,
DATE(NOW());

结果：

now	now	date
2022-10-13 18:28:10.356797+11	2022-10-13	2022-10-13

我们可以在第二列和第三列中看到时间分量已被删除。

结论

在 Postgres 中格式化日期可以使用非常灵活的 TO_CHAR 函数来完成。 您还可以使用多种格式插入日期值，但推荐的格式是 YYYY-MM-DD 的 ISO 格式。