bigquery join two tables by timestamp and another column

0
votes

I have two tables as below:

TableA: host, timestamp, type
TableB: host, timestamp, type

I would like to join these two tables by host column and timestamp. It returns null because usually there is few seconds gap between timestamps so they don't match. So I want to join them based on host and timestamp(date, hour and minutes only). timestamp column is like this : 2020-11-06 23:33:03.448 UTC

I created below query which is based on hour only, I would like to include day, minutes (and maybe second).

  with tableA as (
      SELECT
      EXTRACT(HOUR from timestamp) as hour,
      ...
  ),
  tableB as (
      SELECT
      EXTRACT(HOUR from timestamp) as hour,
      ...
  )
  SELECT * 
  FROM tableA 
  JOIN tableB 
  USING(host,hour)
1
sqlgoogle-bigquery

1 Answers

1
votes

Use timestamp_trunc():

with tableA as (
      SELECT TIMESTAMP_TRUNC(timestamp, HOUR ) as hour,
             ...
     ),
     tableB as (
      SELECT TIMESTAMP_TRUNC(timestamp, HOUR ) as hour,
             ...
     )
SELECT * 
FROM tableA JOIN
     tableB 
     USING(host, hour);