Received: from jbarilla.student.princeton.edu (jbarilla.student.Princeton.EDU [140.180.137.120]) by postgresql.org (Postfix) with ESMTP id 3ACEA475872 for ; Sun, 28 Apr 2002 20:56:21 -0400 (EDT) Received: (from jbarilla@localhost) by jbarilla.student.princeton.edu (8.11.6/8.11.6) id g3T0uO031810; Sun, 28 Apr 2002 20:56:24 -0400 To: pgsql-sql@postgresql.org Subject: Casting dates 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: Sun, 28 Apr 2002 20:56:23 -0400 Message-ID: Lines: 62 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/347 X-Sequence-Number: 7611 --=-=-= Content-Transfer-Encoding: quoted-printable Hi, 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? 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.) Subtracting two timestamps gives a day count: cal=3D> select timestamp 'today' - timestamp 'may 1, 2000'; ?column?=20 ---------- 727 days (1 row) Subtracting two days-only intervals gives the expected result: cal=3D> select interval '6000 days' - interval '100 days'; ?column?=20=20 ----------- 5900 days (1 row) But it doesn't work for all like-unit intervals: some are reported using other quantities: cal=3D> select interval '6000 minutes' - interval '100 minutes'; ?column?=20=20=20 -------------- 4 days 02:20 <--- not the expected '5900 minutes' (1 row) And most notably, it doesn't work for years, where it would be most useful: cal=3D> select timestamp 'jan 24, 1998 00:00' - timestamp 'jan 24, 1990 00= :00'; ?column?=20=20 ----------- 2922 days (1 row) cal=3D> select extract (years from timestamp 'jan 24, 1998 00:00' - timesta= mp 'jan 24, 1990 00:00'); date_part=20 ----------- 0 (1 row) My aim is to make it easier to write a function that manipulates years (the code fragment in question takes two timestamps, A and B, and an integer N, and subtracts A from B to see if they differ by a multiple of N years. If not, then it adds years to B to ensure that A and B differ by a multiple of N). Any ideas would be appreciated. Cordially, Joe Barillari --=-=-= 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 iD8DBQE8zJo4IV4b0vah+jcRAlFZAJ0cKuAi6NXi17D65tuwo7aA+cS9hwCeOrwl sCb5f9jCe+lhsLZPZZ3uBEE= =t55w -----END PGP MESSAGE----- --=-=-=--