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

Answer 1

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)