极客教程PostgreSQL 日期/时间函数和操作符
我们在数据类型章节中讨论了日期/时间数据类型。现在，让我们来看一下日期/时间操作符和函数。
以下表格列出了基本算术操作符的行为：
| 操作符 | 示例 | 结果 |
|---|---|---|
| + | date ‘2001-09-28’ + integer ‘7’ | date ‘2001-10-05’ |
| + | date ‘2001-09-28’ + interval ‘1 hour’ | timestamp ‘2001-09-28 01:00:00’ |
| + | date ‘2001-09-28′ + time ’03:00’ | timestamp ‘2001-09-28 03:00:00’ |
| + | interval ‘1 day’ + interval ‘1 hour’ | interval ‘1 day 01:00:00’ |
| + | timestamp ‘2001-09-28 01:00′ + interval ’23 hours’ | timestamp ‘2001-09-29 00:00:00’ |
| + | time ’01:00′ + interval ‘3 hours’ | time ’04:00:00′ |
| – | – interval ’23 hours’ | interval ‘-23:00:00’ |
| – | date ‘2001-10-01’ – date ‘2001-09-28’ | integer ‘3’ (days) |
| – | date ‘2001-10-01’ – integer ‘7’ | date ‘2001-09-24’ |
| – | date ‘2001-09-28’ – interval ‘1 hour’ | timestamp ‘2001-09-27 23:00:00’ |
| – | time ’05:00′ – time ’03:00′ | interval ’02:00:00′ |
| – | time ’05:00′ – interval ‘2 hours’ | time ’03:00:00′ |
| – | timestamp ‘2001-09-28 23:00′ – interval ’23 hours’ | timestamp ‘2001-09-28 00:00:00’ |
| – | interval ‘1 day’ – interval ‘1 hour’ | interval ‘1 day -01:00:00’ |
| – | timestamp ‘2001-09-29 03:00’ – timestamp ‘2001-09-27 12:00’ | interval ‘1 day 15:00:00’ |
| * | 900 * interval ‘1 second’ | interval ’00:15:00′ |
| * | 21 * interval ‘1 day’ | interval ’21 days’ |
| * | double precision ‘3.5’ * interval ‘1 hour’ | interval ’03:30:00′ |
| / | interval ‘1 hour’ / double precision ‘1.5’ | interval ’00:40:00′ |
以下是所有与日期和时间相关的重要函数的列表。
| 序号 | 功能与描述 |
|---|---|
| 1 | AGE() 减去参数 |
| 2 | CURRENT DATE/TIME() 当前日期和时间 |
| 3 | DATE_PART() 获取子字段（相当于extract函数） |
| 4 | EXTRACT() 获取子字段 |
| 5 | ISFINITE() 测试有限的日期、时间和间隔（不是+/-无穷大） |
| 6 | JUSTIFY 调整间隔 |
AGE(timestamp, timestamp), AGE(timestamp)
| 序号 | 功能和描述 |
|---|---|
| 1 | AGE(timestamp, timestamp) 当第二个参数以TIMESTAMP形式传入时，AGE()函数将两个参数相减，产生一个使用年份和月份的“符号”结果，结果的类型为INTERVAL。 |
| 2 | AGE(timestamp) 当只有TIMESTAMP作为参数传入时，AGE()函数从当前日期（午夜时刻）开始减去该参数。 |
函数AGE(timestamp, timestamp)的示例为 –
testdb=# SELECT AGE(timestamp '2001-04-10', timestamp '1957-06-13');
给定的PostgreSQL语句将产生以下结果：
age
-------------------------
43 years 9 mons 27 days
函数AGE(timestamp)的示例是 −
testdb=# select age(timestamp '1957-06-13');
上述提供的PostgreSQL语句将生成以下结果 –
age
--------------------------
55 years 10 mons 22 days
CURRENT DATE/TIME()
PostgreSQL提供了一些与当前日期和时间相关的函数，以下是其中一些函数：
| 序号 | 功能与描述 |
|---|---|
| 1 | CURRENT_DATE 提供当前日期。 |
| 2 | CURRENT_TIME 提供带有时区的值。 |
| 3 | CURRENT_TIMESTAMP 提供带有时区的值。 |
| 4 | CURRENT_TIME(precision) 可选参数，将结果四舍五入为秒字段中的指定小数位数。 |
| 5 | CURRENT_TIMESTAMP(precision) 可选参数，将结果四舍五入为秒字段中的指定小数位数。 |
| 6 | LOCALTIME 提供无时区的值。 |
| 7 | LOCALTIMESTAMP 提供无时区的值。 |
| 8 | LOCALTIME(precision) 可选参数，将结果四舍五入为秒字段中的指定小数位数。 |
| 9 | LOCALTIMESTAMP(precision) 可选参数，将结果四舍五入为秒字段中的指定小数位数。 |
使用上面表格中的函数的示例：
testdb=# SELECT CURRENT_TIME;
timetz
--------------------
08:01:34.656+05:30
(1 row)
testdb=# SELECT CURRENT_DATE;
date
------------
2013-05-05
(1 row)
testdb=# SELECT CURRENT_TIMESTAMP;
now
-------------------------------
2013-05-05 08:01:45.375+05:30
(1 row)
testdb=# SELECT CURRENT_TIMESTAMP(2);
timestamptz
------------------------------
2013-05-05 08:01:50.89+05:30
(1 row)
testdb=# SELECT LOCALTIMESTAMP;
timestamp
------------------------
2013-05-05 08:01:55.75
(1 row)
PostgreSQL还提供了一些函数，返回当前语句的开始时间，以及在调用函数时的实际当前时间。 这些函数包括：
| 序号 | 功能和描述 |
|---|---|
| 1 | transaction_timestamp() 它等同于CURRENT_TIMESTAMP，但被命名为清晰地反映它返回的内容。 |
| 2 | statement_timestamp() 它返回当前语句的开始时间。 |
| 3 | clock_timestamp() 它返回实际的当前时间，因此它的值甚至在单个SQL命令中也会发生变化。 |
| 4 | timeofday() 它返回实际的当前时间，但作为格式化的文本字符串而不是带有时区的时间戳值。 |
| 5 | now() 它是传统的PostgreSQL中与transaction_timestamp()等价的函数。 |
DATE_PART（text，timestamp），DATE_PART（text，interval），DATE_TRUNC（text，timestamp）
| 序号 | 功能与描述 |
|---|---|
| 1 | DATE_PART(‘field’, source) 这些函数获取子字段。 field 参数应为字符串值，而非名称。有效的字段名称有： century, day, decade, dow, doy, epoch, hour, isodow, isoyear, microseconds, millennium, milliseconds, minute, month, quarter, second, timezone, timezone_hour, timezone_minute, week, year. |
| 2 | DATE_TRUNC(‘field’, source) 该函数在概念上类似于对数字进行截断的 trunc 函数。 source 是类型为timestamp或interval的值表达式。 field 选择要截断输入值的精度。返回值是 timestamp 或 interval 类型的结果。 field 的有效值有： microseconds, milliseconds, second, minute, hour, day, week, month, quarter, year, decade, century, millennium |
下面是DATE_PART(‘field’, source)函数的示例：
testdb=# SELECT date_part('day', TIMESTAMP '2001-02-16 20:38:40');
date_part
-----------
16
(1 row)
testdb=# SELECT date_part('hour', INTERVAL '4 hours 3 minutes');
date_part
-----------
4
(1 row)
下面是使用DATE_TRUNC(‘field’, source)函数的示例：
testdb=# SELECT date_trunc('hour', TIMESTAMP '2001-02-16 20:38:40');
date_trunc
---------------------
2001-02-16 20:00:00
(1 row)
testdb=# SELECT date_trunc('year', TIMESTAMP '2001-02-16 20:38:40');
date_trunc
---------------------
2001-01-01 00:00:00
(1 row)
EXTRACT(field from timestamp), EXTRACT(field from interval)
从日期/时间值中提取年份或小时等子字段的 EXTRACT(field FROM source) 函数。source 必须是类型为 timestamp、time 或 interval 的值表达式。field 是一个选择要从源值中提取的字段的标识符或字符串。EXTRACT 函数返回 double precision 类型的值。
以下是有效的字段名称（类似于 DATE_PART 函数的字段名称）：century、day、decade、dow、doy、epoch、hour、isodow、isoyear、microseconds、millennium、milliseconds、minute、month、quarter、second、timezone、timezone_hour、timezone_minute、week、year。
以下是 EXTRACT(‘field’, source) 函数的示例 –
testdb=# SELECT EXTRACT(CENTURY FROM TIMESTAMP '2000-12-16 12:21:13');
date_part
-----------
20
(1 row)
testdb=# SELECT EXTRACT(DAY FROM TIMESTAMP '2001-02-16 20:38:40');
date_part
-----------
16
(1 row)
ISFINITE(date), ISFINITE(timestamp), ISFINITE(interval)
| 编号 | 功能与描述 |
|---|---|
| 1 | ISFINITE(date) 测试日期是否有限。 |
| 2 | ISFINITE(timestamp) 测试时间戳是否有限。 |
| 3 | ISFINITE(interval) 测试时间间隔是否有限。 |
| 以下是ISFINITE()函数的示例： |
以下是ISFINITE()函数的示例。
testdb=# SELECT isfinite(date '2001-02-16');
isfinite
----------
t
(1 row)
testdb=# SELECT isfinite(timestamp '2001-02-16 21:28:30');
isfinite
----------
t
(1 row)
testdb=# SELECT isfinite(interval '4 hours');
isfinite
----------
t
(1 row)
JUSTIFY_DAYS(interval)，JUSTIFY_HOURS(interval)，JUSTIFY_INTERVAL(interval)
| 序号 | 功能和描述 |
|---|---|
| 1 | JUSTIFY_DAYS(interval) 调整间隔，使30天的时间段表示为月份。返回 interval 类型 |
| 2 | JUSTIFY_HOURS(interval) 调整间隔，使24小时的时间段表示为天数。返回 interval 类型 |
| 3 | JUSTIFY_INTERVAL(interval) 使用JUSTIFY_DAYS和JUSTIFY_HOURS调整间隔，并进行附加的符号调整。返回 interval 类型 |
下面是ISFINITE()函数的示例：
testdb=# SELECT justify_days(interval '35 days');
justify_days
--------------
1 mon 5 days
(1 row)
testdb=# SELECT justify_hours(interval '27 hours');
justify_hours
----------------
1 day 03:00:00
(1 row)
testdb=# SELECT justify_interval(interval '1 mon -1 hour');
justify_interval
------------------
29 days 23:00:00
(1 row)