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
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
