日期时间函数和操作符

最近更新时间:2024-08-15 16:19:42

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