Обсуждение: Age function

Hi,

I believe I already mentioned something along these lines as an aside,
but this time I need it to work...

Look here:

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 ;)

Now, what I tried to achieve was a numeric representation for the
interval. As an interval of (for example) a month is kind of hard to
translate into a number (28, 29, 30 or 31 days?), I figured adding the
interval to now() and calculating the age of the result. I figured that
would nicely give me what I want... For the types of intervals I use
(hours and minutes) this could have worked, but... well, see above.

For the record, I tried this first on 7.4.7 (debian packages... shrug)
and then on 8.1.3. Both show this behaviour.

Regards,
--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
    7500 AK Enschede

// Integrate Your World //

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

Alban Hertroys wrote:

 > Look here:
 >
 > 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 ;)

<good-natured sarcasm>Your suspicions are correct that you are doing, or
rather, understanding  something incorrectly. </good-natured sarcasm>

First of all, the "17" is hours, not days. Look again at the formatted
output.

Secondly, there are two forms of AGE, taking, respectively two arguments
and one argument. The latter is a shorthand for the former, assuming the
current time as the base time. So in your query is equivalent to

('2006-05-17 00:00:00') - ('2006-05-17 10:02:01.727674-04' + '01:30:00')

(my current time, 10:02:01.727674-04, is different from what yours was,
obviously)

Looking that the output from :

select
    age(now() + '01:30:00'::interval),
    now(),
    current_date::timestamp,
    current_time,
    age(current_date, now() + '01:30:00'::interval),
    age(now() + '01:30:00'::interval, current_date),
    age(now() + '01:30:00'::interval, now())


might help, <professorial pontification>as would RTFM
("http://www.postgresql.org/docs/8.1/static/functions-datetime.html",
Table 9-26. Date/Time Functions). </professorial pontification>

Regards,
Berend Tober

Berend Tober wrote:
> Alban Hertroys wrote:
>  > 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 ;)
>
> <good-natured sarcasm>Your suspicions are correct that you are doing, or
> rather, understanding  something incorrectly. </good-natured sarcasm>

No sarcasm needed, that was exactly what I was referring to ;)

> First of all, the "17" is hours, not days. Look again at the formatted
> output.

Yeah, I "corrected" myself after reading an example in the docs where
the age between two dates was calculated (as opposed to timestamps), and
figured I must've misread that as hours while they were days. That colon
pretty much nails it down as hours though. Silly me...

> Secondly, there are two forms of AGE, taking, respectively two arguments
> and one argument. The latter is a shorthand for the former, assuming the
> current time as the base time. So in your query is equivalent to
>
> ('2006-05-17 00:00:00') - ('2006-05-17 10:02:01.727674-04' + '01:30:00')

Ok, I see now. Actually, now I read it back, the docs for age(timestamp)
say it calculates from current_date - I missed that earlier.

> Looking that the output from :
(...)
> might help, <professorial pontification>as would RTFM
> ("http://www.postgresql.org/docs/8.1/static/functions-datetime.html",
> Table 9-26. Date/Time Functions). </professorial pontification>

I've looked at that table so often that I figured I knew it from the top
of my head. Guess I was wrong...

Actually, IMO those tables could be a bit more readable if the borders
would be simple 1px lines and some padding is added left and right of
the cell contents.

Well, thanks for the pointers.

--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
    7500 AK Enschede

// Integrate Your World //