PrestoDB: select all dates between two dates

Question

I need to form a report which provides some information per each date within dates interval.

I need to have it within a single query (can't create any functions or supporting tables).

How can I achieve that in PrestoDB?

Note: There are lots of vendor specific solution here, here and even here. But none of them satisfies my need as they either don't work in Presto or use tables/functions.

To be more precise here is an example of query:

WITH ( query to select all dates between 2017.01.01 and 2018.01.01 ) AS dates
SELECT 
  date     date, 
  count(*) number_of_orders
FROM dates dates
LEFT JOIN order order
  ON order.created_at = dates.date

Can you provide some example SQL to show what you are trying to do? — Ike Walker, Jul 18 '18 at 20:09

score 13 · Accepted Answer · edited Sep 15 '21 at 03:36

13

You can use the Presto SEQUENCE() function to generate a sequence of days as an array, and then use UNNEST to explode that array as a result set.

Something like this should work for you:

SELECT date_array AS DAY
FROM UNNEST(
      SEQUENCE(
        cast('2017-01-01' AS date), 
        cast('2018-01-01' AS date), 
        INTERVAL '1' DAY
      ) 
    ) AS t1(date_array)

edited Sep 15 '21 at 03:36

falsePockets

3,826
4
18
37

answered Jul 19 '18 at 15:01

Ike Walker

64,401
14
110
109

Really helpful! – Ogochi Aug 29 '19 at 14:36
What's the purpose of the CROSS JOIN? Is there really any joining going on here? – falsePockets Sep 10 '21 at 02:36

PrestoDB: select all dates between two dates

1 Answers1