AWS Athena中Timestamp转小数格式Epoch时间的方法

在Athena中可以通过以下两种方式实现将timestamp转换为带小数的Epoch时间，且结果可直接用于GROUP BY子句：

方法1：使用to_unixtime结合类型转换 #

to_unixtime返回的科学计数法本质是浮点数的显示形式，通过将其转换为指定精度的decimal类型，即可得到你需要的小数格式：

select count(*) over () as data_count, 
       cast(to_unixtime(ts) as decimal(18,2)) as ts_sec 
from device_data 
where ts >= timestamp '2022-09-30 18:30:00.000000' 
  and ts <= timestamp '2022-10-31 18:30:00.000000'
  and device_id = 760 
  and id in (1,2,3,4,5)
group by ts_sec 
order by ts_sec desc 
limit 1

这里decimal(18,2)指定了总位数18、小数位2，如果你需要保留更高精度（比如3位毫秒），可以调整为decimal(18,3)。

方法2：手动计算秒数+毫秒部分 #

通过分别计算秒级时间差和毫秒余数，再合并得到带小数的Epoch时间：

select count(*) over () as data_count, 
       date_diff('second', timestamp '1970-01-01 00:00:00', ts) 
       + (date_diff('millisecond', timestamp '1970-01-01 00:00:00', ts) % 1000) / 1000.0 as ts_sec 
from device_data 
where ts >= timestamp '2022-09-30 18:30:00.000000' 
  and ts <= timestamp '2022-10-31 18:30:00.000000'
  and device_id = 760 
  and id in (1,2,3,4,5)
group by ts_sec 
order by ts_sec desc 
limit 1

这种方式通过整数运算+浮点除法，同样能得到精确的小数格式Epoch时间，且结果为数值类型，支持GROUP BY操作。

注意：Athena中timestamp类型的比较建议显式将字符串转换为timestamp类型（如示例中的timestamp '2022-09-30 18:30:00.000000'），避免隐式转换可能带来的问题。

内容的提问来源于stack exchange，提问作者Yatin Pandit