Monday, January 20, 2014

generating a list of dates with sqlite3



By default there's a recursion limit of 1000.

pragma recursive_triggers = 1;                                                                                                                                 
                                                                                                                                                               
create temp table dates (d text);                                                                                                                              
create temp trigger fill_dates after insert on dates                                                                                                           
begin                                                                                                                                                          
    insert into dates (d)                                                                                                                                      
    select date(new.d, '+1 day') tomorrow                                                                                                                      
    where tomorrow < date(new.d, '+1 year', 'start of year');                                                                                                    
end;                                                                                                                                                           
                                                                                                                                                               
insert into dates (d) select date('now', 'start of year');                                                                                                      
                                                                                                                                                               
select * from dates;                                                                                                                                           

Enjoy.

No comments: