0

Hope you are fine I am trying to generate a table like this on Redshift using CTEs

Date Hour Minute
2001-12-02 0 0
2001-12-02 0 1
2001-12-02 0 2
2001-12-02 0 3
2001-12-02 0 4
2001-12-02 1 0
2001-12-02 0 1

In order to create a table that will every minute and hour for every day,

I achieved something like this with this query, but only for one day:

WITH test AS (
SELECT trunc(sysdate) as dt, 
       generate_series(0,23,1) as  hour, minutes 
FROM (
  select generate_series(0,59,1) minutes)
)
SELECT * FROM test

It makes sense to have a table function written or something like that. But is not at option at present

I have DATE_TABLE is a table contains all the dates. But when I execute something like this:

WITH test AS (
SELECT (SELECT date_dt from DATE_TABLE LIMIT 10) as dt, generate_series(0,23,1) as  hour, minutes FROM (select generate_series(0,59,1) minutes)
)

SELECT * FROM test

I get an error:

Function "generate_series(integer,integer,integer)" not supported.

Function "generate_series(integer,integer,integer)" not supported.

Tried this:

with recursive cte(N,minutes) as
(
select 0 as N,minutes FROM (select generate_series(0,59,1) minutes)
union all
select cte.N + 1,minutes
from cte
where cte.N <23
)
select * from cte

Didnt work probably because I dont understand it

Do you have any suggestion on how to achieve this? Probably a recursive query?

Phil C.
  • 11
  • 3

3 Answers3

1

As you can see generate_series() can only be used on the leader node and the result cannot (easily) be made into a normal table.

You will need a recursive CTE, as you tried, but without the generate_series().

with recursive cte(minutes) as
(
  select '00:00:00'::time as minutes
  union all
  select minutes + interval '1 minute'
  from cte
  where cte.minutes < '23:59:59'::time
)
select * from cte

I do worry about how you plan on using such a table. Cross joining with a large table will lead to a very big data segment and likely non-performant results. You may need to rethink how you are attaching your query - or not if my assumptions are off base.

Bill Weiner
  • 8,835
  • 2
  • 7
  • 18
0

To generate a date column with a minute interval, you can run the following:

select date::timestamp 
from generate_series(
  '2023-04-01'::date,
  '2023-04-03'::date,
  '1 minute'::interval
) date;

This will then generate a timestamp as below:

enter image description here

Once the output from the above has been stored as a date in the table, then the hour and minute can be extracted once separate columns have been created for each:

select date, hour=extract(hour from date), minute=extract(minute from date) from table;

Please see this answer for further information.

Michael Grogan
  • 973
  • 5
  • 10
  • HI Michael, Hope you are fine. Appreciate your response. For some reason I am getting this error: ```[Amazon](500310) Invalid operation: function generate_series(date, date, interval) does not exist Hint: No function matches the given name and argument types. You may need to add explicit type casts``` – Phil C. Apr 13 '23 at 18:27
  • Hi Phil, is this for the first part of the code, i.e. the series? And are you running PostgreSQL? – Michael Grogan Apr 13 '23 at 18:32
  • Hi Michael, yeah I am getting this error for the first section. This is for Amazon Redshift, I know they are different, but sometimes one solution works for both. – Phil C. Apr 13 '23 at 18:50
0

This worked out

with numbers as
(
SELECT 1,ROW_NUMBER() OVER ()-1 as n FROM DateTable LIMIT 24
)
select DISTINCT date_dt+ (n || ' hours')::interval as timeseries from numbers CROSS JOIN (SELECT date_dt from DateTable  LIMIT 10)
ORDER BY 1
Phil C.
  • 11
  • 3
  • As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Apr 23 '23 at 19:04