Thread: Casting dates

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

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?
> 
> 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.)

Unfortunately, you've hit one of Postgres implementation limits.  Multiplying and dividing INTERVAL values, while
coveredby the SQL92
 
spec, has not been completely implemented in Postgres.   That is, it
should be possible for you to:

SELECT  INTERVAL '3 days 5 hours' / INTERVAL '1 minute'

However, implementing this has been a challenge for all SQL-DB
designers.  I don't know of any DB that does handle INTERVAL division.

It's a surmountable challenge, though, but requires somebody to take
the lead in a) working out the logic, and b) writing the code to
implement it.  A) is not a trivial task, either ... while 60 minutes /
1 second is obvious, how about 4 months / 4 days?  Months are not
constant in length.

I've had some ideas for a spec for this myself (expanding on the rather
terse spec in SQL 92) but have not had time to write it up.  Good luck.

-Josh Berkus

>>>>> "JB" == Josh Berkus <josh@agliodbs.com> 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?   >>    >>
PostgreSQLusually 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-correctresult,   >> 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
shouldbe 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
doeshandle INTERVAL   JB> division. 
   JB> It's a surmountable challenge, though, but requires somebody   JB> to take the lead in a) working out the logic,
andb) writing   JB> the code to implement it.  A) is not a trivial task, either   JB> ... while 60 minutes / 1 second
isobvious, 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,

select date_part('day', (timestamp 'jan 1, 1996' -                       timestamp 'jan 1, 1992'));

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=> select date_part('day', (timestamp 'jan 1, 2600'              - timestamp 'jan 1, 1000'))/365;    ?column?
------------------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