pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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

view thread (3+ messages)  latest in thread

Message-ID: <m3helvmifc.fsf@washer.barillari.org>
Permalink:  ../m3helvmifc.fsf@washer.barillari.org/
Also on:    postgresql.org/message-id/m3helvmifc.fsf@washer.barillari.org

 · 

reply

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