PostgreSQL 教程: interval 间隔数据类型

摘要：在本教程中，您将了解 PostgreSQL 的 interval 数据类型以及如何操作间隔值。

PostgreSQL interval 数据类型简介

间隔数据类型允许您存储和操作一段时间（以年、月、日、小时、分钟、秒等为单位）。下面说明了间隔类型：

@ interval [ fields ] [ (p) ]

一个间隔值需要 16 个字节的存储大小，可以存储范围为-178,000,000年到178,000,000年的时间段。

另外，间隔值可以有一个可选的精度值p，允许的范围是0到6。精度p是第二个字段中保留的小数位数。

at 符号 (@) 是可选的，因此您可以省略它。

以下示例显示了一些间隔值：

interval '2 months ago';
interval '3 hours 20 minutes';

在内部，PostgreSQL 将间隔值存储为月、日和秒。月数和天数的值是整数，而秒数部分可以有小数。

进行日期或时间的算术运算时，间隔值非常有用。例如，如果你想知道去年当前时间3小时2分钟前的时间，可以使用以下语句：

SELECT
	now(),
	now() - INTERVAL '1 year 3 hours 20 minutes' 
             AS "3 hours 20 minutes ago of last year";

让我们看看如何格式化间隔值的输入和输出。

PostgreSQL interval 输入格式

PostgreSQL 为您提供了以下详细语法来编写间隔值：

quantity unit [quantity unit...] [direction]

• quantity是数字、符号+或者-也是被接受的。
• unit可以是千年（millennium）、世纪（century）、十年（decade）、年（year）、月（month）、周（week）、日（day）、小时（hour）、分钟（minute）、秒（second）、毫秒（millisecond）、微秒（microsecond），或缩写（y、m、d 等），或复数形式（months、days 等）。
• direction可以是ago或空字符串''。

这种格式称为postgres_verbose，也被用于 interval 的输出格式。以下示例说明了一些使用详细语法的间隔值：

INTERVAL '1 year 2 months 3 days';
INTERVAL '2 weeks ago';

ISO 8601 间隔格式

除了上面的详细语法之外，PostgreSQL 还允许您通过两种方式使用ISO 8601时间间隔编写间隔值：带指示符的格式和替代格式。

带指示符的ISO 8601格式如下：

P quantity unit [ quantity unit ...] [ T [ quantity unit ...]]

在此格式中，间隔值必须以字母P开头。字母T用于确定一天中的时间单位。

下表说明了ISO 8601间隔单位缩写：

请注意，M可能是几个月或几分钟，具体取决于它是出现在字母T之前还是之后。

例如，间隔 “6 年 5 个月 4 天 3 小时 2 分 1 秒” 可以按照 ISO 8601 指示符格式编写如下：

P6Y5M4DT3H2M1S

替代的ISO 8601形式是：

P [ years-months-days ] [ T hours:minutes:seconds ]

它还必须以字母P开头，并且用字母T分隔间隔值的日期和时间部分。例如，间隔值6 years 5 months 4 days 3 hours 2 minutes 1 second可以用ISO 8601替代形式写成：

P0006-05-04T03:02:01

PostgreSQL interval 输出格式

间隔值的输出样式可以通过SET intervalstyle命令设置，例如：

SET intervalstyle = 'sql_standard';

PostgreSQL 提供四种输出格式：sql standard、postgres、postgresverbose和iso_8601。PostgresQL 默认使用postgres样式来格式化间隔值。

下面以四种形式表示了间隔值6 years 5 months 4 days 3 hours 2 minutes 1 second：

SET intervalstyle = 'sql_standard';

SELECT
	INTERVAL '6 years 5 months 4 days 3 hours 2 minutes 1 second';


SET intervalstyle = 'postgres';

SELECT
	INTERVAL '6 years 5 months 4 days 3 hours 2 minutes 1 second';


SET intervalstyle = 'postgres_verbose';

SELECT
	INTERVAL '6 years 5 months 4 days 3 hours 2 minutes 1 second';


SET intervalstyle = 'iso_8601';

SELECT
	INTERVAL '6 years 5 months 4 days 3 hours 2 minutes 1 second';

PostgreSQL 间隔相关的运算符和函数

interval 运算符

您可以将算术运算符（+、-、*等）应用于间隔值，例如：

SELECT
INTERVAL '2h 50m' + INTERVAL '10m'; -- 03:00:00

SELECT
INTERVAL '2h 50m' - INTERVAL '50m'; -- 02:00:00

SELECT
600 * INTERVAL '1 minute'; -- 10:00:00

将 PostgreSQL 间隔值转换为字符串

要将间隔值转换为字符串，请使用TO_CHAR()函数。

TO_CHAR(interval,format)

该TO_CHAR()函数将第一个参数作为间隔值，第二个参数作为格式，并返回一个以指定格式表示间隔的字符串。

请参见以下示例：

SELECT
    TO_CHAR(
        INTERVAL '17h 20m 05s',
        'HH24:MI:SS'
    );

它返回以下输出：

 to_char
----------
 17:20:05
(1 row)

从 PostgreSQL 间隔值提取数据

要从间隔值中提取年、月、日等字段，请使用EXTRACT()函数。

EXTRACT(field FROM interval)

field字段可以是您要从时间间隔中提取的年（year）、月（month）、日（date）、小时（hour）、分钟（minute）等。extract函数返回一个双精度类型的浮点数值。

请参见以下示例：

SELECT
    EXTRACT (
        MINUTE
        FROM
            INTERVAL '5 hours 21 minutes'
    );

在此示例中，我们从间隔值5 hours 21 minutes中提取分钟，并按预期返回21：

 date_part
-----------
        21
(1 row)

调整间隔值

PostgreSQL 提供了两个函数justifydays 和justifyhours ，可以让你将 30 天的间隔调整为一个月，将 24 小时的间隔调整为一天：

SELECT
    justify_days(INTERVAL '30 days'),
    justify_hours(INTERVAL '24 hours');

 justify_days | justify_hours
--------------+---------------
 1 mon        | 1 day
(1 row)

此外，justify_interval函数可使用justifydays和justifyhours带有附加符号调整来调整间隔值：

SELECT
    justify_interval(interval '1 year -1 hour');

     justify_interval
--------------------------
 11 mons 29 days 23:00:00
(1 row)

在本教程中，您了解了 PostgreSQL 间隔数据类型以及如何操作间隔值。