Postgresql日期时间统计

日期函数：date_trunc

-- 按小时统计
SELECT count(*), DATE_TRUNC('hour', 日期::timestamp)as d FROM 表 GROUP BY d ORDER BY d;
--按天统计
SELECT count(*), DATE_TRUNC('day', 日期::timestamp)as d FROM 表 GROUP BY d ORDER BY d;
-- 按周统计
SELECT count(*), DATE_TRUNC('week', 日期::timestamp)as d FROM 表 GROUP BY d ORDER BY d;
-- 按月统计
SELECT count(*), DATE_TRUNC('month', 日期::timestamp)as d FROM 表 GROUP BY d ORDER BY d;

-- 统计每天7点半
SELECT (date_trunc('day', send_time::TIMESTAMP) + interval  '7 h 30 min') as date FROM


 
-- 日期加减
SELECT date_part('day','2022-04-13'::TIMESTAMP - '2022-01-13'::TIMESTAMP) AS data_days,date_part('day','2022-06-10'::TIMESTAMP - (CURRENT_DATE)::TIMESTAMP) AS data1_days
SELECT CURRENT_DATE -5 as days