cancel
Showing results for 
Search instead for 
Did you mean: 
intapiuser
Community Team Member
Community Team Member
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
Version history
Last update:
‎03-02-2023 08:57 AM
Updated by:
Contributors
Community Toolbox

Recommended quick links to assist you in optimizing your community experience:

Developers Group:

Product Feedback Forum:

Need additional support?:

Submit a Support Request

The Legal Stuff

Have a question about the Sisense Community?

Email [email protected]

Share this page: