CODEWITHSUNDEEP
sqlfunctionamazon-redshift
nick_willard
nick_willard

Reputation: 3

Create list of timestamps by second in Redshift

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 would prefer, but do not need:

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;

Upvotes: 0

Views: 238

Answers (1)

user330315
user330315

Reputation:

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

Upvotes: 1

Related Questions