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

1
votes

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.

2
sqlamazon-redshift
Don't develop new stuff with PostgreSQL 8.0 – Laurenz Albe
Please don't use this version! – S-Man
Sorry, I forgot to mention we're using Amazon Redshift which does not support generate_series to create a table. PostgreSQL 8.0.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.2 20041017 (Red Hat 3.4.2-6.fc3), Redshift 1.0.3906. – Leo Jones
Possible duplicate of Using sql function generate_series() in redshift – S-Man
silota.com/docs/recipes/… – 42n4

2 Answers

0
votes

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;
0
votes

Disclaimer: Please do not use this version anymore!

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