下列表格展示了数据库支持的常用日期和时间函数以及操作符。
操作符 | 示例 | 结果 |
+ | 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 |