提要:在本教程中,您将学习PostgreSQL INTERVAL 数据类型以及如何操作时间间隔值。

INTERVAL 数据类型简介

INTERVAL 数据类型允许存储和操作年,月,日,时,分,秒等时间间隔。下面展示了该数据类型:

@ interval [ fields ] [ (p) ]   

一个 INTERVAL 类型的值需要16字节进行存储,它的取值范围从 -178,000,000 年到 178,000,000 年。

另外,一个 INTERVAL 值可以有一个可选的精度值。p 用来指定从 06 的精度,它是保留在第二个字段中的小数位数。

它前面的 @ 是可选的,因此可以省略它。

看个例子:

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

在内部,PostgreSQL 将 INTERVAL 值存储为月,日和秒。月份和日期值是整数,而秒数可以是字段可以有分数。

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

SELECT
 now(),
 now() - INTERVAL '1 year 3 hours 20 minutes' 
             AS "3 hours 20 minutes ago of last year";
             now              | 3 hours 20 minutes ago of last year
------------------------------+-------------------------------------
 2018-01-11 17:10:40.26797+08 | 2017-01-11 13:50:40.26797+08
(1 行记录)

格式化输入 INTERVAL

PostgreSQL 提供以下语法来编写INTERVAL 值:

quantity unit [quantity unit...] [direction]
  • quantity: 是一个数字,可以加上 +-
  • unit:可以是millennium, century, decade, year, month, week, day, hour, minute, second, millisecond, microsecond(千年,世纪,十年,年,月,星期,日,时,分,秒,毫秒,微秒)中的任何值,或是上述值的简写形式,y, m, d 等,也可以是上述值的复数形式,months, years, days 等。
  • direction:可以是 AGO''(空字符)

这个格式称为 postgres_verbose,同样适用于格式化输出 INTERVAL 值。

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

ISO 8601 INTERVAL

PostgreSQL 还允许使用 ISO 8601 规范来格式化 INTERVAL 值:

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

使用这种格式,必须以字母 P 开头,字母 T 用于确定时间单位。

ISO 8601 格式化的单位缩写:

缩写 描述
Y
M 日期部分的月
W
D
H 小时
M 时间部分的分钟
S

注意,M 既可用来表示月,又可表示分钟。具体含义依赖于它前面是否有 T(有 T 表示时间,没有则表示日期)。

下例演示了 ISO 8601 格式化6年5个月4天3小时2分钟1秒的INTERVAL 值:

P6Y5M4DT3H2M1S

格式化输出 INTERVAL

使用 SET intervalstyle 命令设置 INTERVAL 值的输出格式:

SET intervalstyle = 'sql_standard';

PostgreSQL 提供了4种 INTERVAL 值的输出格式:sql standard, postgres, postgresverboseiso_8601。默认格式为 postgres

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';

结果分别是:

格式 结果
sql standard +6-5 +4 +3:02:01
postgres 6 years 5 mons 4 days 03:02:01
postgres_verbose @ 6 years 5 mons 4 days 3 hours 2 mins 1 sec
iso_8601 P6Y5M4DT3H2M1S

INTERVAL 值相关的运算符和函数

运算符

可以在 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

INTERVAL 值转换为字符串

使用 TO_CHAR() 函数,可以将 INTERVAL 值转换为字符串:

TO_CHAR(interval,format)

如下例:

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

结果如下:

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

INTERVAL 值提取各部分

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

调整 INTERVAL

PostgreSQL 提供了 justifydaysjustifyhours 两个函数来将30天调整为一个月,将24小时调整为一天:

SELECT
    justify_days(INTERVAL '30 days'),
    justify_hours(INTERVAL '24 hours');
 justify_days | justify_hours
--------------+---------------
 1 mon        | 1 day
(1 row)
下一篇:PostgreSQL TIME类型