CODEWITHSUNDEEP
sqlamazon-redshift
Leo Jones
Leo Jones

Reputation: 165

Generate time series with date and hour and create table in Amazon Redshift

I'd like to generate a table of dates and hours in Amazon Redshift. The following query would work in Postgresql 9.1 and above but unfortunately we're limited to Amazon Redshift, which resulted in error: "function generate_series does not exist." Your help in generating time series in 8.02 like the result table would be greatly appreciated.

SELECT * FROM generate_series('2008-03-01 00:00'::timestamp, 
                          '2008-03-04 12:00', '10 hours');
  generate_series   

generate_series   
    ---------------------
     2008-03-01 00:00:00
     2008-03-01 10:00:00
     2008-03-01 20:00:00
     2008-03-02 06:00:00
     2008-03-02 16:00:00
     2008-03-03 02:00:00
     2008-03-03 12:00:00
     2008-03-03 22:00:00
     2008-03-04 08:00:00

Edit: I was able to generate the time series that I wanted with the following code but was unable to create them into a table in Redshift. Error message was:

"Specified types or functions (one per INFO message) not supported on Redshift tables."

SELECT '2017-01-01 00:00:00'::timestamp + x * interval'1 hour' AS Date_Time
FROM generate_series(0, 1000 * 24) AS g(x)

Any ideas how this can be created into a table in Redshift? Thanks.

Upvotes: 1

Views: 1840

Answers (2)

S-Man
S-Man

Reputation: 23676

Disclaimer: Please do not use this version anymore!

Works for Postgres 8.4 (could not check 8.0 but it should work as well)

demo: db<>fiddle

SELECT start_date + gs * interval '10 hours'
FROM (
    SELECT '2008-03-01 00:00:00'::timestamp as start_date, generate_series(1,10, 1) as gs
) s
  1. generate_series from 1 to 10 and join the result against your starting timestamp. The result is a table with two columns: One with the start at every row, one with the numbers from 1 to 10.
  2. now you can multiply the generated number with your interval and add this to the start.

Edit: The problem is discussed already

Upvotes: 0

krithikaGopalakrishnan
krithikaGopalakrishnan

Reputation: 1335

A script like this might help you,

CREATE or REPLACE function gen_ser() returns integer
as 
$body$
declare
query varchar;
i integer := 0;
begin
loop 
exit when i >= 100;
i:=i+10;
query :='Insert into table select ''01-01-2018''::timestamptz + '''|| i ||' hours'' ::interval;';
execute query;
end loop;
return 1;
end;
$body$
language plpgsql;

Upvotes: 0

Related Questions