Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 3E2449FB267 for ; Fri, 26 Jan 2007 16:11:34 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-new, port 10024) with ESMTP id 36589-01 for ; Fri, 26 Jan 2007 16:11:30 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from wolff.to (wolff.to [66.93.197.194]) by postgresql.org (Postfix) with SMTP id 919C49FB1BF for ; Fri, 26 Jan 2007 16:11:29 -0400 (AST) Received: (qmail 14627 invoked by uid 500); 26 Jan 2007 20:11:24 -0000 Date: Fri, 26 Jan 2007 14:11:24 -0600 From: Bruno Wolff III To: Achilleas Mantzios Cc: pgsql-sql@postgresql.org Subject: Re: Intersection of two date interval Message-ID: <20070126201124.GA11685@wolff.to> Mail-Followup-To: Bruno Wolff III , Achilleas Mantzios , pgsql-sql@postgresql.org References: <49a67b230701260139n1153ee82x8ae91875118f3cd2@mail.gmail.com> <20070126095007.GD15591@a-kretschmer.de> <200701261202.26168.achill@matrix.gatewaynet.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <200701261202.26168.achill@matrix.gatewaynet.com> User-Agent: Mutt/1.4.2.1i X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200701/143 X-Sequence-Number: 27295 On Fri, Jan 26, 2007 at 12:02:24 +0200, Achilleas Mantzios wrote: > Suha, > the function is the number of days in the > maximum of the two start dates , untill , minimum of the two end dates > interval. > But in postgresql (7.4.15 at least) there is no MIN(date,date),MAX(date,date) > functions. So someone has to write them, so you cant avoid some logic there. > Whats your problem with "CASE" statements? > what you are basically looking for is smth like the above implementation > from Andreas. Postgres (at least since 8.1) has GREATEST and LEAST and they provide the needed functions. And you should be able to subtract two dates (not timestamps) to get an integer. (And to count the number of days rather than the time between, you need to add 1.) You also will want to put a floor on the overlap so that you don't get negative values. So in your case the query would look something like: bruno=> SELECT GREATEST(0, LEAST('2007-01-20'::date, '2007-01-21'::date) - bruno(> GREATEST('2007-01-10'::date, '2007-01-13'::date) + 1); greatest ---------- 8 (1 row)