From: Joseph Barillari <jbarilla@princeton.edu>
To: pgsql-sql@postgresql.org
Subject: Casting dates
Date: Sun, 28 Apr 2002 20:56:23 -0400
Message-ID: <m3helvmifc.fsf@washer.barillari.org> (raw)
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=> select timestamp 'today' - timestamp 'may 1, 2000';
?column?
----------
727 days
(1 row)
Subtracting two days-only intervals gives the expected result:
cal=> select interval '6000 days' - interval '100 days';
?column?
-----------
5900 days
(1 row)
But it doesn't work for all like-unit intervals: some are reported
using other quantities:
cal=> select interval '6000 minutes' - interval '100 minutes';
?column?
--------------
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=> select timestamp 'jan 24, 1998 00:00' - timestamp 'jan 24, 1990 00:00';
?column?
-----------
2922 days
(1 row)
cal=> select extract (years from timestamp 'jan 24, 1998 00:00' - timestamp 'jan 24, 1990 00:00');
date_part
-----------
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jbarilla@princeton.edu
Subject: Re: Casting dates
In-Reply-To: <m3helvmifc.fsf@washer.barillari.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox