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:

• 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.