From: Kristo Kaiv <kristo.kaiv@skype.net>
To: Michael Glaesemann <grzm@seespotcode.net>
Cc: Andrew Sullivan <ajs@crankycanuck.ca>
Cc: pgsql-sql@postgresql.org
Subject: Re: current_date / datetime stuff
Date: Tue, 5 Jun 2007 00:08:28 +0300
Message-ID: <EFC1C078-4624-4E97-85C8-EB5C3EDAE6FB@skype.net> (raw)
In-Reply-To: <7B00D29F-089A-4A19-B9C1-0FF8A949DDA3@seespotcode.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>
oneliner:
select date_trunc('month',now()) + ((8 - extract('dow' from date_trunc
('month',now()))||'days')::text)::interval;
Kristo
On 04.06.2007, at 19:39, Michael Glaesemann wrote:
>
> 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
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
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: kristo.kaiv@skype.net, grzm@seespotcode.net, ajs@crankycanuck.ca
Subject: Re: current_date / datetime stuff
In-Reply-To: <EFC1C078-4624-4E97-85C8-EB5C3EDAE6FB@skype.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