CODEWITHSUNDEEP
sqlpresto
solo220
solo220

Reputation: 1

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

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

Upvotes: 0

Views: 3073

Answers (2)

Piotr Findeisen
Piotr Findeisen

Reputation: 20730

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.

Upvotes: 4

kokosing
kokosing

Reputation: 5601

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:

Upvotes: 1

Related Questions