There may be several reasons why you want to create a list of dates or timestamps. The primary use case is to left join a dataset that has missing dates onto this full list of dates so you can clearly show where no data is reported.
Postgres has a handy generate_series function, but we need to be more creative with other flavors of SQL. Check out this blog post to see how to generate a list of dates when you don't have the option to use Postgres's generate_series!
Now, let's say you want to generate a list of timestamps, separated by 1 second instead. The approach is very similar to that in the post, check out the snippet below!
Note: The below query is in Redshift syntax
with numbers as ( select row_number() over(order by true) as n from -- use any table that has ample rows users ) select getdate() - n * interval '1 second' from numbers
Please sign in to leave a comment.