Thread: First Saturday and Last Saturday of a month

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.

I know that sounds odd but it is necessary for pulling information out of a
database.


Thank you all for you help.

Brian

"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
ofdays in the month.  I think you'll have to calculate that (for February) the hard way :-P  You *do* know the
algorithmfor 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