> template1=> select age(now() + '01:30:00'::interval);
> age
> ------------------
> -17:02:41.247957
> (1 row)
>
> So, One and a half hour in the future is actually 17 days ago?
> Interesting... Either I am doing something wrong, or postgres is, I have
> my suspicions ;)
>
I guess the result is correct, it's 17 hours, not days, and it is
calculating the difference between the start of this day (current_date)
and the timestamp you gave as parameter to the age function. In that
case the result is correct... the argument is bigger than the start of
the day, and it is substracted from it, so the result is negative.
> Now, what I tried to achieve was a numeric representation for the
> interval.
For that purpose you might want something like:
dbval=# select date_part('day', justify_hours('127:30:00'::interval));
date_part
-----------
5
(1 row)
See:
http://www.postgresql.org/docs/8.1/static/functions-datetime.html
It would be nice to have a variant of the date_part functions which
returns a floating point result expressing the complete interval value
scaled according to the given field, but including the scaled values of
all fields. You can achieve that now by extracting separately each
field, multiplying with the proper weight and summing all that. Of
course that can be wrapped in a function, but it's not so nice as a
built-in would be...
Cheers,
Csaba.