Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 8E03C9FBA3A for ; Wed, 6 Jun 2007 12:44:22 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 10355-03 for ; Wed, 6 Jun 2007 12:44:18 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from smtp110.mail.mud.yahoo.com (smtp110.mail.mud.yahoo.com [209.191.85.220]) by postgresql.org (Postfix) with SMTP id 4CACF9FB5AB for ; Wed, 6 Jun 2007 12:44:18 -0300 (ADT) Received: (qmail 58120 invoked from network); 6 Jun 2007 15:44:17 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com.br; h=Received:X-YMail-OSG:Message-ID:Date:From:User-Agent:MIME-Version:To:Subject:References:In-Reply-To:Content-Type:Content-Transfer-Encoding; b=TPh3FGxwNEqybwYrBiliTPtl12zV1KbsnRIkrtuVdPjnBDsx3/Cx6AuJhksYuh1kgrbTLrIkS5Ebh3f9hroo/OQ3vspdlfbohc/V9B6Sai086bRg+nkTzfelByklNH8bU+kWHBInAhjQA3pbgw7GVA+AHK2tckR7EpY3Blbm+9Y= ; Received: from unknown (HELO ?10.2.200.151?) (osvaldo_kussama@201.51.166.134 with plain) by smtp110.mail.mud.yahoo.com with SMTP; 6 Jun 2007 15:44:16 -0000 X-YMail-OSG: J8gof2IVM1mMeliYC9DVrUa3Gpn1fJBCOBTSxG9zR1dJ1SLnQe_2UQNLmP7A6ooYoqN1FEmHmw-- Message-ID: <4666D8CC.40801@yahoo.com.br> Date: Wed, 06 Jun 2007 12:54:52 -0300 From: Osvaldo Rosario Kussama User-Agent: Thunderbird 1.5.0.10 (X11/20070301) MIME-Version: 1.0 To: Kristo Kaiv , pgsql-sql@postgresql.org Subject: Re: current_date / datetime stuff References: <46642A05.40708@joshuaneil.com> <20070604152736.GS32320@phlogiston.dyndns.org> <7B00D29F-089A-4A19-B9C1-0FF8A949DDA3@seespotcode.net> In-Reply-To: Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.201 tagged_above=0 required=5 tests=BAYES_50=0.001, DNS_FROM_RFC_ABUSE=0.2 X-Spam-Level: X-Archive-Number: 200706/56 X-Sequence-Number: 28546 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