当前位置:   article > 正文

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

sql 时间戳转字符

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;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
-- 字符串转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
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
-- 生成当天小时整点的时间戳,字符串转日期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
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/花生_TL007/article/detail/406346
推荐阅读
相关标签
  

闽ICP备14008679号