|
|
Posted by "M. Sokolewicz" on 01/10/06 16:39
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
Navigation:
[Reply to this message]
|