BigQuery: convert epoch to TIMESTAMP

16
votes

I'm trying to range-join two tables, like so

SELECT *
FROM main_table h
INNER JOIN
    test.delay_pairs d
ON
    d.interval_start_time_utc < h.visitStartTime
    AND h.visitStartTime < d.interval_end_time_utc

where h.visitStartTime is an INT64 epoch and d.interval_start_time_utc and d.interval_end_time_utc are proper TIMESTAMPs.

The above fails with

No matching signature for operator < for argument types: TIMESTAMP, INT64. Supported signature: ANY < ANY

Neither wrapping h.visitStartTime in TIMESTAMP() nor CAST(d.interval_start_time_utc AS INT64) work. How do I make the two comparable in BigQuery's Standard SQL dialect?

2
google-bigquerytimestampepoch

2 Answers

30
votes

You can use timestamp conversion functions like TIMESTAMP_SECONDS, TIMESTAMP_MILLIS, TIMESTAMP_MICROS

for example, assuming your h.visitStartTime is microseconds since the unix epoch

SELECT *
FROM main_table h
INNER JOIN test.delay_pairs d
ON d.interval_start_time_utc < TIMESTAMP_MICROS(h.visitStartTime)
AND TIMESTAMP_MICROS(h.visitStartTime) < d.interval_end_time_utc
10
votes

With standard sql you can use one of those, depending on precision:

  • DATE_FROM_UNIX_DATE - from days epoch to date
  • TIMESTAMP_SECONDS - from seconds epoch to timestamp
  • TIMESTAMP_MILLIS - from milliseconds epoch to timestamp
  • TIMESTAMP_MICROS - from microseconds epoch to timestamp

See documentation here: https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators#timestamp_seconds

With legacy sql you can just use TIMESTAMP function and multiply or divide by 1000 to bring it to needed epoch type:

SELECT 
  TIMESTAMP(epoch_in_millis / 1000) AS datetime
FROM 
  my_table