Thread: Re: Week of the Year?
Sorry,
I don't speak TCL or TK. I've got a book on it, but I haven't had any time
or pressing need to get familiar with it. I'm learning Python & PHP. Well,
at least I'm trying to whenever I can find a spare moment (which is rare).
On Sat, 12 Aug 2000, David Lloyd-Jones wrote:
> John,
>
> Many thanks. What I actually need is to present the five weekdays of a week
> beginning with Sunday on a drop-down box in TCL; then have a person click on
> any day and get sales for that day, or on a thing at the bottom to get sales
> for the week
>
> Do you TCL? Can you give me any clues on drop-down boxes, and how to get
> the -U, Linux week-of-the-year, into them?
>
> Best wishes,
>
> -dlj.
>
> Hey, I didn't know Prodigy still existed!
>
>
>
>
>
> ----- Original Message -----
> From: "John McKown" <jmckown@prodigy.net>
> To: "David Lloyd-Jones" <david.lloyd-jones@attcanada.ca>
> Cc: <pgsql-sql@postgresql.org>
> Sent: Friday, August 11, 2000 10:55 PM
> Subject: Re: [SQL] Week of the Year?
>
>
> >
> > Try using the function date_part such as:
> >
> > select date_part('week',now());
> >
> > "and the days that are in that week" I guess want to answer a question
> > such as:
> > Given a date, what is first date in that same week, and what is the last
> > date in that week. There are a couple of approaches to this. My first was:
> >
> > select
> > to_date(date_part('year',now()),'YYYY')+(7*date_part('week',now()));
> >
> > and the above +6 to the the last day of the week. Another approach for
> > this same question is much simplier (if the question is indeed what you
> > are asking)
> >
> > select now()-date_part('dow',now());
> >
> > This last select gives the Sunday for the current week. To get the
> > Saturday, simply:
> >
> > select now()-date_part('dow',now())+6;
> >
> > Of course, replace the now() with whatever contains the date or timestamp.
> >
> > John McKown
> >
> > > I'm probably staring right at it. (One of the difficulties with RTFMing,
> is
> > > having too many docs!)
> > >
> > > Is there anything in the API that produces the week of the year, from 1
> to
> > > 52 or 53 depending on the week of the year, and the days that are in
> that
> > > week?
> > >
> > > Many thanks.
> > >
> > > -dlj.
> > >
> > >
> > >
> >
> >
>
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:
>Try using the function date_part such as:
>
>select date_part('week',now());
>
>"and the days that are in that week" I guess want to answer a question
>such as:
>Given a date, what is first date in that same week, and what is the last
>date in that week. There are a couple of approaches to this. My first was:
>
>select
>to_date(date_part('year',now()),'YYYY')+(7*date_part('week',now()));
>
>and the above +6 to the the last day of the week. Another approach for
>this same question is much simplier (if the question is indeed what you
>are asking)
>
>select now()-date_part('dow',now());
>
>This last select gives the Sunday for the current week. To get the
>Saturday, simply:
>
>select now()-date_part('dow',now())+6;
>
>Of course, replace the now() with whatever contains the date or timestamp.
>
>John McKown
>
> > I'm probably staring right at it. (One of the difficulties with RTFMing, is
> > having too many docs!)
> >
> > Is there anything in the API that produces the week of the year, from 1 to
> > 52 or 53 depending on the week of the year, and the days that are in that
> > week?
> >
> > Many thanks.
> >
> > -dlj.
> >
> >
> >
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