PG库日期函数操作

PG库日期函数操作

1.pg查看是否锁表及释放

代码如下（示例）：

--查询是否锁表了
select oid from pg_class where relname='lockTableName';
select pid from pg_locks where relation='上面查出的oid';
-- 如果查询到了结果，表示该表被锁 则需要释放锁定
select pg_cancel_backend(上面查到的pid);

2.日、周、月、年函数

1、格式化日期
select date_trunc(‘day’,CURRENT_TIMESTAMP)
注：day天、week周、month月、year年

2、时间推移
–近一周
select now() - interval ‘1 week’;

–近一月
select now() - interval ‘1 month’;

–近一年
select now() - interval ‘1 year’;

–下一周
select now() + interval ‘1 week’;

–下一月
select now() + interval ‘1 month’;

–下一年
select now() + interval ‘1 year’;

---

3、时间维度划分

– 天划分为小时
select
date_series,
to_char(date_series, ‘yyyyMMddhh24miss’) as date_series_sort,
to_char(date_series, ‘hh24:mi’) as time_series,
(to_char(date_series, ‘hh24:mi’) || ‘~’ || to_char(date_series + interval ‘1 h’, ‘hh24:mi’)) as time_frame
from (select generate_series (date_trunc(‘h’, ‘2021-01-09’::timestamp),date_trunc(‘h’, ‘2021-03-09’::timestamp + interval ‘23 h’),‘1 h’ ) as date_series )tab

– 自定义时间
select * from (select generate_series(date_trunc(‘day’,‘2021-01-09’::date)::date,-- 起始日期
date_trunc(‘day’,‘2021-03-09’::date)::date +‘1 d’::interval,-- 结束日期
‘1 d’::interval
)::date dayid) t where dayid<=date_trunc(‘day’,‘2021-03-09’::date)

– 月划分到天
select * from (select generate_series(date_trunc(‘month’,CURRENT_TIMESTAMP)::date,-- 起始日期
date_trunc(‘month’,CURRENT_TIMESTAMP)::date +‘1 month -1 d’::interval,-- 结束日期
‘1 d’::interval
)::date dayid) t where t.dayid<=date_trunc(‘day’,CURRENT_TIMESTAMP)

– 年划分到天
select dayid::date
from generate_series(date_trunc(‘year’,CURRENT_TIMESTAMP)::date,-- 开始日期
date_trunc(‘year’, CURRENT_TIMESTAMP)::date + ‘1y -1 d’::interval,-- 结束日期
‘1 day’::interval-- step 位移步调 1 months 代表往后推一个月
) dayid where dayid::date<=date_trunc(‘day’,CURRENT_TIMESTAMP)

---

--  当前天划分为24小时
select
date_series,
to_char(date_series, 'yyyyMMddhh24miss') as dayid,
to_char(date_series, 'hh24:mi') as "timeStr",
(to_char(date_series, 'hh24:mi') || '~' || to_char(date_series + interval '1 h', 'hh24:mi')) as time_frame
from (select generate_series (date_trunc('h', CURRENT_TIMESTAMP - interval '23 h'),date_trunc('h', 			    
CURRENT_TIMESTAMP),'1 h' ) as date_series )tab
where date_trunc('day', date_series) = date_trunc('day', CURRENT_TIMESTAMP) 


--  当前天往前推29天，封装一个月的日期
select * from (select generate_series(date_trunc('day',CURRENT_DATE) -'29 d'::interval,
date_trunc('day',CURRENT_DATE),
'1 d'::interval
)::date dayid) t where dayid<=date_trunc('day',CURRENT_DATE)

--  当前时间往前推四周。封装一个月的日期
select dayid || '~' ||
CASE WHEN  date_trunc('day', CURRENT_TIMESTAMP(0)) > date_trunc('day',dayid::TIMESTAMP)+'6 day'::interval 
THEN to_char(date_trunc('day', dayid::TIMESTAMP) +'6 day'::interval,'yyyy-MM-dd') 
ELSE to_char(date_trunc('day', CURRENT_TIMESTAMP(0)),'yyyy-MM-dd') END AS dayid
from (select generate_series(date_trunc('week',CURRENT_DATE)::date -'3 week'::interval,
date_trunc('week',CURRENT_DATE)::date,
'1 week'::interval
)::date dayid) t where dayid<=date_trunc('week','2022-06-15'::date)	


-- 当前月往前推11个月，封装一年的日期
select to_char(dayid::date,'yyyy-MM') as dayid
from generate_series(date_trunc('month',CURRENT_DATE)::date - '11 month'::interval,
date_trunc('month', CURRENT_DATE)::date,
'1 month'::interval
) dayid where dayid::date<=date_trunc('month',CURRENT_DATE)