postgres 日期字符串转换及月份补0处理

目录

引言

需求分析

数据处理

结束

---

引言

        工作中经常涉及到对时间类型的各种处理和转换，现记录一下pg中如何进行补0操作。涉及函数LPAD、CAST、POSITION、SUBSTR、SPLIT_PART

需求分析

        现库中存在一张停车记录表，需统计最新一天各类型车辆停车记录数，并返回实际出入时间（天）。数据中包含每辆车出入闸机时间，为varchar类型的时间字符串。源数据如下所示，现需将时间类型转换为YYYY-MM-DD格式。

表结构

i_id	主键
cpys	车牌颜色（车辆类型）
crcsj	出入车时间（出入闸机时间）
crfx	出入方向
tclx	停车类型
s_createtime	数据入库时间

       

数据处理

 1.按类型统计最新一天各类型车辆停车记录数。结果如下

-- 通过进入计算 只进不出算1次  同一辆车多次进入进几次算几次
select count(i_id) cls,cpys  from resafety_jtzt.a_mx_cgj_hs_cg_tcjl 
where S_CREATETIME=(select max(S_CREATETIME) from resafety_jtzt.a_mx_cgj_hs_cg_tcjl)
and crfx='入车'
group by cpys

2.出入时间处理。通过substr和position函数截取年月日部分

select count(i_id) cls,cpys,substr(crcsj,1,position(' ' in crcsj)-1) as sj  from resafety_jtzt.a_mx_cgj_hs_cg_tcjl 
where S_CREATETIME=(select max(S_CREATETIME) from resafety_jtzt.a_mx_cgj_hs_cg_tcjl)
and crfx='入车'
group by cpys,substr(crcsj,1,position(' ' in crcsj)-1)

3. 通过SPLIT_PART函数通过斜杠拆分字符串 

-- split_part(字符串,'拆分字符',返回拆分后地几个下标的字符串 从1开始)
-- SPLIT_PART('5/14/23', '/', 3) 返回23
-- SPLIT_PART('5/14/23', '/', 2) 返回14
-- SPLIT_PART('5/14/23', '/', 1) 返回5

SPLIT_PART(substr(crcsj,1,position(' ' in crcsj)-1), '/', 3)     

4.月份和天补0处理

日处理：
    lpad(CAST(SPLIT_PART(sj, '/', 2) AS character varying), 2, '0')
月处理：
    lpad(CAST(SPLIT_PART(sj, '/', 1) AS character varying), 2, '0')
年处理：
    '20'||SPLIT_PART(sj, '/', 3)



日期处理：YYYY||'-'||MM||'-'||DD

select *,
'20'||SPLIT_PART(sj, '/', 3)
||'-'||
lpad(CAST(SPLIT_PART(sj, '/', 1) AS character varying), 2, '0')
||'-'||
lpad(CAST(SPLIT_PART(sj, '/', 2) AS character varying), 2, '0')
 as sj2
from (
	select count(i_id) cls,cpys,substr(crcsj,1,position(' ' in crcsj)-1) as sj  from resafety_jtzt.a_mx_cgj_hs_cg_tcjl 
	where S_CREATETIME=(select max(S_CREATETIME) from resafety_jtzt.a_mx_cgj_hs_cg_tcjl)
	and crfx='入车'
	group by cpys,substr(crcsj,1,position(' ' in crcsj)-1)
)t1
order by cls desc

结束

        到此针对此类特殊日期格式处理就到此结束了，本文只是提供了一种方案，你还可以通过TO_CHAR (date_value, output_format)方式处理时间戳等。因需而已，这里不做展开，后续如有需要另行补充。