From: | Howie <caffeine(at)toodarkpark(dot)org> |
---|---|
To: | "Michal A(dot) Kowalski" <mak(at)minfo(dot)com(dot)pl> |
Cc: | pgsql-general(at)postgreSQL(dot)org |
Subject: | Re: [GENERAL] Expiration date. |
Date: | 1999-09-27 06:42:00 |
Message-ID: | Pine.LNX.3.96.990927062538.29855N-100000@rabies.toodarkpark.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(at)toodarkpark(dot)org> URL: http://www.toodarkpark.org
"Just think how much deeper the ocean would be if sponges didn't live there."
From | Date | Subject | |
---|---|---|---|
Next Message | amy cheng | 1999-09-27 09:26:17 | ERROR: nodeRead: Bad type 0 |
Previous Message | Simon Kwan | 1999-09-27 05:58:09 | Help: Choose PostgreSQL vs MySQL |