用日期和月份更新表

Question

有一个表dates_calendar:

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

我需要更新这个表并插入日期，直到下个月从表中的最后一个日期开始计数。例如，最后一个日期是2016-10-24 00:00:00 -我需要插入日期直到2016-10-31。之后(最后一个日期现在是2016-10-31)下一个语句调用应该插入日期直到2016-11-30，等等。

我的SQL代码的例子，但它一直插入30天。

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'
      )
    );

我正在使用PostgreSQL。同样，去掉最后日期的重复SELECT语句也很好。

Answer 1

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;

Answer 2

要计算您需要插入的第一个和最后一个日期，您可以使用以下查询:

select max(date) + interval '1' day as first_day, 
       date_trunc('month', max(date) + interval '1' month) - interval '1' day as last_day
from dates_calendar

表达式date_trunc('month', max(date) + interval '1' month)计算下一个月的开始日期。减去一天就是那个月的最后一天。

这可以用来生成日期列表:

with from_to (first_day, last_day) as (
  select max(date) + interval '1' day, 
         date_trunc('month', max(date) + interval '1' month) - interval '1' day
  from dates_calendar
)
select dt
from generate_series(  (select first_day from from_to), (select last_day from from_to), interval '1' day) as t(dt);

最后，这可以用来插入生成的行到表中:

with from_to (first_day, last_day) as (
  select max(date) + interval '1' day, 
         date_trunc('month', max(date) + interval '1' month) - interval '1' day
  from dates_calendar
)
insert into dates_calendar (date)
select dt
from generate_series(  (select first_day from from_to), (select last_day from from_to), interval '1' day) as t(dt);

Answer 3

with max_date (d) as (select max(date)::date from dates_calendar)
insert into dates_calendar (date)
select d
from generate_series (
    (select d from max_date) + 1,
    (select date_trunc('month', d + interval '1 month')::date - 1 from max_date),
    '1 day'
) g(d)