数据日期’${data_dt}’
格式如:‘20230112’
上日
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’（即中国时区),以秒为单位文章来源：https://www.toymoban.com/news/detail-595252.html
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.将以秒为单位的时间转为中国时区文章来源地址https://www.toymoban.com/news/detail-595252.html
to_timestamp(1662374040) at time zone 'prc' as timestamp
到了这里，关于PostgreSQL常见日期转换的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！
