Received: from oxmail.ox.ac.uk (oxmail4.ox.ac.uk [163.1.2.33]) by hub.org (8.9.3/8.9.3) with ESMTP id PAA37612 for ; Mon, 25 Oct 1999 15:06:41 -0400 (EDT) (envelope-from moray.mcconnachie@computing-services.oxford.ac.uk) Received: from heraldgate1.oucs.ox.ac.uk ([163.1.2.49] helo=frontend1.herald.ox.ac.uk ident=exim) by oxmail.ox.ac.uk with esmtp (Exim 2.10 #1) id 11fpRw-0006Cr-00; Mon, 25 Oct 1999 20:06:32 +0100 Received: from max104.public.ox.ac.uk ([192.76.27.104] helo=max1.public.ox.ac.uk) by frontend1.herald.ox.ac.uk with smtp (Exim 2.02 #1) id 11fpRw-00047v-00; Mon, 25 Oct 1999 20:06:32 +0100 Message-ID: <002501bf1f1b$c03a7da0$681b4cc0@public.ox.ac.uk> From: "Moray McConnachie" To: "Zot O'Connor" , "postgres sql" , "Herouth Maoz" References: Subject: Re: [SQL] Week of year function? Date: Mon, 25 Oct 1999 20:04:22 +0100 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.00.2314.1300 X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2314.1300 ----- Original Message ----- From: Herouth Maoz To: Zot O'Connor ; postgres sql 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