PG时间计算

PG数据库，时间计算使用场景总结

日期之差

--**获取秒差**

SELECT round(date_part('epoch', TIMESTAMP '2019-05-05 12:11:20' - TIMESTAMP '2019-05-05 10:10:10'));

--**获取分钟差**

SELECT round(date_part('epoch', TIMESTAMP '2019-05-05 12:11:20' - TIMESTAMP '2019-05-05 10:10:10')/60);

--**获取小时差**

SELECT round(date_part('epoch', TIMESTAMP '2019-05-05 12:11:20' - TIMESTAMP '2019-05-05 10:10:10')/60/60);

--**获取天数差**

SELECT Date('2019-06-05') - Date('2019-05-03');

--**获取月份差**

select extract(year from age(TIMESTAMP '2018-04-05',TIMESTAMP '2017-02-04')) * 12 + extract(MONTH from age(TIMESTAMP '2019-04-05',TIMESTAMP '2017-02-04'));

--**获取年份差**

SELECT extract(year from age(TIMESTAMP '2018-04-05',TIMESTAMP '2017-02-04'));

-- 获取月份

SELECT date_part('month', cast('2023-08-11' as TIMESTAMP) );

--获取星期几1-6，星期天是0

SELECT date_part('dow', cast('2023-08-12' as TIMESTAMP) );

-- 获取年

SELECT date_part('year', cast('2023-08-11' as TIMESTAMP) );

-- 获取日

SELECT date_part('day', cast('2023-08-12' as TIMESTAMP) );