BigQuery: counting entries between 30-minute timestamp intervals

Question

I have a pre-created table data:

id      timestamp_entry
1       "2023-01-01 04:11:24 UTC"
2       "2023-01-01 04:14:55 UTC"
...
99999   "2023-01-31 23:45:59 UTC"

where timestamp_entry has a uniform time zone "UTC" and ranges within January 2023.

I want to create a 30-minute time skeleton and count how many entries in timestamp_entry falls into each interval.

I first created a subquery:

WITH
intervals AS(
  SELECT interval AS start_time,
         TIMESTAMP_SUB(TIMESTAMP_ADD(interval, INTERVAL 30 MINUTE), INTERVAL 1 SECOND) AS end_time
  FROM UNNEST(GENERATE_TIMESTAMP_ARRAY("2023-01-01 00:00:00 UTC", "2023-01-31 23:59:59 UTC", INTERVAL 30 MINUTE)) interval
)

But ideally, I want my outcome to show:

start_time                  end_time                    count
"2023-01-01 00:00:00 UTC"   "2023-01-01 00:29:59 UTC"   0
"2023-01-01 00:30:00 UTC"   "2023-01-01 00:59:59 UTC"   0
...
"2023-01-31 23:00:00 UTC"   "2023-01-31 23:29:59 UTC"   12
"2023-01-31 23:30:00 UTC"   "2023-01-31 23:59:59 UTC"   5

where count shows how many timestamp_entry from data falls into each interval.

I have tried using RIGHT JOIN with BETWEEN, but I won't be able to join the two tables as there are no exact "matches".

Any insights are appreciated.

score 0 · Answer 1 · answered Mar 09 '23 at 13:18

0

Works exactly with JOIN and BETWEEN:

SELECT start_time, end_time, COUNT(*) AS count
  FROM data
  LEFT JOIN intervals
    ON timestamp_entry >= start_time
   AND timestamp_entry <  end_time
 GROUP BY start_time, end_time
 ORDER BY start_time

answered Mar 09 '23 at 13:18

jstaxlin

517
4
18

score 0 · Accepted Answer · answered Mar 09 '23 at 14:16

You can consider below as well

WITH data AS (
  -- put your data here
)
SELECT TIMESTAMP_SECONDS(slot) start_time,
       TIMESTAMP_SECONDS(slot + 1800 - 1) end_time,
       COUNT(timestamp_entry) `count`
  FROM UNNEST(GENERATE_ARRAY(1672531200, 1675207800, 1800)) slot
  LEFT JOIN data ON DIV(slot, 1800) = DIV(UNIX_SECONDS(timestamp_entry), 1800)
 GROUP BY 1, 2;

1672531200 -> UNIX_SECONDS("2023-01-01 00:00:00 UTC");
1675207800 -> UNIX_SECONDS("2023-01-31 23:30:00 UTC");

BigQuery: counting entries between 30-minute timestamp intervals

2 Answers2