pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: John McKown <jmckown@prodigy.net>
To: Brian C. Doyle <bcdoyle@mindspring.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Week of the Year?
Date: Sun, 3 Sep 2000 13:07:32 -0500 (CDT)
Message-ID: <Pine.LNX.4.21.0009030823500.6400-100000@linux2.johnmckown.net> (raw)
In-Reply-To: <4.3.2.7.2.20000903072517.00c90b00@pop>

On Sun, 3 Sep 2000, Brian C. Doyle wrote:

> John,
> 
> Would you have any clue how to figure out the first saturday of any month - 
> 6 days and the last saturday of that month?
> 
> I know that this seems odd but i have to run reports for "Non Standard 
> Months" and well I am clueless.
> 
> At 09:55 PM 8/11/00 -0500, you wrote:
> 

Actually, I'm glad you wanted Saturday and not some other day of the week,
it's simplier. I'm hope you don't mind some "tutorial" type language. I
usually do this when I want to try to explain my thought processes (such
as they are). It's not meant to be "talking down" to anybody. So let's
think about this for a second. We know that the first Saturday of the
month must be in the range from the 1st day of the month to the 7th day of
the month. We also know that we can determine the "day of week" by using
the date_part("dow",datevar). The "day of week" function returns a number
from 0 (for Sunday) to 6 (for Saturday). My first thought was to create a
table containing dates. It would have the 1st through the 7th of every
month, along with the corresonding day of the week. I could then do
something like:

select datevar from dates where weekday='Saturday' and
date_part('month',datevar)=8 /* where 8 is for August */

This works, but it's really a pain since I need a big table containing all
these dates. So I changed my approach. I then thought, OK, I want the
first Saturday after the 1st of the month. So, how many days 
must I add to the 1st of the month it to get to Saturday? Well, if it is
already Saturday (dow==6), then 0. If Friday (dow==5), then 1. And so on.
That's when the light really went on. That's just 

SELECT test_date+(6-date_part('dow',test_date)) AS Saturday FROM
test_table;

Now in the original question is 6 days before the first Saturday of the
month. So just subtract 6 from the above.

The last Saturday of the month is similiar. In fact, the last Saturday of
a month is simply 7 days before the first Saturday of the *next* month. So
if you want the last Saturday of October, then find the first Saturday of
November and subtract 7.

Hope this gets you going.

John
 




view thread (6+ messages)

Message-ID: <Pine.LNX.4.21.0009030823500.6400-100000@linux2.johnmckown.net>
Permalink:  ../Pine.LNX.4.21.0009030823500.6400-100000@linux2.johnmckown.net/
Also on:    postgresql.org/message-id/Pine.LNX.4.21.0009030823500.6400-100000@linux2.johnmckown.net

 · 

reply

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: jmckown@prodigy.net, bcdoyle@mindspring.com
  Subject: Re: Week of the Year?
  In-Reply-To: <Pine.LNX.4.21.0009030823500.6400-100000@linux2.johnmckown.net>
  References: <4.3.2.7.2.20000903072517.00c90b00@pop> <Pine.LNX.4.21.0009030823500.6400-100000@linux2.johnmckown.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