Re: querying the age of a row

Lonni J Friedman wrote:

> 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.

And on the suggestion of a timestamp column with DEFAULT NOW():

> Unfortunately, its too late now. The database (and its tables) have
> been around for a while, so even if I added this column, it wouldn't
> help me for the thousands of pre-existing rows.

Er, if you have no such column, what are you testing the AGE() of?
Can you take the age of a row? I can't find that in the docs.

In any event, you say you need to know when a row is less than 24
hours old - that is presumably not an issue for these old rows. I
would add the column as suggested, but set it to some time in the
past for the existing rows. Or, you can set it to NULL,
appropriately if you interpret NULL as unknown, and test the age with
something like this:

where (age(coalesce(ts, '-infinity'::timestamp)) < '24
hours'::interval

- John Burger
MITRE