Re: querying the age of a row

On 6/7/07, Sean Davis <sdavis2@mail.nih.gov> wrote:
> Lonni J Friedman wrote:
> > On 6/7/07, Sean Davis <sdavis2@mail.nih.gov> wrote:
> >> Lonni J Friedman wrote:
> >> > Greetings,
> >> > I've got a PostgreSQL-8.1.x database on a Linux box.  I have a need to
> >> > determine which rows in a specific table are less than 24 hours old.
> >> > I've tried (and failed) to do this with the age() function.  From what
> >> > I can tell, age() only has granularity down to days, and seems to
> >> > assume that anything matching today's date is less than 24 hours old,
> >> > even if there are rows from yesterday's date that existed less than 24
> >> > hours ago.
> >> >
> >> > I've googled on this off and on for a few days, and have come up dry.
> >> > Someone on a different list suggested that I add a column that get
> >> > now() each time a new row is inserted, but that unfortunately won't
> >> > help me for all the pre-existing rows in this database.
> >> >
> >> > At any rate, is there a reliable way of querying a table for rows
> >> > which have existed for a specific period of time?
> >> >
> >>
> >> So your table has no date or time stored in it at all?  If not, then you
> >> cannot do the query that you are suggesting.
> >
> > It does have a column that is populated with a date/timestamp from the
> > following query:
> > select to_char(current_timestamp, 'MM-DD-YYYY HH24:MI:SS')
>
> So, the column is a text column?  Try these to see if it helps:

oh, and here's the output from your queries:

>
> select now() - interval '24 hours';

nightly=# select now() -interval '24 hours';
?column?
-------------------------------
 2007-06-06 12:17:27.860958-07
(1 row)

>
> select '06-06-2007 23:22:11'::timestamp - interval '24 hours';

nightly=# select '06-06-2007 23:22:11'::timestamp - interval '24 hours';
      ?column?
---------------------
 2007-06-05 23:22:11
(1 row)

>
> select now() - interval '24 hours' < '06-06-2007 23:22:11'::timestamp;

nightly=# select now() - interval '24 hours' < '06-06-2007 23:22:11'::timestamp;
 ?column?
----------
 t
(1 row)


--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
L. Friedman                                    netllama@gmail.com
LlamaLand                       http://netllama.linux-sxs.org