From: | "Moray McConnachie" <moray(dot)mcconnachie(at)computing-services(dot)oxford(dot)ac(dot)uk> |
---|---|
To: | "Zot O'Connor" <zot(at)zotconsulting(dot)com>, "postgres sql" <pgsql-sql(at)hub(dot)org>, "Herouth Maoz" <herouth(at)oumail(dot)openu(dot)ac(dot)il> |
Subject: | Re: [SQL] Week of year function? |
Date: | 1999-10-25 19:04:22 |
Message-ID: | 002501bf1f1b$c03a7da0$681b4cc0@public.ox.ac.uk |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
----- Original Message -----
From: Herouth Maoz <herouth(at)oumail(dot)openu(dot)ac(dot)il>
To: Zot O'Connor <zot(at)zotconsulting(dot)com>; postgres sql <pgsql-sql(at)hub(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Christian Guenther | 1999-10-25 19:43:15 | different between || and && in a statement |
Previous Message | Herouth Maoz | 1999-10-25 17:14:11 | Re: [SQL] Week of year function? |