Skip site navigation (1) Skip section navigation (2)

Re: Getting the week of a date

From: Robert Creager <Robert_Creager(at)LogicalChaos(dot)org>
To: "Kumar" <sgnerd(at)yahoo(dot)com(dot)sg>
Cc: "psql" <pgsql-sql(at)postgresql(dot)org>
Subject: Re: Getting the week of a date
Date: 2004-02-16 12:43:20
Message-ID: 20040216054320.32df9226.Robert_Creager@LogicalChaos.org (view raw or flat)
Thread:
Lists: pgsql-sql
--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(at)yahoo(dot)com(dot)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--



Attachment: unknown_filename
Description: multipart/signed (1.8 KB)

In response to

Responses

pgsql-sql by date

Next:From: Richard HuxtonDate: 2004-02-16 13:03:12
Subject: Re: Getting the week of a date
Previous:From: KumarDate: 2004-02-16 12:10:08
Subject: Getting the week of a date

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group