google-bigquery - 大查询 : Cast float64 to date in SQL Standard

Question

我想将 float64 转换为日期/日期时间？

我有一个内容为 1.483653436E9 的字段，如何将其转换为日期？

谢谢

Answer 1

试试下面的例子

#legacySQL
SELECT 
  ts_original, 
  ts_as_float, 
  USEC_TO_TIMESTAMP(CAST(ts_as_float AS INTEGER)) AS ts,
  DATE(USEC_TO_TIMESTAMP(CAST(ts_as_float AS INTEGER))) AS dt
FROM (SELECT CURRENT_TIMESTAMP() AS ts_original, 
      CAST(TIMESTAMP_TO_USEC(CURRENT_TIMESTAMP()) AS FLOAT) AS ts_as_float)

对于标准 sql(最初在问题标题中忽略了这一点):

#standardSQL
WITH test AS (
  SELECT CURRENT_TIMESTAMP() AS ts_original, 
  CAST(UNIX_MICROS(CURRENT_TIMESTAMP()) AS FLOAT64) AS ts_as_float
)
SELECT 
  ts_original, 
  ts_as_float, 
  TIMESTAMP_MICROS(CAST(ts_as_float AS INT64)) AS ts,
  DATE(TIMESTAMP_MICROS(CAST(ts_as_float AS INT64))) AS dt
FROM test