From: Robert Creager <Robert_Creager@LogicalChaos.org>
To: Kumar <sgnerd@yahoo.com.sg>
Cc: psql <pgsql-sql@postgresql.org>
Subject: Re: Getting the week of a date
Date: Mon, 16 Feb 2004 05:43:20 -0700
Message-ID: <20040216054320.32df9226.Robert_Creager@LogicalChaos.org> (raw)
In-Reply-To: <00f701c3f485$d7091430$7502a8c0@hdsc.com>
References: <00f701c3f485$d7091430$7502a8c0@hdsc.com>
--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" <sgnerd@yahoo.com.sg> 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--
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: Robert_Creager@LogicalChaos.org, sgnerd@yahoo.com.sg
Subject: Re: Getting the week of a date
In-Reply-To: <20040216054320.32df9226.Robert_Creager@LogicalChaos.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox