Big Query : Cast float64 to date in SQL Standard

Question

I would like to cast float64 to date/datetime?

I have a field with content 1.483653436E9 how could i convert it to date?

Thanks

Answer 1

try below as an example

#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)

and for standard sql (missed this initially in the title of question):

#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