First Saturday and Last Saturday of a month

"Brian C. Doyle" wrote:

Hello all,

I have a Function that gives me the fsunday of a week Based on the date you
give it the select looks like
SELECT Date('now') - timespan( text( date_part( 'dow', Date('now') ) ) ||
'days' )
and if you run that today you will get
?column?
------------------------
2001-08-05 00:00:00-04
(1 row)

Which is what I want it to do ...

Now I need to setup a function that will give me the first Saturday of the
month and then one to get the Last Saturday of the month.

It's too late to hack code, but:

For the first Saturday problem assign

select date_part('dow', 'yyyy-mm-01'::DATE);

to a variable x. Here yyy and mm is the year and month you are interested in. The day you want is 'yyyy-mm-01' + [(6-x) days].

Getting all the conversions right is left as an exercise for the reader :-)

Last Saturday problem is similar to above except for some sign reversals, but more interesting as you need the number of days in the month. I think you'll have to calculate that (for February) the hard way :-P You *do* know the algorithm for testing if year yyyy is a leap year, don't you? ((yyyy%4) && !(yyyy%400))

Allan.

Allan Engelhardt <allane@cybaea.com> writes:

Last Saturday problem is similar to above except for some sign
reversals, but more interesting as you need the number of days in the
month.

I'd be inclined to take the first day of the *next* month (relatively
easy to figure), and then back up to a Saturday using the 'dow' value
for that day.

regards, tom lane

Tom Lane wrote:

Allan Engelhardt <allane@cybaea.com> writes:

Last Saturday problem is similar to above except for some sign
reversals, but more interesting as you need the number of days in the
month.

I'd be inclined to take the first day of the *next* month (relatively
easy to figure), and then back up to a Saturday using the 'dow' value
for that day.

I *knew* there had to be a better way, even if I couldn't think of it late last night :-)

Thanks, Tom.

From: "Allan Engelhardt" <allane@cybaea.com>
To: "Brian C. Doyle" <bcdoyle@mindspring.com>;
<pgsql-general@postgresql.org>
Sent: Wednesday, August 08, 2001 11:18 PM
Subject: [GENERAL] Re: First Saturday and Last Saturday of a month

You *do* know the algorithm for testing if year yyyy is a leap year, don't

you?

((yyyy%4) && !(yyyy%400))

ITYM ((yyyy%4) && ( !(yyyy%400) || (yyyy%1000) ) - year 2000 was a leap
year, even though it was a multiple of 400, because it was a millenium.

Alex
--
Alex Page, IT Department, Solid State Logic
E-Mail: alex.page@solid-state-logic.com
Phone: +44 (0) 1865 842 300
Web: http://www.solid-state-logic.com

**********************************************************************
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the system manager.

This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.

www.mimesweeper.com
**********************************************************************

Alex Page writes:

From: "Allan Engelhardt" <allane@cybaea.com>
To: "Brian C. Doyle" <bcdoyle@mindspring.com>;
<pgsql-general@postgresql.org>
Sent: Wednesday, August 08, 2001 11:18 PM
Subject: [GENERAL] Re: First Saturday and Last Saturday of a month

You *do* know the algorithm for testing if year yyyy is a leap year, don't

you?

((yyyy%4) && !(yyyy%400))

ITYM ((yyyy%4) && ( !(yyyy%400) || (yyyy%1000) ) - year 2000 was a leap
year, even though it was a multiple of 400, because it was a millenium.

Actually it's

y % 4 = 0 and (y % 100 <> 0 or y % 400 = 0)

(SQL pedants would use mod(y,4) etc. instead.)

--
Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter