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

Re: current_date / datetime stuff

From: Netzach <psql-novice(at)netzach(dot)co(dot)il>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: current_date / datetime stuff
Date: 2007-06-04 16:23:00
Message-ID: Pine.LNX.4.58.0706041913170.30078@perpetual.homelinux.org (view raw or flat)
Thread:
Lists: pgsql-novice
> Is there anything similiar to:     SELECT current_date;
> that will return the date of the first Monday of the month?

In the following examples, replace <month> and <year> with the values
that you want.

Try this:

SELECT ( 1 -
	extract(dow from ('1/<month>/<year>')::date)::integer
	 + 8
	) % 7

This will give you a number from 1 to 7 indicating the day of the month
which is the first Monday from the month of the specified date. The '1'
represents Monday. Replace accordingly if you want a different day.

If you want the full date, you will need to concatenate the day and
month:

SELECT ( 1 -
	extract(dow from ('1/<month>/<year>')::date)::integer
	 + 8
	) % 7
	|| '/' || '<month>' || '/' || '<year>';

And finally, if you want an answer for the 'current' month, then try
this monster:

SELECT ( 1 - firstday::integer + 8 ) % 7  || '/' || month || '/' || year
FROM (
	SELECT extract(dow from ('1/'||month||'/'||year)::date) AS firstday,
		month,year
	FROM (
		SELECT extract(month from current_date) AS month,
		extract(year from current_date) AS year
	) AS date_parts
) AS date_parts;


NOTE: Americans may wish to swap the order of day/month. Or not :)

Netzach

Responses

pgsql-novice by date

Next:From: David GardnerDate: 2007-06-04 16:32:33
Subject: Re: Retreving count of rows returned by a join query
Previous:From: Jon SimeDate: 2007-06-04 16:09:04
Subject: Re: current_date / datetime stuff

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