pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Osvaldo Rosario Kussama <osvaldo_kussama@yahoo.com.br>
To: Kristo Kaiv <kristo.kaiv@skype.net>
To: pgsql-sql@postgresql.org
Subject: Re: current_date / datetime stuff
Date: Wed, 06 Jun 2007 12:54:52 -0300
Message-ID: <4666D8CC.40801@yahoo.com.br> (raw)
In-Reply-To: <EFC1C078-4624-4E97-85C8-EB5C3EDAE6FB@skype.net>
References: <46642A05.40708@joshuaneil.com>
	<20070604152736.GS32320@phlogiston.dyndns.org>
	<F7C976F3-68DB-4D96-B992-875F5F40757D@seespotcode.net>
	<7B00D29F-089A-4A19-B9C1-0FF8A949DDA3@seespotcode.net>
	<EFC1C078-4624-4E97-85C8-EB5C3EDAE6FB@skype.net>

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



view thread (10+ messages)  latest in thread

Message-ID: <4666D8CC.40801@yahoo.com.br>
Permalink:  ../4666D8CC.40801@yahoo.com.br/
Also on:    postgresql.org/message-id/4666D8CC.40801@yahoo.com.br

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: osvaldo_kussama@yahoo.com.br, kristo.kaiv@skype.net
  Subject: Re: current_date / datetime stuff
  In-Reply-To: <4666D8CC.40801@yahoo.com.br>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox