Generate every date / hour / minute / second between two timestamps / dates.

generate_every_x_between_two_timestamps.sql

-- Amazon Redshift
-- CALL generate_ts_every_x_between_two_timestamps(<start_ts> , <end_ts>, <steps>, <step_size>);
-- SELECT * FROM tmp_generated_time_series;
-- parameter details:
-- <step_size> : type of duration i.e. DAY, HOUR, MINUTE, SECOND etc
-- <step> : number of unit steps to take for generating next ts in the timeseries.
-- <start_ts>: start timestamp or date.
-- <end_ts>: end timestamp or date.

------------------------------------------------------------------------------
-- CODE 
CREATE OR REPLACE PROCEDURE generate_ts_every_x_between_two_timestamps(start TIMESTAMP, stop TIMESTAMP, step FLOAT, step_scale TEXT)
AS
$$
DECLARE
  interval_sql TEXT;
  interval_r   RECORD;
BEGIN
  DROP TABLE IF EXISTS tmp_generated_time_series;
  interval_sql := 'SELECT INTERVAL ' || QUOTE_LITERAL('1 ' || step_scale) || 'as step_interval';
  EXECUTE interval_sql INTO interval_r;
  CREATE TEMP TABLE tmp_generated_time_series AS
  WITH
    RECURSIVE
    t_ts(ts) AS (
      SELECT
        start AS ts
      UNION ALL
      SELECT
        p.ts + step * interval_r.step_interval AS xts
      FROM
        t_ts p
      WHERE xts <= stop
    )
  SELECT *
  FROM
    t_ts;
END;
$$ LANGUAGE plpgsql;

-- EXAMPLE USAGE

-- generate every minute between two timestamps.
CALL generate_ts_every_x_between_two_timestamps('2020-01-01 00:00:00' , '2020-01-01 01:00:00', 2, 'minute');
SELECT * FROM tmp_generated_time_series ORDER BY 1;

-- generate every hour between two timestamps
CALL generate_ts_every_x_between_two_timestamps('2020-01-01 00:00:00' , '2020-01-02 00:00:00', 1, 'hour');
SELECT * FROM tmp_generated_time_series ORDER BY 1;
  
-- generate every day between two timestamps
CALL generate_ts_every_x_between_two_timestamps('2020-01-01 00:00:00' , '2020-01-30 00:00:00', 1, 'day');
SELECT * FROM tmp_generated_time_series ORDER BY 1;