Re: [SQL] Week of the Year?

[Brian C. Doyle]

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()),'')+(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.
> >
> >
> >

Re: [SQL] Week of the Year?

[John McKown]

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
 

[SQL] Re: Auto increment

[Richard Rowell]

In article , "Mads Jensen"
<[EMAIL PROTECTED]> wrote:
> Hi
> 
> I'm a newbiw with pgsql:
> 
> 1: Haven't been able to find the officiel manual to pgsql. What's the
> complete URL?

Hmm, I don't know, I use the book which you can find with all the rest of the docs at:
http://www.postgresql.org/docs/index.html


> 2: How can I make an auto increment with PostGreSQL? is it "inherit"?

One of two ways.  Either make the data type "serial" or you can create a sequence and 
set the default value of the field to curval(sequence).

[SQL] Order by in stored functions

[Andreas Tille]

Hello

I tried to create the following function

CREATE Function pGetMenu ( int )
  returns setof varchar
  As '
SELECT IdMenuShow || IdWebPage
FROM   Menu
WHERE  IdMenu = $1 
ORDER BY IdSort ;
  ' language 'SQL' ;

I've got the following error message:

ERROR:  function declared to return varchar returns multiple values in final retrieve

If O just remove the ORDER BY clause als works well so I guess that the
ORDER BY has to be replaced by somethjing other.  Could somebody
enlighten me how to do the ordering in a stored procedure?

Kind regards

  Andreas.

[SQL] pg_hba.conf

[Craig May]

Hi,

I'm recieving an error "No pg_hba.conf entry for host XXX, usr XXX, database
XXX."  I've modified the pg_hba.conf.sample file to allow all access, however
still getting the same message.  Any suggestions?

Regards,
Craig May