Generate Interval from Variable

There are numerous ways of generating intervals 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.