Postgresql – removing dates from generate_Series in postgreSQL

postgresqlpostgresql-12

im trying to remove some dates from generate_Series in postgres liie this:-

select min(dt)+'1 day' from generate_series(date_trunc('day',now()) , date_trunc('day',now()+'30 day'),'1 day') as s(dt)
except
SELECT dt
FROM generate_series(date_trunc('day',ca."startAt"), date_trunc('day',ca."startAt"), '1 day') as ex1(dt))

but seems like min()+1day is always the same value no matter what startAt is

Best Answer

You have to calculate the EXCEPT in a subquery, before you aggregate:

SELECT min(dt) + '1 day'
FROM (SELECT dt
      FROM generate_series(
              date_trunc('day',now()),
              date_trunc('day',now()+'30 day'),
              '1 day') AS s(dt)
      EXCEPT
      SELECT dt
      FROM generate_series(
              date_trunc('day',ca."startAt"),
              date_trunc('day',ca."startAt"),
              '1 day') AS ex1(dt)
     ) AS q;