Skip site navigation (1) Skip section navigation (2)

Re: [GENERAL] Expiration date.

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 (view raw or flat)
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."


In response to

pgsql-general by date

Next:From: amy chengDate: 1999-09-27 09:26:17
Subject: ERROR: nodeRead: Bad type 0
Previous:From: Simon KwanDate: 1999-09-27 05:58:09
Subject: Help: Choose PostgreSQL vs MySQL

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group