question about age()

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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)

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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�

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general