X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id CEE00D1DD21 for ; Mon, 16 Feb 2004 12:44:27 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 08158-02 for ; Mon, 16 Feb 2004 08:44:28 -0400 (AST) Received: from srvr3.iniquinet.com (srvr2.iniquinet.com [64.240.87.12]) by svr1.postgresql.org (Postfix) with SMTP id 0DFD8D1DCD6 for ; Mon, 16 Feb 2004 08:44:24 -0400 (AST) Received: (qmail 11111 invoked by uid 104); 16 Feb 2004 12:30:09 -0000 Received: from Robert_Creager@LogicalChaos.org by srvr3.iniquinet.com by uid 101 with qmail-scanner-1.15 (clamscan: 0.65. spamassassin: 2.55. Clear:SA:0(-7.2/6.0):. Processed in 36.66118 secs); 16 Feb 2004 12:30:09 -0000 Received: from vsat-148-64-8-86.c119.t7.mrt.starband.net (HELO chaos.mshome.net) (148.64.8.86) by srvr3.iniquinet.com with SMTP; 16 Feb 2004 12:29:32 -0000 Received: from localhost.localdomain (thunder.mshome.net [192.168.0.250]) by chaos.mshome.net (Postfix) with ESMTP id 8C5D91E456F; Mon, 16 Feb 2004 05:43:44 -0700 (MST) Received: from logicalchaos.org (thunder.mshome.net [192.168.0.250]) by chaos.mshome.net (Postfix) with SMTP id 90CB718B911; Mon, 16 Feb 2004 05:43:40 -0700 (MST) Date: Mon, 16 Feb 2004 05:43:20 -0700 From: Robert Creager To: "Kumar" Cc: "psql" Subject: Re: Getting the week of a date Message-Id: <20040216054320.32df9226.Robert_Creager@LogicalChaos.org> In-Reply-To: <00f701c3f485$d7091430$7502a8c0@hdsc.com> References: <00f701c3f485$d7091430$7502a8c0@hdsc.com> Organization: Starlight Vision, LLC. X-Mailer: Sylpheed version 0.9.5claws (GTK+ 1.2.10; i586-mandrake-linux-gnu) Mime-Version: 1.0 Content-Type: multipart/signed; protocol="application/pgp-signature"; micalg="pgp-sha1"; boundary="Multipart_Mon__16_Feb_2004_05_43_20_-0700_=.(.ItbW1)tGSFUG" USER_IN_WHITELIST autolearn=ham version=2.63 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200402/194 X-Sequence-Number: 16884 --Multipart_Mon__16_Feb_2004_05_43_20_-0700_=.(.ItbW1)tGSFUG Content-Type: text/plain; charset=US-ASCII Content-Disposition: inline Content-Transfer-Encoding: 7bit When grilled further on (Mon, 16 Feb 2004 17:40:08 +0530), "Kumar" confessed: > Dear Friends, > > Postgres 7.3.4 on RH Linux7.2. > > While this works for month and why not for week > date_trunc (obviously) doesn't support week. I ran into this a while ago, and came up with this function. I left the function signature the same as date_trunc, even though I don't use the first argument. I did only minor testing (10 years or so), so no guarantee about it's correctness. And it's kind of slow... CREATE OR REPLACE FUNCTION date_trunc_week( text, timestamp ) RETURNS timestamp AS ' DECLARE reading_time ALIAS FOR $2; year timestamp; dow integer; adjust text; week text; BEGIN year := date_trunc( ''year''::text, reading_time ); week := date_part( ''week'', reading_time ) - 1 || '' week''; dow := date_part( ''dow'', year ); -- If the dow is less than Thursday, then the start week is last year IF dow <= 4 THEN adjust := 1 - dow || '' day''; ELSE adjust := 8 - dow || '' day''; END IF; RETURN year + adjust::interval + week::interval; END; ' LANGUAGE plpgsql IMMUTABLE STRICT; -- 05:37:49 up 1 day, 13:20, 2 users, load average: 0.09, 0.36, 0.63 Linux 2.4.21-0.13_test #60 SMP Sun Dec 7 17:00:02 MST 2003 --Multipart_Mon__16_Feb_2004_05_43_20_-0700_=.(.ItbW1)tGSFUG Content-Type: application/pgp-signature -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.2 (GNU/Linux) iEYEARECAAYFAkAwuvwACgkQLQ/DKuwDYzm2xACdGIajt5kT/heTMhmiyIqa63Gb 9isAn2WgpI2A2GLiOaLFLbyjjZMmy6JS =Hhtd -----END PGP SIGNATURE----- --Multipart_Mon__16_Feb_2004_05_43_20_-0700_=.(.ItbW1)tGSFUG--