Skip site navigation (1) Skip section navigation (2)

Re: current_date / datetime stuff

From: Jon Sime <jsime(at)mediamatters(dot)org>
To: pgsql-novice(at)postgresql(dot)org
Cc: Joshua <joshua(at)joshuaneil(dot)com>
Subject: Re: current_date / datetime stuff
Date: 2007-06-04 16:09:04
Message-ID: 46643920.3050600@mediamatters.org (view raw or flat)
Thread:
Lists: pgsql-novice
Joshua wrote:
> Hello,
> 
> I was hoping someone here may be able to help me out with this one:
> 
> Is there anything similiar to:     SELECT current_date;
> that will return the date of the first Monday of the month?

You might try the following query, or a variation thereof (replacing the 
"now()"s in the subquery with an arbitrary date if you need the first 
monday of months other than the current one):

select cast(case
     when d.dow > 1 then d.m + (8 - d.dow) * interval '1 day'
     when d.dow < 1 then d.m + 1 * interval '1 day'
     else d.m end as date) as first_monday
from (select extract(dow from date_trunc('month', now())) as dow,
     date_trunc('month', now()) as m) d;

-Jon

-- 
Senior Systems Developer
Media Matters for America
http://mediamatters.org/

In response to

pgsql-novice by date

Next:From: NetzachDate: 2007-06-04 16:23:00
Subject: Re: current_date / datetime stuff
Previous:From: JoshuaDate: 2007-06-04 15:05:19
Subject: current_date / datetime stuff

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group