Re: querying the age of a row

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' ;

Sean