Generating a Date Series in Presto

SNIPPETS • DATABASE • PRESTO | • 1 MINUTE READ

I always forget all of the syntax for generating a series in Presto. The ability to generate a series is super useful for calculating metrics like user engagement over a period of time with distinct start and end dates.

select cast(date_column as date) as end_date
     , cast(date_column - interval '28' day as date) as begin_date
  from
   (VALUES
        (sequence(
                  date_trunc('week', current_date) - interval '35' day,
                  date_trunc('week', current_date),
                  INTERVAL '7' day))
    ) as t1(date_array)
cross join
    unnest(date_array) AS t2(date_column)

Running this query will create this table:

| end_date   | begin_date |
|------------|------------|
| 2021-02-15 | 2021-01-18 |
| 2021-02-22 | 2021-01-25 |
| 2021-03-01 | 2021-02-01 |
| 2021-03-08 | 2021-02-08 |
| 2021-03-15 | 2021-02-15 |
| 2021-03-22 | 2021-02-22 |