PostgreSQL常用时间函数与时间计算提取示例说明

常用函数与常量

select current_date,current_time,current_timestamp,now(),localtime,localtimestamp,timeofday();

select age('2030-04-10', '2000-06-13');

第1个参数减第2个参数，age返回值是interval，很方便算年龄

-- 时间戳转字符串再截取
SELECT now()::timestamp,substring(''||now()::timestamp from 1 for 19);

to_timestamp(字符串转时间戳、数字转时间戳)

select to_timestamp(1859351600),to_timestamp('1859351600'::bigint),to_timestamp('2090 01 12', 'YYYY MM DD');

想要了解YYYY MM DD代表什么，可以参考后文：日期时间格式化字符串

date与to_date(字符串转日期、时间戳转日期)

select to_date('2090 01 12', 'YYYY MM DD'),date(now()),date('2090-09-28');

在一些喜欢用字符串存时间的计算时非常有用，虽然不推荐在数据库上做计算，但是有时候有些特殊的项目，需要存储过程，这些函数能极大的简化计算。

例如，有些每日都要和前一日比(这里简化了只计算了日期)：

select priceday,to_char(to_date(priceday, 'YYYYMMDD')-interval '1 day','YYYYmmdd') as lastDay from elt_data_table;

interval(时间计算)

interval还是非常有用，不仅仅可以用来表示时间差，还可以用来做时间计算。

基本操作与格式

加不加s不影响，interval '2 day’和interval '2 days’都可以。

select now(),now() - interval '1 day' as subday,
now() + interval '1 week' as addweek,now() - interval '1 month' as submonth,now()+interval '1 year' as addyear;
 
--加1年1月1天1时1分1秒
select now() + interval '1 year 1 month 1 day 1 hour 1 min 1 sec';

混合运算

interval还可以执行乘除运算：

select interval '1 day' + interval '1 hour' as addmod,
interval '1 day' - interval '1 hour' as submod,
interval '1 hour' * double precision '3.5' as mulmod,
interval '1 hour' / double precision '1.5' as divmod,
current_time - interval '2 hours' as sub2h,
current_date - interval '3 days' as sub3d,
current_timestamp + interval '2 years' as add2y;

to_char(各种时间转字符串)

这里我们只看to_char转日期时间的部分

第2个参数都是格式化字符串，关于日期时间部分可以参考后文：日期时间格式化字符串

select
to_char(current_timestamp,'YYYY/mm/dd HH24:MI:SS'),
to_char(current_date,'YYYYmmdd'),
to_char(interval '15h 2m 12s', 'HH24:MI:SS');

extract(提取时间字段，年月日时分秒，周、季度，第几周、第几天)

基本说明

select current_date,
extract(year from current_date) as dyear,
extract(month from current_date) as dmonth,
extract(day from current_date) as dday,
-- 时分秒这种只能从包含时间的数据取，例如timestamp
extract(hour from current_timestamp) as dhour,
extract(minute from current_timestamp) as dminute,
extract(second from current_timestamp) as dsecond,
extract(week from current_date) as dweek,
extract(quarter from current_date) as dquarter;

select current_timestamp as dtime,extract(epoch from current_timestamp) as depoch;

-- 计算年龄
SELECT extract(year from age(TIMESTAMP '2030-07-08',TIMESTAMP '2000-07-07'));
-- 计算相差月份
select extract(year from age(TIMESTAMP '2030-11-05',TIMESTAMP '2010-02-04')) * 12 + extract(MONTH from age(TIMESTAMP '2030-11-05',TIMESTAMP '2010-02-04'));

常用时间字段

不区分大小写

其他时间字段

date_part(类似extract)

基本和extract等价

select 
date_part('year',current_date) as dyear,
date_part('month',now()) as dmonth,
date_part('day',now()) as dday,
date_part('week',now()) as dweek,
date_part('quarter',now()) as dquarter;

date_trunc(时间截断，计算特殊时间，一年第1天，一个季度第1天，一周最后1天等)

主要用于计算一些特殊时间，

select date_trunc('year',now()) as 当年第1天,
date_trunc('year',now() + interval '1 year') - interval '1 day' as 当年最后1天,
date_trunc('month',now()) as 当月第1天,
date_trunc('month',now() + interval '1 month') - interval '1 day' as 当月最后1天,
date_trunc('quarter',now()) as 当季度第1天,
date_trunc('quarter',now() + interval '3 month') - interval '1 day' as 当季度最后1天,
date_trunc('week',now()) as 当周第1天,
date_trunc('week',now() + interval '1 week') - interval '1 day' as 当周最后1天;

综合应用-时间计算

时间差

-- 59只取分钟部分
select date_part('minute',cast('2050-01-01 14:00:00' as TIMESTAMP)-cast('2050-01-01 13:00:10' as TIMESTAMP));


select round(date_part('epoch', TIMESTAMP '2050-05-05 14:00:00' - TIMESTAMP '2050-05-05 13:10:10')) as dsec,
round(date_part('epoch', TIMESTAMP '2050-05-05 14:00:00' - TIMESTAMP '2050-05-05 13:10:10')/60) as dmin,
round(date_part('epoch', TIMESTAMP '2050-05-05 14:00:00' - TIMESTAMP '2050-05-05 13:10:10')/3600) as dhour; 

select date('2050-01-10') - date('2050-01-01') as dday; 

时间加减

select current_timestamp,current_timestamp + interval '1 year',
current_timestamp + interval '1 month',
current_timestamp + interval '1 day',
current_timestamp + interval '1 hour',
current_timestamp + interval '1 min',
current_timestamp + interval '1 sec';

select now() + interval '1 year 1 month 1 day 1 hour 1 min 1 sec' as r;

select now() + (2 || ' day')::interval as r;

-- date类型：2050-01-17
select date '2050-01-10' + integer '7' as r;
-- date类型：2050-01-10
select date '2050-01-10' + interval '1 hour' as r;

select date '2050-01-01' + time '06:00' as r;

-- timestamp类型：2050-01-01 06:00:00.000
select timestamp '2050-01-01' + time '06:00' as r;


-- 返回interval类型:1 day -01:00:00
select interval '1 day' - interval '1 hour' as r1,
interval '1 hour' * double precision '3.5' as r2,
interval '1 hour' / double precision '1.5' as r3,
timestamp '2050-01-01 23:00' - interval '23 hours' as r4,
date '2050-10-01' - interval '1 hour' as r5;

日期时间格式化字符串

常用格式化字符

其他格式化字符