Postgres Pro
Downloads
Home   >   mailing lists

Thread: question about age()

question about age()

From
Andreas Kretschmer
Date:
is there a bug in age()?

test=*# select *, age(birthday), age (current_date-1, birthday) from birthday ;
 id |  birthday  |   age   |           age
----+------------+---------+-------------------------
  1 | 2010-08-29 | 3 years | 2 years 11 mons 30 days
(1 row)

Time: 0,322 ms
test=*# select * from birthday where age(birthday) != age (current_date-1, birthday);
 id | birthday
----+----------
(0 rows)

'3 years' != '2 years 11 mons 30 days', but i got 0 rows, why?


I'm using 9.2.4.




Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

Re: question about age()

From
Karsten Hilbert
Date:
On Thu, Aug 29, 2013 at 09:11:07PM +0200, Andreas Kretschmer wrote:

> test=*# select * from birthday where age(birthday) != age (current_date-1, birthday);
>  id | birthday
> ----+----------
> (0 rows)
>
> '3 years' != '2 years 11 mons 30 days', but i got 0 rows, why?

What does

    select age(birthday) = age (current_date-1, birthday) from birthday;

say ?
--
GPG key ID E4071346 @ gpg-keyserver.de
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

Re: question about age()

From
Rob Sargent
Date:
On 08/29/2013 01:11 PM, Andreas Kretschmer wrote:
> is there a bug in age()?
>
> test=*# select *, age(birthday), age (current_date-1, birthday) from birthday ;
>   id |  birthday  |   age   |           age
> ----+------------+---------+-------------------------
>    1 | 2010-08-29 | 3 years | 2 years 11 mons 30 days
> (1 row)
>
> Time: 0,322 ms
> test=*# select * from birthday where age(birthday) != age (current_date-1, birthday);
>   id | birthday
> ----+----------
> (0 rows)
>
> '3 years' != '2 years 11 mons 30 days', but i got 0 rows, why?
>
>
> I'm using 9.2.4.
>
>
>
>
> Andreas
Seems correct to me.
  select current_date, current_date -1  as yesterday,
age('2010-08-28'::date), age(current_date -1, '2010-08-28'::date);
+------------+------------+---------------+---------+
|    date    | yesterday  |      age      |   age   |
+------------+------------+---------------+---------+
| 2013-08-29 | 2013-08-28 | 3 years 1 day | 3 years |
+------------+------------+---------------+---------+
(1 row)
  select version();
+---------------------------------------------------------------------------------------------------------------+
| version                                                    |
+---------------------------------------------------------------------------------------------------------------+
| PostgreSQL 9.3beta2 on x86_64-unknown-linux-gnu, compiled by gcc
(Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit |
+---------------------------------------------------------------------------------------------------------------+
(1 row)

Re: question about age()

From
Michael Nolan
Date:
On 8/29/13, Andreas Kretschmer <akretschmer@spamfence.net> wrote:
> is there a bug in age()?
>
> test=*# select *, age(birthday), age (current_date-1, birthday) from
> birthday ;
>  id |  birthday  |   age   |           age
> ----+------------+---------+-------------------------
>   1 | 2010-08-29 | 3 years | 2 years 11 mons 30 days
> (1 row)
>
> Time: 0,322 ms
> test=*# select * from birthday where age(birthday) != age (current_date-1,
> birthday);
>  id | birthday
> ----+----------
> (0 rows)
>
> '3 years' != '2 years 11 mons 30 days', but i got 0 rows, why?
>
>
> I'm using 9.2.4.


What is the content of the field 'birthday''?  My guess is there's a
null value for the field, in which case you are comparing two nulls.
--
Mike Nolan

Re: question about age()

From
Michael Nolan
Date:
On 8/29/13, Michael Nolan <htfoot@gmail.com> wrote:
> On 8/29/13, Andreas Kretschmer <akretschmer@spamfence.net> wrote:
>
>> I'm using 9.2.4.
>
>
> What is the content of the field 'birthday''?  My guess is there's a
> null value for the field, in which case you are comparing two nulls.

Oops, missed seeing the first half of the post with the data.

It may be a function output type issue.  Modifying the query as
follows works (in 8.2, which I'm still stuck on):

select * from birthday where age(birthday)::text != age
(current_date-1, birthday)::text;
--
Mike Nolan

Re: question about age()

From
Tom Lane
Date:
Andreas Kretschmer <akretschmer@spamfence.net> writes:
> '3 years' != '2 years 11 mons 30 days', but i got 0 rows, why?

Well, actually:

regression=# select '3 years'::interval = '2 years 11 mons 30 days'::interval;
 ?column?
----------
 t
(1 row)

IIRC, interval comparison operators normalize the two values assuming that
1 month = 30 days.  Which is kind of arbitrary, but without some such
assumption there's no way to have a scalar ordering of intervals at all.

            regards, tom lane

Re: question about age()

From
Andreas Kretschmer
Date:
Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Andreas Kretschmer <akretschmer@spamfence.net> writes:
> > '3 years' != '2 years 11 mons 30 days', but i got 0 rows, why?
>
> Well, actually:
>
> regression=# select '3 years'::interval = '2 years 11 mons 30 days'::interval;
>  ?column?
> ----------
>  t
> (1 row)
>
> IIRC, interval comparison operators normalize the two values assuming that
> 1 month = 30 days.  Which is kind of arbitrary, but without some such
> assumption there's no way to have a scalar ordering of intervals at all.

Thanks, okay, make sense. I'm using extract() to extract and compare the
YEAR-field to spot a birthday from the age() ;-)


Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°