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 19699D1DB0E for ; Wed, 31 Mar 2004 10:41:39 +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 46248-10 for ; Wed, 31 Mar 2004 06:41:28 -0400 (AST) Received: from tx1.oucs.ox.ac.uk (tx1.oucs.ox.ac.uk [129.67.1.167]) by svr1.postgresql.org (Postfix) with ESMTP id B021CD1E158 for ; Wed, 31 Mar 2004 06:41:25 -0400 (AST) Received: from scan1.oucs.ox.ac.uk ([129.67.1.166] helo=localhost) by tx1.oucs.ox.ac.uk with esmtp (Exim 4.24) id 1B8d9z-00060I-Hg for pgsql-sql@postgresql.org; Wed, 31 Mar 2004 11:41:27 +0100 Received: from rx1.oucs.ox.ac.uk ([129.67.1.165]) by localhost (scan1.oucs.ox.ac.uk [129.67.1.166]) (amavisd-new, port 25) with ESMTP id 22766-08 for ; Wed, 31 Mar 2004 11:41:27 +0100 (BST) Received: from smtp0.herald.ox.ac.uk ([163.1.0.246]) by rx1.oucs.ox.ac.uk with esmtp (Exim 4.24) id 1B8d9z-0005zt-3y for pgsql-sql@postgresql.org; Wed, 31 Mar 2004 11:41:27 +0100 Received: from mizar.oucs.ox.ac.uk ([163.1.15.107]) by smtp0.herald.ox.ac.uk with esmtp (Exim 3.35 #1) id 1B8d9z-0002Zf-3y for pgsql-sql@postgresql.org; Wed, 31 Mar 2004 11:41:27 +0100 Received: from stephen by mizar.oucs.ox.ac.uk with local (Exim 3.36 #1 (Debian)) id 1B8d9z-0003qI-00 for ; Wed, 31 Mar 2004 11:41:27 +0100 Date: Wed, 31 Mar 2004 11:41:27 +0100 From: Stephen Quinney To: pgsql-sql@postgresql.org Subject: Timestamp manipulation Message-ID: <20040331104126.GB11932@computing-services.oxford.ac.uk> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline X-Operating-System: Linux mizar 2.6.4 x-gpg-fingerprint: F934 244D 4E26 DBDE C9EF BFB7 2131 9B94 4C1A 5BE5 x-gpg-key: wwwkeys.uk.pgp.net User-Agent: Mutt/1.5.5.1+cvs20040105i X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200403/269 X-Sequence-Number: 17368 I am having a bit of difficulty trying to find a solution to this problem of manipulating timestamps and dates so I hope someone can enlighten me. I have a table which contains a column of type timestamp. For each row I want to find out the dates for the beginning and end of the week within which the timestamp lies. e.g. 2004-04-09 16:00:00+01 The beginning of the week (sunday) is: 2004-04-4 and the end of the week (saturday) is: 2004-04-10 Any suggestions as to how to go about this would be great, thanks in advance. Stephen Quinney