Re: current_date / datetime stuff
От | Osvaldo Rosario Kussama |
---|---|
Тема | Re: current_date / datetime stuff |
Дата | |
Msg-id | 4666D8CC.40801@yahoo.com.br обсуждение исходный текст |
Ответ на | Re: current_date / datetime stuff (Kristo Kaiv <kristo.kaiv@skype.net>) |
Ответы |
Re: current_date / datetime stuff
|
Список | pgsql-sql |
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:002007-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:002007-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:002007-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:002007-12-03 00:00:00 (12 registros) []s Osvaldo
В списке pgsql-sql по дате отправления: