1

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.

Leo Jones
  • 165
  • 1
  • 12
  • 1
    Don't develop new stuff with PostgreSQL 8.0 – Laurenz Albe Sep 24 '18 at 06:43
  • 1
    Please don't use this version! – S-Man Sep 24 '18 at 07:08
  • 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 Sep 24 '18 at 23:07
  • 1
    Possible duplicate of [Using sql function generate\_series() in redshift](https://stackoverflow.com/questions/17282276/using-sql-function-generate-series-in-redshift) – S-Man Sep 25 '18 at 06:22
  • http://www.silota.com/docs/recipes/redshift-sequential-generate-series-numbers-time.html – 42n4 Mar 29 '21 at 22:00

2 Answers2

0

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;
krithikaGopalakrishnan
  • 1,217
  • 10
  • 28
0

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

S-Man
  • 22,521
  • 7
  • 40
  • 63
  • @LeoJones Does this work for you? `generate_series` should work for integer ranges (even if not for date ranges) IIRC. – S-Man Sep 25 '18 at 04:28
  • Thanks, S-Man. I got the query to work but could not create a table from it in Redshift (see Edit). – Leo Jones Sep 25 '18 at 20:28
  • @LeoJones Please describe what you tried to create the table. – S-Man Sep 26 '18 at 04:17
  • @LeoJones It would be better to create a second question on Stackoverflow since it is not allowed to ask different questions in one thread. Please upvote/accept this on if it solves your origin problem and post a new question (maybe referring to this one) – S-Man Sep 26 '18 at 04:28