Hi Mitch,
>> This is more Thomas' bailiwick than mine, but it seems to me that these
>> operations are inherently rather ill-defined. Consider: counting
>> forward from Oct 10 to Dec 3, one would naturally call the interval
>> "1 month + 23 days" (1 month takes you to Nov 10, from which it's
>> 23 days to Dec 3, no?). But counting backwards from Dec 3 to Oct 10
>> looks like "1 month + 22 days" (1 month takes you to Nov 3, from which
>> it's 22 days back to Oct 12). The trouble is that Oct and Nov have
>> different numbers of days, so you get different answers depending on
>> what your referent for "1 month" is.
[...]
>> Maybe we need to offer a different kind of interval that avoids the
>> symbolic "month" rigmarole and just counts honest-to-god seconds.
I don't know if that will help but this is the way I have work for a
few years now : dates are stored as floats with integer part= julian
date (number of days since some special date like epoch) and
fractionnal part is a portion of 1 day (that is 0.25 is 6am, 1/86400=1
second, etc.)
In oracle you can write :
select trunc(sysdate-mydate) as diffdays from mytable;
I haven't found out --yet-- how to do such calculations with
Postgresql.
Thay also have a few very clever functions that should not be too hard
to code in pg if we can gain access to date arithmetics.
Anyone already found interesting things ?
--
Jean-Christophe Boggio
cat@thefreecat.org
Independant Consultant and Developer
Delphi, Linux, Oracle, Perl
