X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 9C6E09DC856 for ; Sat, 18 Feb 2006 10:45:13 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 72560-09 for ; Sat, 18 Feb 2006 10:45:17 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from ciao.gmane.org (main.gmane.org [80.91.229.2]) by postgresql.org (Postfix) with ESMTP id 7E83A9DC84C for ; Sat, 18 Feb 2006 10:45:11 -0400 (AST) Received: from list by ciao.gmane.org with local (Exim 4.43) id 1FATKj-0000BE-OD for pgsql-sql@postgresql.org; Sat, 18 Feb 2006 15:45:13 +0100 Received: from 85.187.136.11 ([85.187.136.11]) by main.gmane.org with esmtp (Gmexim 0.1 (Debian)) id 1AlnuQ-0007hv-00 for ; Sat, 18 Feb 2006 15:45:13 +0100 Received: from milen by 85.187.136.11 with local (Gmexim 0.1 (Debian)) id 1AlnuQ-0007hv-00 for ; Sat, 18 Feb 2006 15:45:13 +0100 X-Injected-Via-Gmane: http://gmane.org/ To: pgsql-sql@postgresql.org From: "Milen A. Radev" Subject: Re: Interval subtracting Date: Sat, 18 Feb 2006 16:45:34 +0200 Lines: 43 Message-ID: <43F7330E.1060907@radev.net> References: <200602181444.06009.milorad.poluga@cores.co.yu> <43F72E27.2060408@radev.net> <200602181536.20605.milorad.poluga@cores.co.yu> Mime-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit X-Complaints-To: usenet@sea.gmane.org X-Gmane-NNTP-Posting-Host: 85.187.136.11 User-Agent: Mozilla Thunderbird 1.5 (Windows/20051201) In-Reply-To: <200602181536.20605.milorad.poluga@cores.co.yu> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=1.373 required=5 tests=[AWL=0.120, RCVD_NUMERIC_HELO=1.253] X-Spam-Score: 1.373 X-Spam-Level: * X-Archive-Number: 200602/237 X-Sequence-Number: 24247 Milorad Poluga написа: > On Saturday 18 February 2006 15:24, Milen A. Radev wrote: >> Milorad Poluga написа: >>> Hi all, >>> >>> Is there something incorrect in the above query ? >>> SELECT '10 years 1 mons 1 days'::interval - '9 years 10 mons 15 days'::interval >>> >>> ?column? >>> --------------- >>> 3 mons -14 days >>> >>> Why not '2 mons 16 days' ? >>> >>> /version = PostgreSQL 8.0.4 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.2 20041017 (Red Hat 3.4.2-6.fc3) / >> >> How many days are there in a month? >> > > I beleive that a month is calculated on the 30-days base. Are you sure? Where? > > One way to solve this problem is to use a neutal date element and make timestamps : > > SELECT age(('1990-01-01'::date + '10 years 1 mons 1 days'::interval)::timestamp , > ('1990-01-01'::date + '9 years 10 mons 15 days'::interval)::timestamp) > > age > -------------- > 2 mons 16 days > Please read the last paragraph in section 8.5.1.4 of the manual (http://www.postgresql.org/docs/8.1/static/datatype-datetime.html#AEN4775) . It mentions the functions named "justify_days" and "justify_hours" that could do what you need. -- Milen A. Radev