Brief: I need to create a table that is a list of timestamps for all seconds a year in both directions from today, in Redshift.

Conditions:

• I cannot use generate_series as I need to write the results to a table. generate_series does not allow me to write to a table as it is a leader node only and unsupported in Redshift.
• I do not have a list of timestamps in another table to reference.
• Exporting results and importing to a new table is not feasible due to file size. I specifically need a self-contained solution.

I would prefer, but do not need:

• A function that allows me to do what generate_series does without the current limitations. Not sure this is possible and unsure how to write this since I don't think Redshift really allows for loops in the way I want, but hoping I'm wrong.

Example desired output:

2021-09-13 00:01:04.000000
2021-09-13 00:01:03.000000
2021-09-13 00:01:02.000000
2021-09-13 00:01:01.000000
2021-09-13 00:01:00.000000

Is there a way to do this with some sort of for loop or n 1 type of solution? I'm more than willing to do this in Epoch time and then convert to timestamp type like above, I just need a viable solution that will allow me to write to a table. Code that generated the example output is below:

with interval_1_second_cte as (
    SELECT CURRENT_DATE::TIMESTAMP - (i * interval '1 seconds') as interval_1_second
    FROM generate_series(1, (365 * 24 * 60 * 60)) i
    UNION ALL
    SELECT CURRENT_DATE::TIMESTAMP   (i * interval '1 seconds') as interval_1_second
    FROM generate_series(1, (365 * 24 * 60 * 60)) i
)

select top 5 i1sc.interval_1_second
from interval_1_second_cte i1sc
where interval_1_second like '2021-09-13 00:01:0%'
order by 1;

CodePudding user response：

I don't understand what "in both directions" is supposed to mean other than a list of all seconds in the current year.

This can be achieved using a recursive common table expression:

with recursive this_year as (
    SELECT date_trunc('year', current_timestamp) as ts
    UNION ALL
    SELECT p.ts   interval '1 second'
    from this_year p
    where p.ts < date_trunc('year', current_timestamp)   interval '1 year'
)
select *
from this_year