Thread: querying the age of a row
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. At any rate, is there a reliable way of querying a table for rows which have existed for a specific period of time? -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ L. Friedman netllama@gmail.com LlamaLand http://netllama.linux-sxs.org
Interesting issue --
I have usually solved this by adding a specific field to each table with a
default timestamp of NOW()...
When you:
CREATE TABLE tbl (
blah...
blah....
create_dt TIMESTAMP NOT NULL DEFAULT NOW()
);
each and every record now has a timestamp of exactly when the row was
created -- then it is a simple query to select, update, or delete WHERE
create_dt < (NOW() - interval '1 day')...
HTH....
""Lonni J Friedman"" <netllama@gmail.com> wrote in message
news:7c1574a90706071047x773c7085yf0d9f100dbca51da@mail.gmail.com...
> 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.
> At any rate, is there a reliable way of querying a table for rows
> which have existed for a specific period of time?
>
> --
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> L. Friedman netllama@gmail.com
> LlamaLand http://netllama.linux-sxs.org
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>
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. Thanks though. On 6/7/07, codeWarrior <gpatnude@hotmail.com> wrote: > Interesting issue -- > > I have usually solved this by adding a specific field to each table with a > default timestamp of NOW()... > > When you: > > CREATE TABLE tbl ( > > blah... > blah.... > > create_dt TIMESTAMP NOT NULL DEFAULT NOW() > > ); > > each and every record now has a timestamp of exactly when the row was > created -- then it is a simple query to select, update, or delete WHERE > create_dt < (NOW() - interval '1 day')... > > > HTH.... > > > ""Lonni J Friedman"" <netllama@gmail.com> wrote in message > news:7c1574a90706071047x773c7085yf0d9f100dbca51da@mail.gmail.com... > > 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. > > At any rate, is there a reliable way of querying a table for rows > > which have existed for a specific period of time? > > -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ L. Friedman netllama@gmail.com LlamaLand http://netllama.linux-sxs.org
Lonni J Friedman wrote: > 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. Thanks though. > Please don't top-post. Assuming that the majority of the pre-existing rows are more than 24 hours old, there probably wouldn't be any harm in altering the table with the new column and assigning all present rows the timestamp for the moment you do this. Then just wait at least 24 hours before doing whatever it is you wish to do. Not perfect, but it doesn't seem like it would be a problem. brian
Lonni J Friedman escribió: > 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. Thanks though. The answer to your original question is "you can't". That info isn't stored by the database. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
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
John D. Burger wrote: > 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 ...and actually you wouldn't even need the coalesce, as NULL < '24 hours'::interval IS NULL ...which is considered FALSE by the WHERE clause. It's probably wiser not to rely on that in your code though, it can be confusing ;) -- Alban Hertroys alban@magproductions.nl magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World //