Re: current_date / datetime stuff

true, didn't test it that thoroughly: mod 7 should be bit more beautiful

select date_trunc('month',now()) + ( ((8 - extract('dow' from
date_trunc('month',now()))%7) ||'days')::text)::interval;

On 06.06.2007, at 18:54, Osvaldo Rosario Kussama wrote:

> Kristo Kaiv escreveu:
>> oneliner:
>> select date_trunc('month',now()) + ((8 - extract('dow' from
>> date_trunc('month',now()))||'days')::text)::interval;
>
>
>
> There is a problem when first monday is 1st or 2nd day of month.
>
> bdteste=# SELECT date_trunc('month',meses) + ((8 - extract('dow'
> from date_trunc ('month',meses))||'days')::text)::interval FROM
> (SELECT ('2007-' || s.a || '-15')::timestamp as meses FROM
> generate_series(1,12) as s(a)) AS foo;
> ?column?
> ---------------------
> 2007-01-08 00:00:00
> 2007-02-05 00:00:00
> 2007-03-05 00:00:00
> 2007-04-09 00:00:00
> 2007-05-07 00:00:00
> 2007-06-04 00:00:00
> 2007-07-09 00:00:00
> 2007-08-06 00:00:00
> 2007-09-03 00:00:00
> 2007-10-08 00:00:00
> 2007-11-05 00:00:00
> 2007-12-03 00:00:00
> (12 registros)
>
> Testing this condition we have the correct answer:
> bdteste=# SELECT date_trunc('month',foo) + ((CASE WHEN extract
> ('dow' from date_trunc('month',foo)) > 1 THEN 8 ELSE 1 END) -
> extract('dow' from date_trunc ('month',foo)))*'1 day'::interval
> FROM (SELECT ('2007-' || s.a || '-15')::timestamp as foo FROM
> generate_series(1,12) as s(a)) AS bar;
> ?column?
> ---------------------
> 2007-01-01 00:00:00
> 2007-02-05 00:00:00
> 2007-03-05 00:00:00
> 2007-04-02 00:00:00
> 2007-05-07 00:00:00
> 2007-06-04 00:00:00
> 2007-07-02 00:00:00
> 2007-08-06 00:00:00
> 2007-09-03 00:00:00
> 2007-10-01 00:00:00
> 2007-11-05 00:00:00
> 2007-12-03 00:00:00
> (12 registros)
>
> []s
> Osvaldo