Postgres Pro
Downloads
Home   >   mailing lists

Thread: Best way to delete time stamped data?

Best way to delete time stamped data?

From
Kyle
Date:
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

Re: Best way to delete time stamped data?

From
Steve Crawford
Date:
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

Re: Best way to delete time stamped data?

From
Josh Berkus
Date:
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

Re: Best way to delete time stamped data?

From
Rajesh Kumar Mallah
Date:

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.

Re: Best way to delete time stamped data?

From
Bruno Wolff III
Date:
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.