Generating timestamps using the GENERATE_DATE_ARRAY function in BigQuery

Recently for work I had a need to generate a series of timestamps for a range of dates in Google Cloud BigQuery.

My use case was made considerably easier by the creation of a "calendar" table, except instead of dates I had to create a record for every hour of the day, for each day, within a specific range of dates. Standard SQL in BigQuery has a useful function called GENERATE_DATE_ARRAY() for generating dates between a start and end date with a given frequency, but the minimal value for the interval is one day. There is nothing for creating times. In the future I would hope that Google will add a function to BigQuery by creating a GENERATE_TIME_ARRAY() or GENERATE_TIMESTAMP_ARRAY() or something similar.

Below is a solution to my use case. The query makes use of GENERATE_DATE_ARRAY() and creates the timestamp by creating each hour of the day for each day in the array. This creates a result set that has three columns:
one for the dates, one for the times, and one for the combined timestamp, which is then joined against
other tables in my database.

WITH shifts AS (  
 SELECT
   [STRUCT(" 00:00:00 UTC" as hrs, GENERATE_DATE_ARRAY('2014-01-15','2017-09-30', INTERVAL 1 DAY) as dt_range),
    STRUCT(" 01:00:00 UTC" as hrs, GENERATE_DATE_ARRAY('2014-01-15','2017-09-30', INTERVAL 1 DAY) as dt_range),
    STRUCT(" 02:00:00 UTC" as hrs, GENERATE_DATE_ARRAY('2014-01-15','2017-09-30', INTERVAL 1 DAY) as dt_range),
    STRUCT(" 03:00:00 UTC" as hrs, GENERATE_DATE_ARRAY('2014-01-15','2017-09-30', INTERVAL 1 DAY) as dt_range),
    STRUCT(" 04:00:00 UTC" as hrs, GENERATE_DATE_ARRAY('2014-01-15','2017-09-30', INTERVAL 1 DAY) as dt_range),
    STRUCT(" 05:00:00 UTC" as hrs, GENERATE_DATE_ARRAY('2014-01-15','2017-09-30', INTERVAL 1 DAY) as dt_range),
    STRUCT(" 06:00:00 UTC" as hrs, GENERATE_DATE_ARRAY('2014-01-15','2017-09-30', INTERVAL 1 DAY) as dt_range),
    STRUCT(" 07:00:00 UTC" as hrs, GENERATE_DATE_ARRAY('2014-01-15','2017-09-30', INTERVAL 1 DAY) as dt_range),
    STRUCT(" 08:00:00 UTC" as hrs, GENERATE_DATE_ARRAY('2014-01-15','2017-09-30', INTERVAL 1 DAY) as dt_range),
    STRUCT(" 09:00:00 UTC" as hrs, GENERATE_DATE_ARRAY('2014-01-15','2017-09-30', INTERVAL 1 DAY) as dt_range),
    STRUCT(" 10:00:00 UTC" as hrs, GENERATE_DATE_ARRAY('2014-01-15','2017-09-30', INTERVAL 1 DAY) as dt_range),
    STRUCT(" 11:00:00 UTC" as hrs, GENERATE_DATE_ARRAY('2014-01-15','2017-09-30', INTERVAL 1 DAY) as dt_range),
    STRUCT(" 12:00:00 UTC" as hrs, GENERATE_DATE_ARRAY('2014-01-15','2017-09-30', INTERVAL 1 DAY) as dt_range),
    STRUCT(" 13:00:00 UTC" as hrs, GENERATE_DATE_ARRAY('2014-01-15','2017-09-30', INTERVAL 1 DAY) as dt_range),
    STRUCT(" 14:00:00 UTC" as hrs, GENERATE_DATE_ARRAY('2014-01-15','2017-09-30', INTERVAL 1 DAY) as dt_range),
    STRUCT(" 15:00:00 UTC" as hrs, GENERATE_DATE_ARRAY('2014-01-15','2017-09-30', INTERVAL 1 DAY) as dt_range),
    STRUCT(" 16:00:00 UTC" as hrs, GENERATE_DATE_ARRAY('2014-01-15','2017-09-30', INTERVAL 1 DAY) as dt_range),
    STRUCT(" 17:00:00 UTC" as hrs, GENERATE_DATE_ARRAY('2014-01-15','2017-09-30', INTERVAL 1 DAY) as dt_range),
    STRUCT(" 18:00:00 UTC" as hrs, GENERATE_DATE_ARRAY('2014-01-15','2017-09-30', INTERVAL 1 DAY) as dt_range),
    STRUCT(" 19:00:00 UTC" as hrs, GENERATE_DATE_ARRAY('2014-01-15','2017-09-30', INTERVAL 1 DAY) as dt_range),
    STRUCT(" 20:00:00 UTC" as hrs, GENERATE_DATE_ARRAY('2014-01-15','2017-09-30', INTERVAL 1 DAY) as dt_range),
    STRUCT(" 21:00:00 UTC" as hrs, GENERATE_DATE_ARRAY('2014-01-15','2017-09-30', INTERVAL 1 DAY) as dt_range),
    STRUCT(" 22:00:00 UTC" as hrs, GENERATE_DATE_ARRAY('2014-01-15','2017-09-30', INTERVAL 1 DAY) as dt_range),
    STRUCT(" 23:00:00 UTC" as hrs, GENERATE_DATE_ARRAY('2014-01-15','2017-09-30', INTERVAL 1 DAY) as dt_range)]
    AS full_timestamps
  )
  SELECT
    dt AS dates,
    hrs,
    CAST(CONCAT( CAST(dt as STRING), CAST(hrs as STRING)) as TIMESTAMP) as shift_value
    FROM shifts, shifts.full_timestamps
  LEFT JOIN full_timestamps.dt_range as dt;

Pitt Fagan

Greetings! I'm passionate about data; specifically the big data and data science ecosystems! It's such an exciting time to be working in these spaces. I run the BigDataMadison meetup where I live.