Received: from localhost.localdomain (postgresql.org [64.49.215.8]) by localhost (Postfix) with ESMTP id F17BC475E7C for ; Mon, 29 Apr 2002 12:36:40 -0400 (EDT) Received: from jbarilla.student.princeton.edu (jbarilla.student.Princeton.EDU [140.180.137.120]) by postgresql.org (Postfix) with ESMTP id 2C85447599A for ; Mon, 29 Apr 2002 12:19:18 -0400 (EDT) Received: (from jbarilla@localhost) by jbarilla.student.princeton.edu (8.11.6/8.11.6) id g3TGJM901958; Mon, 29 Apr 2002 12:19:22 -0400 To: Josh Berkus Cc: pgsql-sql@postgresql.org Subject: Re: Casting dates References: From: Joseph Barillari X-Public-Key: finger -l jbarilla@phoenix.princeton.edu X-Public-Key-Fingerprint: 99C7 4F49 AF41 AD0F A4FC 529C 215E 1BD2 F6A1 FA37 X-URL: http://www.princeton.edu/~jbarilla Date: Mon, 29 Apr 2002 12:19:21 -0400 In-Reply-To: (Josh Berkus's message of "Mon, 29 Apr 2002 09:02:07 -0700") Message-ID: Lines: 58 User-Agent: Gnus/5.090006 (Oort Gnus v0.06) Emacs/21.2.50 (i686-pc-linux-gnu) MIME-Version: 1.0 Content-Type: multipart/signed; boundary="=-=-="; micalg=pgp-sha1; protocol="application/pgp-signature" X-Archive-Number: 200204/361 X-Sequence-Number: 7625 --=-=-= Content-Transfer-Encoding: quoted-printable >>>>> "JB" =3D=3D Josh Berkus writes: JB> Joe, >> Does anyone know if it's possible to calculate the number of >> days (or weeks, seconds, minutes, or what-have-you) in a given >> interval without casting to EPOCH and performing manipulations >> there? >>=20 >> PostgreSQL usually returns intervals created by subtraction in >> days and smaller fragments, I would like to know if it's >> possible for it to return years. (Integer division of the >> number of days by 365 would produce an almost-correct result, >> but it's rather inelegant.) JB> Unfortunately, you've hit one of Postgres implementation JB> limits. Multiplying and dividing INTERVAL values, while JB> covered by the SQL92 spec, has not been completely implemented JB> in Postgres. That is, it should be possible for you to: JB> SELECT INTERVAL '3 days 5 hours' / INTERVAL '1 minute' JB> However, implementing this has been a challenge for all SQL-DB JB> designers. I don't know of any DB that does handle INTERVAL JB> division. =20 JB> It's a surmountable challenge, though, but requires somebody JB> to take the lead in a) working out the logic, and b) writing JB> the code to implement it. A) is not a trivial task, either JB> ... while 60 minutes / 1 second is obvious, how about 4 months JB> / 4 days? Months are not constant in length. For the purposes of this application (because it deals in relatively short intervals, it's a calendaring application), I just divide the day count by 365 and throw away the remainder. For example,=20 select date_part('day', (timestamp 'jan 1, 1996' - timestamp 'jan 1, 1992'));=20 is 1461 days, or 1+365*4 (1992 is a leap year). Integer division of 1461/365 produces the correct year count. This doesn't work for longer intervals: cal=3D> select date_part('day', (timestamp 'jan 1, 2600'=20 - timestamp 'jan 1, 1000'))/365; ?column?=20=20=20=20=20 ------------------ 1601.06301369863 <-- an error of +1 year, due to accumulated leap days Obviously, this naive method won't work for astronomical time intervals, but for my purposes, few people have calendars that stretch across millennia. --Joe --=-=-= Content-Type: application/pgp-signature -----BEGIN PGP MESSAGE----- Version: GnuPG v1.0.6 (GNU/Linux) Comment: Processed by Mailcrypt 3.5.6 and Gnu Privacy Guard iD8DBQE8zXKJIV4b0vah+jcRAuyDAKCATpvxS1Edg9tTbRPWmgzl0XQwxgCeLGnI s+S/qQM0JOG+TkHIeOcYmBE= =1HVo -----END PGP MESSAGE----- --=-=-=--