
Generate Interval from Variable Using PrestoDB
There are numerous ways to generate interval from variable using PrestoDB SQL for different data types.

Dates
Date intervals can be generated for a given date range using a combination of the following not-so-well-known SQL functions:
- SEQUENCE() – generates a sequence of values between a given start and stop range. It generates an ARRAY type. Function syntax is:
sequence(start
, stop
, step
) -> array(timestamp)
Generate a sequence of timestamps from start
to stop
, incrementing by step
. The type of step
can be INTERVAL DAY TO SECOND or INTERVAL YEAR TO MONTH. Using step
can choose the interval we need e.g. INTERVAL ‘1’ DAY
Note that there is an array size upper limit of 10000.
- FROM_ISO8601_DATE() – Parses a ISO 8601 formatted string into a date.
- VALUES() – defines an inline / dummy table. You can use VALUES() anywhere a query can be used e.g. as the FROM clause of a SELECT, an INSERT
- UNNEST() – this function is used to expand an ARRAY into a single column.
Example 1
Here’s an example query showing how to generate dates for the first 2 weeks of June with an interval of 1 day . We create an array with the sequence of dates we need called date_array, then convert the array into a series of values with a join.
SELECT CAST(date_column AS DATE) date_column
FROM (
VALUES (SEQUENCE(FROM_ISO8601_DATE('2020-06-01'),
FROM_ISO8601_DATE('2020-06-14'), INTERVAL '1' DAY) ) ) AS t1(date_array)
CROSS JOIN UNNEST(date_array) AS t2(date_column) ;
date_column
-------------
2020-06-01
2020-06-02
2020-06-03
2020-06-04
2020-06-05
2020-06-06
2020-06-07
2020-06-08
2020-06-09
2020-06-10
2020-06-11
2020-06-12
2020-06-13
2020-06-14
(14 rows)
Example 2
Here’s a slightly different approach this time using the DATE_ADD function. We specify a start date in a table and the result set begins with this given date plus the next n (10 in this example) dates. We’re still using an array but the array from the SEQUENCE function is fed straight to the UNNEST function.
create table table1(start_date date);
insert into table1 values date('2020-10-13');
select DATE_ADD('day',s.n,t.start_date) as date from table1 t cross join UNNEST(SEQUENCE(0,10)) s (n);
date
------------
2020-10-13
2020-10-14
2020-10-15
2020-10-16
2020-10-17
2020-10-18
2020-10-19
2020-10-20
2020-10-21
2020-10-22
2020-10-23
(11 rows)
Integers
Example
Here’s a simple example generating a sequence of integers from 20 to 0 in steps of -2:
SELECT x FROM UNNEST(sequence(20, 0, -2)) t(x);
x
----
20
18
16
14
12
10
8
6
4
2
0
(11 rows)
These examples should help you when trying to generate an interval from a variable.
Related Articles
What is an Open Data Lake in the Cloud?
The Open Data Lake in the cloud is the solution to the massive data problem. Many companies are adopting that architecture because of better price-performance, scale, and non-proprietary architecture.
Data Warehouse Concepts for Beginners
A data warehouse is a relational database that is designed for query and analysis rather than for transaction processing. Check out this article for more information about data warehouses.