From: Michael Glaesemann <grzm@seespotcode.net>
To: Andrew Sullivan <ajs@crankycanuck.ca>
Cc: pgsql-sql@postgresql.org
Subject: Re: current_date / datetime stuff
Date: Mon, 4 Jun 2007 10:59:20 -0500
Message-ID: <F7C976F3-68DB-4D96-B992-875F5F40757D@seespotcode.net> (raw)
In-Reply-To: <20070604152736.GS32320@phlogiston.dyndns.org>
References: <46642A05.40708@joshuaneil.com>
<20070604152736.GS32320@phlogiston.dyndns.org>
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
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: grzm@seespotcode.net, ajs@crankycanuck.ca
Subject: Re: current_date / datetime stuff
In-Reply-To: <F7C976F3-68DB-4D96-B992-875F5F40757D@seespotcode.net>
* 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