Postgresql时间戳long转日期，转字符串,转timestamp，日期转换错误定位及解决

news/2024/9/22 1:15:17

1. 效果图如下

排查问题时遇到个尴尬的问题，程序里计算好的小时开始及结束的时间戳，转换发现有点乱。
遂研究下，终于正确的转换了。

记录下原因： postgresql的 timestamp可以由bigint秒时间戳10位通过 to_timestamp转换出来；

bigint 转 timestamp to_timestamp
string 转 bigint cast
timestamp 转字符串 to_char
‘YYYY-MM-DD HH24:MI:SS’ 年月日24小时制

在这里插入图片描述

2. sql

select 1659351600000,1659355199999,to_char(to_timestamp(1659351600000),'yyyy-mm-dd hh:mm:ss') as msts,
to_char(to_timestamp(1659355199999),'yyyy-mm-dd hh:mm:ss') as mste,
to_char(to_timestamp(1659351600),'yyyy-mm-dd hh:mm:ss') as sts,
to_char(to_timestamp(1659355199),'yyyy-mm-dd hh:mm:ss') as stE,
to_char(to_timestamp(1659351600),'YYYY-MM-DD HH24:MI:SS') as ts24,
to_char(to_timestamp(1659355199),'YYYY-MM-DD HH24:MI:SS') as te24;

-- 字符串转bigint，毫秒转秒，时间戳转timestamp，timestamp转字符串,timestamp转date，char转date
select 1659351600000,1659355199999,
cast('1659351600000' as bigint) as char2bigint,
cast('1659351600000' as bigint)/1000 as s,
to_timestamp(1659355199)::DATE as date,
to_date('2022-08-01 19:59:59.000000', 'yyyy-mm-dd hh24:mi:ss.us' ) as date2,
to_char(to_timestamp(cast('1659351600000' as bigint)/1000),'YYYY-MM-DD HH24:MI:SS') as char2ts24,
to_char(to_timestamp(1659351600000),'yyyy-mm-dd hh:mm:ss') as msts,
to_char(to_timestamp(1659355199999),'yyyy-mm-dd hh:mm:ss') as mste,
to_char(to_timestamp(1659351600),'yyyy-mm-dd hh:mm:ss') as sts,
to_char(to_timestamp(1659355199),'yyyy-mm-dd hh:mm:ss') as stE,
to_char(to_timestamp(1659351600),'YYYY-MM-DD HH24:MI:SS') as ts24,
to_char(to_timestamp(1659355199),'YYYY-MM-DD HH24:MI:SS') as te24,
to_char(to_timestamp(1659355199),'yyyy-mm-dd hh24:mi:ss.us') as te24_2

-- 生成当天小时整点的时间戳，字符串转日期date年月日时分秒，字符串转day年月日，字符串转timestamp，字符串转ms，m
select '2022-08-10 '||hour|| ':00' as dayYMDHMS,('2022-08-10 '||hour|| ':00')::date as date1,
to_date('2022-08-10 '||hour|| ':00', 'yyyy-mm-dd hh24:mi:ss.us' ) as date2,
to_timestamp('2022-08-10 '||hour|| ':00', 'yyyy-mm-dd hh24:mi:ss.us') timestamp1,
floor(extract(epoch from to_timestamp('2022-08-10 '||hour|| ':00', 'yyyy-mm-dd hh24:mi:ss.us'))*1000) ms,
floor(extract(epoch from to_timestamp('2022-08-10 '||hour|| ':00', 'yyyy-mm-dd hh24:mi:ss.us'))) s,
floor(extract(epoch from to_timestamp('2022-08-10 '||hour|| ':00', 'yyyy-mm-dd hh24:mi:ss.us'))*1000)+3600*1000 msE
from (select '0' || generate_series(0,9)||':00' as hour union all select generate_series(10,23)||':00' as hour) t