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

Sean