Generate interval from variable in Presto

Question

In Presto SQL, unlike regular SQL, intervals must be created with inverted commas:

INTERVAL '1' DAY

rather than

INTERVAL 1 DAY

I am trying to generate a set of dates as described here: https://stackoverflow.com/a/2157776/2388930, but am encountering an issue with

INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY

How might this be achieved? I tried

parse((a.a + (10 * b.a) + (100 * c.a)) as varchar)

but this was not successful.

score 41 · Answer 1 · edited May 23 '17 at 12:16

This is not a direct answer to the question, but if the goal is to replicate the results described in the linked Stack Overflow question, generate days from date range, here is an alternative method to generate a sequence of dates in Presto:

SELECT
    CAST(date_column AS DATE) date_column
FROM
    (VALUES
        (SEQUENCE(FROM_ISO8601_DATE('2010-01-20'), 
                  FROM_ISO8601_DATE('2010-01-24'), 
                  INTERVAL '1' DAY)
        )
    ) AS t1(date_array)
CROSS JOIN
    UNNEST(date_array) AS t2(date_column)
;

Output:

 date_column
-------------
 2010-01-20
 2010-01-21
 2010-01-22
 2010-01-23
 2010-01-24

You can also use other INTERVAL values besides DAY and different step sizes besides '1'.

*Adapted from this issue comment, https://github.com/prestodb/presto/issues/2169#issuecomment-68521569.

`sequence` method only supports elements upto 10000. This error is returned when limit is exceeded: "result of sequence function must not have more than 10000 entries" — W Anjum, Sep 01 '20 at 12:55

score 10 · Answer 2 · answered Dec 01 '15 at 00:20

10

I ended up using date_add:

date_add('day', -(a.a + (10 * b.a) + (100 * c.a)), date_trunc('day', now()))

answered Dec 01 '15 at 00:20

Dimpl

935
1
10
24

score 0 · Answer 3 · answered Dec 01 '22 at 00:10

I ended up using a simpler version of @wingr's answer the cross join and values parts seemed redundant:

SELECT
    *
FROM UNNEST(
        SEQUENCE(
            FROM_ISO8601_DATE('2010-01-20'),
            FROM_ISO8601_DATE('2010-01-24'),
            INTERVAL '1' DAY
        )
    )
 AS t1(date_array)

Generate interval from variable in Presto

3 Answers3