Re: [SQL] Week of year function?

----- Original Message -----
From: Herouth Maoz <herouth@oumail.openu.ac.il>
To: Zot O'Connor <zot@zotconsulting.com>; postgres sql <pgsql-sql@hub.org>
Sent: Monday, October 25, 1999 6:14 PM
Subject: Re: [SQL] Week of year function?


> At 21:52 +0200 on 22/10/1999, Zot O'Connor wrote:
>
>
> > Is there a function to return the week of the year (0-51)?
>
> Seems you only need to divide the day of the year by seven to reach that,
> don't you?
>
> Maybe you should try:
>
> CREATE FUNCTION week( datetime ) RETURNS int4 AS '
>   SELECT int( date_part( ''day'', $1 - date_trunc( ''year'', $1 ) ) ) / 7
> ' LANGUAGE 'sql';

I don't think that's quite right. You would need to add some maths to make
sure that if January 1st is a Wednesday, week 1 of the year begins on
January 6th (with Monday as first day of week) or Jan 5th (Sunday as first
day of week).

CREATE FUNCTION week( datetime ) RETURNS int4 AS ' SELECT (int( date_part( ''day'', $1 - date_trunc( ''year'',
$1 )))-datepart(''dow'',date_trunc(''year'',$1)))) / 7' LANGUAGE 'sql';

I don't have postgres accessible from this computer, but I think it should
be something like that - gives weeks 0-51 if dow returns 0-6. Herouth can
probably rewrite it more concisely..

Yours,
Moray