Many Thanks for the help. OP 2 worked a treat.. now i need to run it at midnight, I was going to write a php script and call it vai crontab, but how could I iplament this a triger and stored procedure ? On 1/10/06, M. Sokolewicz <tularis@xxxxxxx> wrote: > > David Grant wrote: > > Gregory, > > > > David Grant wrote: > > > >>Gregory Machin wrote: > >> > >>>I have a table with a timestamp column and would like to use his to > >>>calculate the age of the record . how would i go about this... > >>>I would also like to exicute a mysql stament that pasess the tables and > >>>removes fields older than say 72 hours. how would i go about this . ? > >> > >>A timestamp is the time in seconds (since Jan 1 1970), so you can > >>ascertain the age in seconds by subtracting the stored timestamp from > >>the current timestamp. > >> > >>You can find the current timestamp in MySQL using the > >>CURRENT_TIMESTAMP() function. > >> > >>Once you have the age of the record, finding 72 hours is fairly trivial > >>- 72 hours is 259200 seconds (72hrs * 60mins * 60secs). > >> > >>Therefore your query will be: > >> > >>DELETE FROM <TABLE> WHERE CURRENT_TIMESTAMP() - <FIELD> > 259200 > > > > > > Following Albert's reply, the query ought to read: > > > > DELETE FROM <TABLE> WHERE CURRENT_TIMESTAMP() - UNIX_TIMESTAMP(<FIELD> > > > 259200 > > > > David > David, your solution is correct (though you made a typo; it should read: > "DELETE FROM <TABLE> WHERE CURRENT_TIMESTAMP() - UNIX_TIMESTAMP(<FIELD>) > > 259200"), though slow because mysql needs to calculate a timestamp, > based on all the times/dates stored. Now, all these methods depend on > how the OP stored the dates in his database. In general, there are 2 > ways which are used most often: > 1 - As a UNIX timestamp (as per Albert's example), or > 2 - As a MySQL-timestamp (as per ISO 8601; YYYY-MM-DD) > > If the OP uses #1 to store the date, the easiest way is to do a > modification of David's solution, and do: > "DELETE FROM <TABLE> WHERE <field> < UNIX_TIMESTAMP(NOW())-259200", this > is a quick solution, because all MySQL now needs to do is compare an > integer to an integer for each row (which is pretty fast). > If the OP uses #2, then it's easier to use MySQL's built-in functions > for date-comparison. It's actually faster than converting the dates to > UNIX timestamps and comparing them as per #1, so let's do that. To > achieve this, you can use mysql's own date functions: > "DELETE FROM <TABLE> WHERE <field> < NOW()- INTERVAL 72 HOUR" > > hope this short discussion helped, > - tul > -- Gregory Machin greg@xxxxxxxxxxxxxx gregory.machin@xxxxxxxxx www.linuxpro.co.za www.exponent.co.za Web Hosting Solutions Scalable Linux Solutions www.iberry.info (support and admin) +27 72 524 8096
