Re: current_date / datetime stuff

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)

CREATE OR REPLACE FUNCTION first_dow(DATE, INTEGER)
RETURNS DATE
IMMUTABLE
LANGUAGE SQL AS $_$
SELECT v_first_day_of_month +       CASE WHEN v_day_of_week <= $2 THEN $2 - v_day_of_week            ELSE 8 -
v_day_of_week      END AS first_day_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($1, 1);
$_$;

select first_monday(current_date);
first_monday
--------------
2007-06-04
(1 row)

select first_monday('2007-04-01');
first_monday
--------------
2007-04-02
(1 row)

Michael Glaesemann
grzm seespotcode net