Обсуждение: 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

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 BerkusAglio Database SolutionsSan Francisco

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.