UPDATE: Oops. You *did* mention the DBMS, in the title no less. I didn't see that. So this is a little off topic -- sorry about that.

In PostgreSQL:

delete from table where now() - column > interval '60 days';

I wrote a little standalone bash to show that off. It creates 100 days worth of rows, shows some counts, DELETEs with an appropriate interval expression, then counts again:

(careful: it drops table t) 

#!/bin/sh

echo "
drop table if exists t; -- dropping a table, be careful!
create table t 
as
  select d
  from generate_series(
      current_timestamp - interval '99 days'
    , current_timestamp
    , interval '1 day'
  )
     as f(d) ;

-- UPDATE: add this for index tests:
-- create index t_d_idx on t (d);  

select
       count(*)
     , sum( case
            when now() - d > interval '60 days' then 0
            else                                     1
            end
       )    
            as records_to_keep
     , sum( case
            when now() - d > interval '60 days' then 1
            else                                     0
            end
       )
            as records_to_dump
from t;

delete from t where now() - d > interval '60 days'
;

select
       count(*)
     , sum( case
            when now() - d > interval '60 days' then 0
            else                                     1
            end
       )    
            as records_to_keep
     , sum( case
            when now() - d > interval '60 days' then 1
            else                                     0
            end
       )
            as records_to_dump
from t;

" | psql -X

[download]

Output: 

DROP TABLE
SELECT 100
 count | records_to_keep | records_to_dump 
-------+-----------------+-----------------
   100 |              60 |              40
(1 row)

DELETE 41
 count | records_to_keep | records_to_dump 
-------+-----------------+-----------------
    60 |              60 |               0
(1 row)

[download]

In reply to Re: CGI Program To Delete Old Data From MySQL Table? by erix
in thread CGI Program To Delete Old Data From MySQL Table? by Milti

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.