PostgreSQL - DATE/TIME 函数和操作符

简述

我们在数据类型一章中讨论了date/time数据类型。现在，让我们看看date/time运算符和函数。

下表列出了基本算术运算符的行为 -

操作符	例子	结果
+	date '2001-09-28' + integer '7'	date'2001-10-05'
+	date“2001-09-28”+ interval “1 hour”	时间戳'2001-09-28 01:00:00'
+	date“2001-09-28”+time“03:00”	时间戳'2001-09-28 03:00:00'
+	interval “1day”+interval “1hour”	interval '1 day 01:00:00'
+	时间戳 '2001-09-28 01:00' + interval '23 hour'	时间戳'2001-09-29 00:00:00'
+	time“01:00”+interval “3hour”	time'04:00:00'
-	- interval “23 hour”	interval '-23:00:00'
-	date“2001-10-01”-date“2001-09-28”	integer “3”（day）
-	date '2001-10-01' - integer '7'	date'2001-09-24'
-	date '2001-09-28' - interval '1 hour'	时间戳'2001-09-27 23:00:00'
-	time'05:00' - time'03:00'	interval '02:00:00'
-	time“05:00” - interval “2 hour”	time'03:00:00'
-	时间戳 '2001-09-28 23:00' - interval '23 hour'	时间戳'2001-09-28 00:00:00'
-	interval '1 day' - interval '1 hour'	interval '1day-01:00:00'
-	时间戳'2001-09-29 03:00' - 时间戳'2001-09-27 12:00'	interval '1 day 15:00:00'
*	900 * interval '1 second'	interval “00:15:00”
*	21 * interval '1 day'	interval '21day'
*	double precision '3.5' * interval '1 hour'	interval '03:30:00'
/	interval “1hour”/double precision“1.5”	interval '00:40:00'

以下是可用的所有重要date和time相关函数的列表。

序号	函数说明
1	AGE() 减去参数
2	CURRENT DATE/TIME() 当前date和time
3	DATE_PART() 获取子字段（相当于提取）
4	EXTRACT() 获取子字段
5	ISFINITE() 测试有限的date、time和interval （不是 +/-infinity）
6	JUSTIFY 调整interval

AGE（时间戳，时间戳），AGE（时间戳）

序号	函数说明
1	AGE(timestamp, timestamp) 当使用第二个参数的 TIMESTAMP 形式调用时，AGE() 减去参数，产生使用年份和月份且类型为 INTERVAL 的“符号”结果。
2	AGE(timestamp) 当仅使用 TIMESTAMP 作为参数调用时，AGE() 从 current_date 中减去（在午夜）。

函数 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

当前date/time（）

PostgreSQL 提供了许多返回与当前date和time相关的值的函数。以下是一些函数 -

序号	函数说明
1	CURRENT_DATE 提供当前date。
2	CURRENT_TIME 提供带时区的值。
3	CURRENT_TIMESTAMP 提供带时区的值。
4	CURRENT_TIME(precision) 可选地采用精度参数，这会导致结果被四舍五入到second字段中的小数位数。
5	CURRENT_TIMESTAMP(precision) 可选地采用精度参数，这会导致结果被四舍五入到second字段中的小数位数。
6	LOCALTIME 提供没有时区的值。
7	LOCALTIMESTAMP 提供没有时区的值。
8	LOCALTIME(precision) 可选地采用精度参数，这会导致结果被四舍五入到second字段中的小数位数。
9	LOCALTIMESTAMP(precision) 可选地采用精度参数，这会导致结果被四舍五入到second字段中的小数位数。

使用上表中的函数的示例 -


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 还提供了返回当前语句开始time的函数，以及调用函数时的实际当前time。这些函数是 -

序号	函数说明
1	transaction_timestamp() 它等价于 CURRENT_TIMESTAMP，但被命名为清楚地反映它返回的内容。
2	statement_timestamp() 它返回当前语句的开始time。
3	clock_timestamp() 它返回实际的当前time，因此即使在单个 SQL 命令中，它的值也会发生变化。
4	timeofday() 它返回实际的当前time，但作为格式化的文本字符串而不是带有时区值的时间戳。
5	now() 它是一个传统的 PostgreSQL 等价于 transaction_timestamp()。

DATE_PART（文本，时间戳），DATE_PART（文本，interval ），DATE_TRUNC（文本，时间戳）

序号	函数说明
1	DATE_PART('field', source) 这些函数获取子字段。字段参数需要是字符串值，而不是名称。有效的字段名称是：世纪、日、十年、道、多伊、纪元、hour、等道、等年、微second、千年、毫second、分钟、月、季度、second、时区、时区hour、时区分钟、周、年。
2	DATE_TRUNC('field', source) 此函数在概念上类似于数字的trunc函数。source是时间戳或interval 类型的值表达式。字段选择截断输入值的精度。返回值的类型为timestamp或interval。字段的有效值为：微second、毫second、second、分钟、hour、日、周、月、季度、年、十年、世纪、千年

以下是 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（来自时间戳的字段），EXTRACT（来自interval 的字段）

这EXTRACT(field FROM source)函数从date/time值中检索子字段，例如年或hour。源必须是时间戳、time或interval 类型的值表达式。该字段是一个标识符或字符串，用于选择要从源值中提取的字段。EXTRACT 函数返回double precision类型的值。

以下是有效的字段名称（类似于 DATE_PART 函数字段名称）：世纪、日、十年、道、道伊、纪元、hour、等道、等年、微second、千年、毫second、分钟、月、季度、second、时区、时区hour, timezone_minute, 周, 年。

以下是 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(时间戳)、ISFINITE(interval )

序号	函数说明
1	ISFINITE(date) 测试有限date。
2	ISFINITE(timestamp) 测试有限的时间戳。
3	ISFINITE(interval) 测试有限区间。

以下是 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) 调整interval ，以便将 30 day的time段表示为月。返回interval类型
2	JUSTIFY_HOURS(interval) 调整interval ，以便将 24 hourtime段表示为day。返回interval类型
3	JUSTIFY_INTERVAL(interval) 使用 JUSTIFY_DAYS 和 JUSTIFY_HOURS 调整interval ，并进行额外的符号调整。返回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)