如何生成按小时递增的Datetime时间戳数组？GENERATE_DATE_ARRAY不支持时分粒度

Great question! Since GENERATE_DATE_ARRAY only handles day/week/month granularity and can't do hourly intervals, here are a few straightforward solutions that work perfectly in BigQuery (I'm assuming that's your platform given the function reference):

1. Use GENERATE_TIMESTAMP_ARRAY (the simplest modern approach) #

BigQuery has a dedicated function for exactly this use case: GENERATE_TIMESTAMP_ARRAY. It supports intra-day intervals like 1 hour, so you can get your desired array in one line:

SELECT GENERATE_TIMESTAMP_ARRAY(
  TIMESTAMP('2018-01-01 10:00:00'),
  TIMESTAMP('2018-01-01 12:00:00'),
  INTERVAL 1 HOUR
) AS hourly_timestamps;

This will directly output the array you showed: [2018-01-01 10:00:00 UTC, 2018-01-01 11:00:00 UTC, 2018-01-01 12:00:00 UTC] (note the UTC suffix is standard for BigQuery timestamps, but you can format it if needed).

2. Combine GENERATE_DATE_ARRAY with hour offsets (compatible with older environments) #

If you can't use GENERATE_TIMESTAMP_ARRAY for some reason, you can pair the date array with a range of hours, then build timestamps from the combinations:

WITH date_range AS (
  -- Generate the base date(s) you need
  SELECT date FROM UNNEST(GENERATE_DATE_ARRAY('2018-01-01', '2018-01-01')) date
),
hour_range AS (
  -- Generate the specific hours you want (adjust the range as needed)
  SELECT hour FROM UNNEST(GENERATE_ARRAY(10, 12)) hour
)
SELECT 
  ARRAY_AGG(
    TIMESTAMP(CONCAT(date, ' ', FORMAT('%02d', hour), ':00:00')) 
    ORDER BY hour
  ) AS hourly_timestamps
FROM date_range, hour_range;

This works well if you need to cover multiple days too—just adjust the date_range to span more dates and set hour_range to GENERATE_ARRAY(0,23) for full days, then filter if needed.

3. Calculate offsets with GENERATE_ARRAY (dynamic range solution) #

Another approach is to calculate the total number of hours between your start and end timestamps, generate an array of offsets, then add each offset to the start time:

DECLARE start_ts TIMESTAMP DEFAULT '2018-01-01 10:00:00';
DECLARE end_ts TIMESTAMP DEFAULT '2018-01-01 12:00:00';

SELECT 
  ARRAY_AGG(
    TIMESTAMP_ADD(start_ts, INTERVAL offset HOUR)
    ORDER BY offset
  ) AS hourly_timestamps
FROM UNNEST(GENERATE_ARRAY(0, TIMESTAMP_DIFF(end_ts, start_ts, HOUR))) offset;

This is great for dynamic ranges where you don't want to hardcode dates or hours—just set your start and end timestamps, and it handles the rest automatically.

All three methods will produce the hourly timestamp array you're looking for. The first method is by far the cleanest if your BigQuery environment supports it.

内容的提问来源于stack exchange，提问作者Tarlen