Best way to delete time stamped data?
Hello. I have a table that contains time stamped data. To delete rows
that are over 90 days old, I do this:
DELETE FROM ONLY richtable WHERE trxdate < current_timestamp - interval
'90 days';
This works great. But I was wondering if there is a better or more
compact way to go about it. (this method feels "clunky" to me)
Thanks.
-Kyle
I'm usually lazy and let automatic casting work for me:
DELETE FROM ONLY richtable WHERE now()-trxdate > '90 days';
Cheers,
Steve
Show quoted text
On Friday 02 May 2003 3:06 pm, Kyle wrote:
Hello. I have a table that contains time stamped data. To delete rows
that are over 90 days old, I do this:DELETE FROM ONLY richtable WHERE trxdate < current_timestamp - interval
'90 days';This works great. But I was wondering if there is a better or more
compact way to go about it. (this method feels "clunky" to me)Thanks.
-Kyle
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
Kyle,
DELETE FROM ONLY richtable WHERE trxdate < current_timestamp - interval
'90 days';This works great. But I was wondering if there is a better or more
compact way to go about it. (this method feels "clunky" to me)
More compact than that ?!?!?!
Maybe you should go into Perl, instead of SQL -- I think the one-liners would
suit you.
You can use the age() function, but all that funciton does is the same
calculation you have above.
--
-Josh Berkus
Aglio Database Solutions
San Francisco
Josh,
Thanks, even my boss can read the new statement:
DELETE FROM ONLY richtable WHERE age(trxdate) > '90 days';
Your disparaging aside, that is exactly the kind of expression
compaction I was after! :)
And yeah, I have a deep PERL background, but that's not a bad thing.
-Kyle
Josh Berkus wrote:
Show quoted text
More compact than that ?!?!?!
Maybe you should go into Perl, instead of SQL -- I think the one-liners would
suit you.You can use the age() function, but all that funciton does is the same
calculation you have above.
On Sun, May 04, 2003 at 09:09:05AM -0400, Kyle wrote:
Josh,
Thanks, even my boss can read the new statement:
DELETE FROM ONLY richtable WHERE age(trxdate) > '90 days';
Just remember that that query won't use any index on trxdate, because
it has to calculate 'age(trxdate)' for every value in the table.
--
Jim C. Nasby (aka Decibel!) jim@nasby.net
Member: Triangle Fraternity, Sports Car Club of America
Give your computer some brain candy! www.distributed.net Team #1828
Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"
The index can be used with a little rewrite (assuming trxdate is
indexed):
DELETE FROM ONLY richtable WHERE trxdate < current_timestamp - '90
days'::interval;
"Jim C. Nasby" wrote:
Show quoted text
On Sun, May 04, 2003 at 09:09:05AM -0400, Kyle wrote:
Josh,
Thanks, even my boss can read the new statement:
DELETE FROM ONLY richtable WHERE age(trxdate) > '90 days';
Just remember that that query won't use any index on trxdate, because
it has to calculate 'age(trxdate)' for every value in the table.
--
Jim C. Nasby (aka Decibel!) jim@nasby.net
Member: Triangle Fraternity, Sports Car Club of America
Give your computer some brain candy! www.distributed.net Team #1828Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
an index on age(date(trxdate)) would make it faster ?
regds
mallah.
On Saturday 03 May 2003 4:49 am, Josh Berkus wrote:
Kyle,
DELETE FROM ONLY richtable WHERE trxdate < current_timestamp - interval
'90 days';This works great. But I was wondering if there is a better or more
compact way to go about it. (this method feels "clunky" to me)More compact than that ?!?!?!
Maybe you should go into Perl, instead of SQL -- I think the one-liners
would suit you.You can use the age() function, but all that funciton does is the same
calculation you have above.
--
Rajesh Kumar Mallah,
Project Manager (Development)
Infocom Network Limited, New Delhi
phone: +91(11)6152172 (221) (L) ,9811255597 (M)
Visit http://www.trade-india.com ,
India's Leading B2B eMarketplace.
On Mon, May 05, 2003 at 15:26:21 +0530,
Rajesh Kumar Mallah <mallah@trade-india.com> wrote:
an index on age(date(trxdate)) would make it faster ?
That won't work because the value returned by age depends on the time
the transaction started. If you want the delete to be indexed you
need to write it out the longer way.