sql - inserting dates into a table between a start and end date in Presto


Keywords:sql 


Question: 

I want to create a table with all the dates between a start date and end date. What's the best way to do this without listing every date out in the insert?

for example: start date is 1/1/2018 and end date is 1/4/2018 then the resulting table should be:

date
------
1/1/2018
1/2/2018
1/3/2018
1/4/2018

2 Answers: 

Have you considered using something like:

INSERT INTO your_table 
SELECT ... 
FROM ... 
WHERE date_column BETWEEN DATE '2001-01-01' AND DATE '2018-04-01'

Please see the following bits from documentation:

 

You can use sequence(date, date) to generate the dates in a range:

presto:default> create table dates as
select x from unnest(sequence(date '2018-01-01', date '2018-01-04')) t(x);
...
presto:default> select * from dates;
     x
------------
 2018-01-01
 2018-01-02
 2018-01-03
 2018-01-04
(4 rows)

Note: sequence works with dates since Presto version 0.197.