Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id A39BF4767DC for ; Thu, 26 Sep 2002 09:19:52 -0400 (EDT) Received: from wolff.to (unknown [66.93.197.35]) by postgresql.org (Postfix) with SMTP id B358D4766BD for ; Thu, 26 Sep 2002 09:19:51 -0400 (EDT) Received: (qmail 22072 invoked by uid 500); 26 Sep 2002 13:26:44 -0000 Date: Thu, 26 Sep 2002 08:26:44 -0500 From: Bruno Wolff III To: "John Sebastian N. Mayordomo" Cc: "Pgsql-Sql@Postgresql. Org" Subject: Re: start and end of the week Message-ID: <20020926132644.GA21571@wolff.to> Mail-Followup-To: "John Sebastian N. Mayordomo" , "Pgsql-Sql@Postgresql. Org" References: <1033037809.16948.2.camel@tiger-work.sni.ph> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <1033037809.16948.2.camel@tiger-work.sni.ph> User-Agent: Mutt/1.3.25i X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200209/323 X-Sequence-Number: 9600 On Thu, Sep 26, 2002 at 18:56:46 +0800, "John Sebastian N. Mayordomo" wrote: > > > How do I get the start and end date of the present week? > Is this possible? > > For example this week > Start = Sept. 22 > End = Sept. 28 The following advice will work on 7.3. For 7.2.2 and earlier it might not work during a week with a timezone change depending at what time of day you switch between DST and ST. To fix this you need to cast current_date to a timestamp without timezone, and I haven't been able to figure out how to do that and have to run off to a meeting now. For the first day of the current week use something like: area=> select current_date - extract(dow from current_date) * area-> '1 day'::interval; ?column? --------------------- 2002-09-22 00:00:00 (1 row) For the last day of the week use something like: area=> select current_date + (6 - extract(dow from current_date)) * area-> '1 day'::interval; ?column? --------------------- 2002-09-28 00:00:00 (1 row)