Re: querying the age of a row - Mailing list pgsql-novice

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:
> >> > 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:
> >>
> >> select now() - interval '24 hours';
> >>
> >> select '06-06-2007 23:22:11'::timestamp - interval '24 hours';
> >>
> >> select now() - interval '24 hours' < '06-06-2007 23:22:11'::timestamp;
> >
> > All 3 of the above queries do work as expected.
> >
> > Unfortunately, if I port that over to the actual SQL query, i'm back
> > to square one again, as all the returned rows are all dated after
> > midnight (even though its only been about 12 hours since midnight
> > here, and there are definitely rows before midnight which match the
> > criteria):
> >
> > select last_update, subtest, current_status from cudasmoke where
> > (select now() - interval '24 hours' < to_date(date_created,
> > 'MM-DD-YYYY HH24:MI:SS'))='t' ;
>
> A date it just that, a date.  It does not include the time.  Try:
>
> select last_update, subtest, current_status from cudasmoke where (select
> now() - interval '24 hours' < date_created::timestamp))='t' ;

excellent!  that is exactly what I needed.  thanks so much for your help!

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