age() and date intervals

From: Max Baker <max(at)warped(dot)org>
To: PostgreSQL General Mailing List <pgsql-general(at)postgresql(dot)org>
Subject: age() and date intervals
Date: 2003-05-07 23:34:12
Message-ID: 20030507233412.GE24817@warped.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Sorry if this is a repost, I don't think my last one went through...

I'm having a problem with age():

Let's compare two queries :

SELECT age(last_discover),last_discover FROM device;
age | last_discover
----------+------------------------
-09:39:31 | 2003-05-02 09:39:31-07
-09:39:47 | 2003-05-02 09:39:47-07
-09:39:51 | 2003-05-02 09:39:51-07
-09:39:56 | 2003-05-02 09:39:56-07
-09:41:11 | 2003-05-02 09:41:11-07
-09:41:14 | 2003-05-02 09:41:14-07
-09:41:36 | 2003-05-02 09:41:36-07

SELECT age(last_discover,now()),last_discover FROM device;

age | last_discover
--------------------------------------+------------------------
-01:11:15.264231 | 2003-05-02 09:39:31-07
-01:10:59.264231 | 2003-05-02 09:39:47-07
-01:10:55.264231 | 2003-05-02 09:39:51-07
-01:10:50.264231 | 2003-05-02 09:39:56-07
-01:09:35.264231 | 2003-05-02 09:41:11-07
-01:09:32.264231 | 2003-05-02 09:41:14-07
-01:09:10.264231 | 2003-05-02 09:41:36-07
-01:09:02.264231 | 2003-05-02 09:41:44-07
-01:08:55.264231 | 2003-05-02 09:41:51-07
-01:08:47.264231 | 2003-05-02 09:41:59-07
-01:08:39.264231 | 2003-05-02 09:42:07-07
-01:08:32.264231 | 2003-05-02 09:42:14-07
-01:08:17.264231 | 2003-05-02 09:42:29-07
-01:08:09.264231 | 2003-05-02 09:42:37-07
-01:07:57.264231 | 2003-05-02 09:42:49-07
-1 mons -25 days -23:54:36.2642310001 | 2003-03-07 09:56:10-08
-2 mons -4 days -00:42:18.264231 | 2003-02-26 09:08:28-08
-2 mons -10 days -22:15:05.264231 | 2003-02-19 11:35:41-08

The second query seems to give me what I want.

Next, I try this query to return old records:

SELECT last_discover,age(last_discover,now())
FROM device
WHERE age(last_discover,now()) > interval '1 day';

Which returns 0 rows.

The Query :

SELECT last_discover,age(last_discover,now())
FROM device
WHERE age(last_discover) > interval '1 day';

Returns :

last_discover | age
-----------------------+-----------------------------------
2003-03-07 09:56:10-08 | -1 mons -26 days -00:03:49.060939
2003-02-26 09:08:28-08 | -2 mons -4 days -00:51:31.060939
2003-02-19 11:35:41-08 | -2 mons -10 days -22:24:18.060939
2003-02-21 10:08:52-08 | -2 mons -8 days -23:51:07.060939
2003-02-24 10:06:21-08 | -2 mons -5 days -23:53:38.060939
2003-02-08 10:12:49-08 | -2 mons -21 days -23:47:10.060939
2003-02-18 09:10:50-08 | -2 mons -12 days -00:49:09.060939
2003-02-25 09:15:02-08 | -2 mons -5 days -00:44:57.060939
2003-03-21 09:09:54-08 | -1 mons -12 days -00:50:05.060939
2003-03-25 09:41:54-08 | -1 mons -8 days -00:18:05.060939
2003-04-02 14:21:47-08 | -29 days -19:38:12.060939
2003-04-15 10:27:20-07 | -17 days -00:32:39.060939
2003-04-22 09:10:10-07 | -10 days -01:49:49.060939
2003-04-23 09:12:20-07 | -9 days -01:47:39.060939
2003-04-26 10:19:18-07 | -6 days -00:40:41.060939
2003-04-26 09:18:01-07 | -6 days -01:41:58.060939

I'm running 7.3.2

Thanks for any help,
Max

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Mike Castle 2003-05-07 23:57:28 Re: qsort (was Re: Solaris)
Previous Message Karsten Hilbert 2003-05-07 23:30:11 Re: LC_COLLATE and index usage