postgresql关于时间操作_postgresql quarter

最近因为工作种涉及到求同比环比等数据，然后发现pg和mysql对事件的处理还有点不一样，比如pg种不能像mysql一样用quarter函数，因此再次记录一下对于时间处理的一些操作。本文都是单表处理，本地测试的话只需要用此模板，有一个时间类型字段就好

1、常规时间处理

查询当前日期时间

SELECT to_char(CURRENT_DATE, 'yyyyMMdd')
1

查询当前月份数据

select SUBSTRING(to_char(CURRENT_DATE, 'yyyyMMdd'), 1, 6)
1

pg不像mysql可以直接用quarter函数，查看季度如下，感觉from后面的参数只能用char类型的，我用timestamp什么的试了很多次也没效果：

select extract(quarter from 
	substring(to_char(CURRENT_TIMESTAMP, 'yyyyMMdd'), 1, 8)::TIMESTAMP
);
1
2
3

如果要查询某一间隔时间例如上个月，上一年，只需要使用 interval表示间隔，后面用year和month表示单位

-- 上年度时间
SELECT CURRENT_TIMESTAMP-INTERVAL '1' year 
SELECT to_char(CURRENT_TIMESTAMP-INTERVAL '1' year, 'yyyyMM')
-- 上月时间
SELECT CURRENT_TIMESTAMP-INTERVAL '1' month 
1
2
3
4
5

如果要求两个时间的间隔天数，用date_part函数：

-- 求两个时间之间的间隔天数
select date_part('day','2018-02-02 15:49:15'::timestamp-'2018-01-10 10:12:15'::timestamp)
1
2

2、 查询表中某一月，季度数据

对月份求同比环比，那就需要获取本月，上月，去年本月的数据

求本月数据：

-- 查询本月数据
SELECT count(*) from the_key_personnel p
WHERE SUBSTRING(to_char(CURRENT_DATE, 'yyyyMMdd'), 1, 6)=substring(to_char(p.ministry_special_time, 'yyyyMMdd'), 1, 6)
1
2
3

查询上个月数据：

SELECT count(*) from the_key_personnel p
WHERE SUBSTRING(to_char(CURRENT_TIMESTAMP-INTERVAL '1' month, 'yyyyMM'), 1, 6)=substring(to_char(p.ministry_special_time, 'yyyyMMdd'), 1, 6)
1
2

查询上年度本月数据

-- 查询上年度本月数据
SELECT count(*) from the_key_personnel p
WHERE SUBSTRING(to_char(CURRENT_TIMESTAMP-INTERVAL '1' year, 'yyyyMM'), 1, 6)=substring(to_char(p.ministry_special_time, 'yyyyMMdd'), 1, 6)
1
2
3

关于季度查询，查询某一季度数据，例如第一季度，以及本季度，去年同期本季度：

-- 查询第一季度数据
select * from label_info t
where extract(quarter from to_char(t.create_time,'yyyyMMdd')::TIMESTAMP)=1
-- 查询本季度数据
select * from label_info p
where extract(quarter from to_char(p.create_time,'yyyyMMdd')::TIMESTAMP)=extract(quarter from to_char(CURRENT_TIMESTAMP,'yyyyMMdd')::TIMESTAMP)
-- 查询上年度本季度
select * from label_info p
where substring(to_char(p.create_time,'yyyyMMdd'), 1,4)=substring(to_char(CURRENT_TIMESTAMP-INTERVAL '1' year,	'yyyyMMdd'), 1,4)
	and extract(quarter from to_char(p.create_time,'yyyyMMdd')::TIMESTAMP)=extract(quarter from to_char(CURRENT_TIMESTAMP,'yyyyMMdd')::TIMESTAMP)
1
2
3
4
5
6
7
8
9
10

查询上个季度要稍微麻烦一点，因为比如2021年第一季度的上季度就在2020年的第四季度了

-- 	计算上季度总数
select count(*) from(
SELECT a.building_name, a.pcsdm, b.company_name, b.manage_status, t.risk_key, t.create_time, c.taxpayer_id
from building_info a
left join build_company_register_manage_total_compared_list b on a.building_name=b.building_name
left join  (select  a.risk_key, min(a.create_time) as create_time
	from label_info a
	where a.risk_level in('1','2') group by a.risk_key ) t on b.company_name=t.risk_key
left join registerd_company_baseinfo c on b.company_name=c.company_name
where t.risk_key!='' and t.risk_key is not null ) p
where  case when extract(quarter from to_char(CURRENT_TIMESTAMP,'yyyyMMdd')::TIMESTAMP)=1 then
substring(to_char(p.create_time,'yyyyMMdd'), 1,4)=substring(to_char(CURRENT_TIMESTAMP-INTERVAL '1' year,	'yyyyMMdd'), 1,4)
	and extract(quarter from to_char(p.create_time,'yyyyMMdd')::TIMESTAMP)=4 
	else substring(to_char(p.create_time,'yyyyMMdd'), 1,4)=substring(to_char(CURRENT_TIMESTAMP,	'yyyyMMdd'), 1,4)
	and extract(quarter from to_char(p.create_time,'yyyyMMdd')::TIMESTAMP)=extract(quarter from to_char(CURRENT_TIMESTAMP,'yyyyMMdd')::TIMESTAMP)-1 end
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

关于时间的操作记录下让自己日后翻看，如果有写的不好的希望大佬帮忙指出