In-Reply-To: <F7C976F3-68DB-4D96-B992-875F5F40757D@seespotcode.net>
References: <46642A05.40708@joshuaneil.com>
	<20070604152736.GS32320@phlogiston.dyndns.org>
	<F7C976F3-68DB-4D96-B992-875F5F40757D@seespotcode.net>
Mime-Version: 1.0 (Apple Message framework v752.2)
Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed
Message-Id: <7B00D29F-089A-4A19-B9C1-0FF8A949DDA3@seespotcode.net>
Cc: Andrew Sullivan <ajs@crankycanuck.ca>,
 pgsql-sql@postgresql.org
Content-Transfer-Encoding: 7bit
From: Michael Glaesemann <grzm@seespotcode.net>
Subject: Re: current_date / datetime stuff
Date: Mon, 4 Jun 2007 11:39:21 -0500
To: Michael Glaesemann <grzm@seespotcode.net>


On Jun 4, 2007, at 10:59 , Michael Glaesemann wrote:

>
> On Jun 4, 2007, at 10:27 , Andrew Sullivan wrote:
>
>> On Mon, Jun 04, 2007 at 10:04:37AM -0500, Joshua wrote:
>>> that will return the date of the first Monday of the month?
>>
>> I guess you need to write a function to do this.  I suppose you could
>> do it by finding out what day of the week it is and what the date is,
>> then counting backwards to the earliest possible Monday.
>
> As Andrew said, there's no built-in function to do this, but it's  
> easy enough to write one. Here's a rough example (very lightly  
> tested and probably overly complicated)

And a little simpler:

CREATE OR REPLACE FUNCTION first_dow_of_month(DATE, INTEGER)
RETURNS DATE
IMMUTABLE
LANGUAGE SQL AS $_$
SELECT v_first_day_of_month + ( 7 + $2 - v_day_of_week) % 7
        AS first_dow_of_month
FROM (
      SELECT v_first_day_of_month
             , extract('dow' from v_first_day_of_month)::integer
                             AS v_day_of_week
      FROM (SELECT date_trunc('month', $1)::date)
           AS mon(v_first_day_of_month)) as calc;
$_$;

CREATE OR REPLACE FUNCTION first_monday(DATE)
RETURNS DATE
IMMUTABLE
LANGUAGE SQL AS $_$
SELECT first_dow_of_month($1, 1);
$_$;

Michael Glaesemann
grzm seespotcode net