PostgreSQL常见日期转换

数据日期’${data_dt}’
格式如:‘20230112’

上日

sql">to_date('${data_dt}','yyyymmdd') -1

本月初

date(date_trunc('month',to_date('${data_dt}','yyyymmdd')))

本月末

date(date_trunc('month',to_date('${data_dt}','yyyymmdd')) + interval'1 months' -interval'1 day')

上月末

date(date_trunc('month',to_date('${data_dt}','yyyymmdd'))-interval'1 day')
date(date_trunc('month',to_date('${data_dt}','yyyymmdd'))) -1

上季末

date(date_trunc('quarter',to_date('${data_dt}','yyyymmdd'))-interval'1 day')

上年末

date(date_trunc('year',to_date('${data_dt}','yyyymmdd'))-interval'1 day')

去年同期

date(to_date('${data_dt}','yyyymmdd') - interval'12 MONTH')
date(to_date('${data_dt}','yyyymmdd') - interval'1 YEAR')

当年已过天数

(to_date('${data_dt}','yyyymmdd')-(date(date_trunc('year',to_date('${data_dt}','yyyymmdd')))-1))

EXTRACT日期转换
取月份 EXTRACT 的结果为duoble 类型无法参与运算，可以使用cast将 EXTRACT 的结果转为INT类型

cast(EXTRACT(MONTH FROM date'${data_dt}') as int)

当月第几天

extract(day from to_date('${data_dt}','yyyymmdd'))

当年第几周

extract(week from to_date('${data_dt}','yyyymmdd'))

当年第几月

extract(month from to_date('${data_dt}','yyyymmdd'))

本周第一天

date(date_trunc('week',to_date('${data_dt}','yyyymmdd'))) as week_one_day

本周末

date(date_trunc('week',to_date('${data_dt}','yyyymmdd'))+interval '1 week' - interval'1 day') as week_last_day
-- 2
date(date_trunc('week',to_date('${data_dt}','yyyymmdd'))+interval'6 day') as week_last_day

本周第几天

--1
cast when cast(extract(dow from to_date('${data_dt}','yyyymmdd')) as int) = 0 then 7
     else cast(extract(dow from to_date('${data_dt}','yyyymmdd')) as int)
	 end
	 as day_of_week
--2
to_date('${data_dt}','yyyymmdd') - date(date_trunc('week',to_date('${data_dt}','yyyymmdd'))) +1

时间戳(年月日时分秒)与时间戳(秒单位)
1.将文本格式数据转为时间戳

to_timestamp('2022-09-05 18:34:00', 'YYYY-MM-DD HH24:MI:SS')
to_timestamp(concat_ws(' ',to_char(data_date,'yyyy-mm-dd'),to_char(data_time,'hh24:mi:ss')),'yyyy-mm-dd hh24:mi:ss')

2.将时间戳转换为Unix时间戳（以秒为单位），可以使用extract函数和epoch

extract(epoch FROM to_timestamp('2022-09-05 18:34:00', 'YYYY-MM-DD HH24:MI:SS'))

3.将时间戳转换为 ‘prc’（即中国时区),以秒为单位

extract(epoch from cast('2022-09-05 18:34:00' as timestamp)) - 28000
to_timestamp(extract(epoch FROM cast('2022-09-05 18:34:00' as timestamp)) - 28000)
cast(extract(epoch from cast(concat_ws(' ',to_char(data_date,'yyyy-mm-dd'),to_char(data_time,'hh24:mi:ss')) as timestamp)) - 28800 as varchar ) as data_tm

4.将以秒为单位的时间转为中国时区

to_timestamp(1662374040) at time zone 'prc' as timestamp