On Sun, 26 Sep 1999, Michal A. Kowalski wrote:
> Hi,
>
> what is the easiest way to check if record in database has expired? I have
> records with field CREATED - filled with datetime('now'::abstime) function -
> and with field EXPIRE - filled with number of days for which this record
> remain valid. Is there any way to add days in EXPIRE to CREATED in proper way
> and chceck if this particular record should be deleted using functions
> embedded in PgSQL or maybe I should do this outside Pg, maybe in Perl?
caffeine=> create table tmp ( expdays int4 not null, createdate datetime
not null);
...
caffeine=> select * from tmp;
expdays|createdate
-------+----------------------------
30|Mon Sep 27 02:28:16 1999 EST
3|Mon Sep 27 02:32:25 1999 EST
30|Sat Aug 28 02:34:49 1999 EST
30|Thu Jul 29 02:34:52 1999 EST
(4 rows)
caffeine=> select createdate,expdays, (createdate + (expdays ||
'days')::timespan)::datetime as expire from tmp;
createdate |expdays|expire
----------------------------+-------+----------------------------
Mon Sep 27 02:28:16 1999 EST| 30|Wed Oct 27 02:28:16 1999 EST
Mon Sep 27 02:32:25 1999 EST| 3|Thu Sep 30 02:32:25 1999 EST
Sat Aug 28 02:34:49 1999 EST| 30|Mon Sep 27 02:34:49 1999 EST
Thu Jul 29 02:34:52 1999 EST| 30|Sat Aug 28 02:34:52 1999 EST
(4 rows)
caffeine=> select * from tmp where (createdate + (expdays || 'days')::timespan)::datetime < NOW ();
expdays|createdate
-------+----------------------------
30|Sat Aug 28 02:34:49 1999 EST
30|Thu Jul 29 02:34:52 1999 EST
(2 rows)
like that ?
---
Howie <caffeine@toodarkpark.org> URL: http://www.toodarkpark.org
"Just think how much deeper the ocean would be if sponges didn't live there."