计算时间差天数
select extract(day FROM (age('2017-12-10'::date , '2017-12-01'::date)));
计算时间差秒数
select extract(epoch FROM (now() - (now()-interval '1 day') ));
extract函数格式:
extract (field from source)
extract函数是从日期或者时间数值里面抽取子域,比如年、月、日等。source必须是timestamp、time、interval类型的值表达式。field是一个标识符或字符串,是从源数据中的抽取的域。
1. century (世纪)
test=# select extract (century from timestamp '2017-07-31 22:18:00');
date_part
-----------
21
(1 row)
2. year (年)
test=# select extract (year from timestamp '2017-07-31 22:18:00');
date_part
-----------
2017
(1 row)
3. decade (得到年份除10的值)
test=# select extract (decade from timestamp '2017-07-31 22:18:00');
date_part
-----------
201
(1 row)
4. millennium(得到第几个千年,0-1000第一个,1001-2000第二个,2001-3000第三个)
test=# select extract (millennium from timestamp '2017-07-31 22:18:00');
date_part
-----------
3
(1 row)
5. quarter (季度)
test=# select extract (quarter from timestamp '2017-07-31 22:18:00');
date_part
-----------
3
(1 row)
6. month (月份)
test=# select extract (month from timestamp '2017-07-31 22:18:00');
date_part
-----------
7
(1 row)
test=# select extract (month from interval '2 years 11 months');
date_part
-----------
11
(1 row)
7. week (返回当前是几年的第几个周)
test=# select extract (week from timestamp '2017-07-31 22:18:00');
date_part
-----------
31
(1 row)
8. dow (返回当前日期是周几,周日:0,周一:1,周二:2,...)
test=# select extract (dow from timestamp '2017-07-31 22:18:00');
date_part
-----------
1
(1 row)
9. day (本月的第几天)
test=# select extract (day from timestamp '2017-07-31 22:18:00');
date_part
-----------
31
(1 row)
10. doy (本年的第几天)
test=# select extract (doy from timestamp '2017-07-31 22:18:00');
date_part
-----------
212
(1 row)
11. hour (小时)
test=# select extract (hour from timestamp '2017-07-31 22:18:00');
date_part
-----------
22
(1 row)
12. min (得到时间中的分钟)
test=# select extract (min from timestamp '2017-07-31 22:18:00');
date_part
-----------
18
(1 row)
13. sec (返回时间中的秒)
test=# select extract (sec from timestamp '2017-07-31 22:18:00');
date_part
-----------
0
(1 row)
---------------------
extract (field from source)
extract函数是从日期或者时间数值里面抽取子域,比如年、月、日等。source必须是timestamp、time、interval类型的值表达式。field是一个标识符或字符串,是从源数据中的抽取的域。
1. century (世纪)
test=# select extract (century from timestamp '2017-07-31 22:18:00');
date_part
-----------
21
(1 row)
2. year (年)
test=# select extract (year from timestamp '2017-07-31 22:18:00');
date_part
-----------
2017
(1 row)
3. decade (得到年份除10的值)
test=# select extract (decade from timestamp '2017-07-31 22:18:00');
date_part
-----------
201
(1 row)
4. millennium(得到第几个千年,0-1000第一个,1001-2000第二个,2001-3000第三个)
test=# select extract (millennium from timestamp '2017-07-31 22:18:00');
date_part
-----------
3
(1 row)
5. quarter (季度)
test=# select extract (quarter from timestamp '2017-07-31 22:18:00');
date_part
-----------
3
(1 row)
6. month (月份)
test=# select extract (month from timestamp '2017-07-31 22:18:00');
date_part
-----------
7
(1 row)
test=# select extract (month from interval '2 years 11 months');
date_part
-----------
11
(1 row)
7. week (返回当前是几年的第几个周)
test=# select extract (week from timestamp '2017-07-31 22:18:00');
date_part
-----------
31
(1 row)
8. dow (返回当前日期是周几,周日:0,周一:1,周二:2,...)
test=# select extract (dow from timestamp '2017-07-31 22:18:00');
date_part
-----------
1
(1 row)
9. day (本月的第几天)
test=# select extract (day from timestamp '2017-07-31 22:18:00');
date_part
-----------
31
(1 row)
10. doy (本年的第几天)
test=# select extract (doy from timestamp '2017-07-31 22:18:00');
date_part
-----------
212
(1 row)
11. hour (小时)
test=# select extract (hour from timestamp '2017-07-31 22:18:00');
date_part
-----------
22
(1 row)
12. min (得到时间中的分钟)
test=# select extract (min from timestamp '2017-07-31 22:18:00');
date_part
-----------
18
(1 row)
13. sec (返回时间中的秒)
test=# select extract (sec from timestamp '2017-07-31 22:18:00');
date_part
-----------
0
(1 row)
---------------------
整理自:
https://blog.csdn.net/ctypyb2002/article/details/77865677
https://blog.csdn.net/nextaction/article/details/76473613
https://blog.csdn.net/nextaction/article/details/76473613