Posted by David Grant on 01/10/06 15:43

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 Grant
http://www.grant.org.uk/

[Back to original message]