Yurko Bregey Yurko Bregey - 3 months ago 10
SQL Question

update table with dates with month

There's a table

dates_calendar
:

id | date
-------------------------
13 | 2016-10-23 00:00:00
14 | 2016-10-24 00:00:00


I need to update this table and insert dates until the next month counting from the last date in the table. E.g. last date is
2016-10-24 00:00:00
- I need to insert dates till
2016-10-31
. After that (the last date now is
2016-10-31
) next statement call should insert dates till
2016-11-30
and so on.

Example of my SQL code, but it inserts 30 days all the time.

INSERT INTO dates_calendar (date)
VALUES (
generate_series(
(SELECT date FROM dates_calendar ORDER BY date DESC LIMIT 1) + interval '1 day',
(SELECT date FROM dates_calendar ORDER BY date DESC LIMIT 1) + interval '1 month',
'1 day'
)
);


I'm using PostgreSQL. As well would be fine to get rid of a duplicated SELECT statement of the last date.

Answer
insert into dates_calendar (date)
select dates::date
from (
    select max(date)::date+ 1 next_day, '1day'::interval one_day, '1month'::interval one_month
    from dates_calendar
    ) s,
    generate_series(
        next_day, 
        date_trunc('month', next_day)+ one_month- one_day, 
        one_day) dates;