-- 计算当前时间,以下两种方法计算结果一样
SELECT CURRENT_TIMESTAMP, now();
SELECT CURRENT_TIMESTAMP AS d1, now() AS d2;
-- 计算当前时间戳
SELECT floor(extract(epoch from now())*1000) AS last_modified_time; -- 经过在线验证的准确写法
SELECT floor(extract(epoch from now()))*1000 AS last_modified_time; -- 先取整后乘以1000会丢失精度,太粗糙
SELECT extract(epoch from now()) AS last_modified_time; -- 小数点后有5位小数
SELECT extract(epoch from now())*1000 AS last_modified_time; -- 小数点后有两位小数
SELECT floor(extract(epoch from now())*1000) AS last_modified_time; -- 向下取整,无小数
SELECT extract(epoch from now())*1000 AS t1, floor(extract(epoch from now())*1000) AS t2; -- t1有小数,t2无小数
-- 对比在线时间戳转换发现前面这种是准的,后面的这种比当前时间戳大,是错误的计算方法,因为epoch表示距离1970的秒数。
SELECT floor(extract(epoch from now())*1000) AS t1, floor(extract(epoch from ((current_timestamp - timestamp '1970-01-01 00:00:00')*1000))) AS t2;
select (70-20) AS 时间差:测试;
select (1658424309070-1658395509070) AS 时间差:毫秒;
select (1658424309070-1658395509070)/1000/3600 AS 时间差:小时;
-- 10位时间戳的单位是秒,13位时间戳的单位是毫秒
-- 相差8小时
SELECT to_char(to_timestamp(1658424309070/1000), 'yyyy-mm-dd hh24:mi:ss:us') d1, to_char(to_timestamp(1658395509070/1000), 'yyyy-mm-dd hh24:mi:ss:us') d2;
-- 时间戳转换日期格式
SELECT to_char(to_timestamp(1551835379224/1000), 'yyyy-mm-dd hh24:mi:ss:us');
http://tool.aliy7.com