Re: age() and date intervals

No problem - check the docs at:
http://www14.us.postgresql.org/users-lounge/docs/7.2/postgres/functions-datetime.html

1 argument: age(timestamp) = subtract timestamp from today
2 arguments: age(timestamp, timestamp) = difference between the two timestamps

Pick the one you need or just subtract:
now()-last_discover > '1 day'::interval

Cheers,
Steve


On Wednesday 07 May 2003 4:34 pm, Max Baker wrote:
> 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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly