日期时间函数和操作符

最近更新时间:2019-04-22 11:05:56

下列表格展示了数据库支持的常用日期和实际函数以及操作符。

操作符 示例 结果
+ 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'
- 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'

下面的表格是常用的时间/日期函数。

函数 返回值 描述 示例 结果
age(timestamp, timestamp) interval 计算时间差 age(timestamp '2001-04-10', timestamp '1957-06-13') 43 years 9 mons 27 days
age(timestamp) interval 计算时间差 age(timestamp '1957-06-13') 43 years 8 mons 3 days
current_date date 现在的日期 select current_date; 2019-02-18
current_time time with time zone 现在的一天中的时间 select current_time; 16:42:59.991189+08
current_timestamp timestamp with time zone 现在的时间戳 select current_timestamp; 2019-02-18 16:43:20.167284+08
date_part(text, timestamp) double precision 获取时间日期的一部分值 date_part('hour', timestamp '2001-02-16 20:38:40') 20
date_part(text, interval) double precision 获取时间日期的一部分值 date_part('month', interval '2 years 3 months') 3
date_trunc(text, timestamp) timestamp 对日期时间的指定部分清零 date_trunc('hour', timestamp '2001-02-16 20:38:40') 2001-02-16 20:00:00
extract(field from timestamp) double precision 与date_part类似 extract(hour from timestamp '2001-02-16 20:38:40') 20
extract(field from interval) double precision 与date_part类似 extract(month from interval '2 years 3 months') 3
localtime time 获取本地时间 select localtime; 16:56:09.339026
localtimestamp timestamp 本地日期和实际 select localtimestamp; 2019-02-18 16:56:42.331012
now() timestamp with time zone 获取现在时间 select now(); 2019-02-18 16:56:58.843212+08
timeofday() text 现在日期和时间 select timeofday(); Mon Feb 18 16:57:27.677262 2019 CST