Generate Series Of Timestamps (1 Second Intervals)

[intapiuser]

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