Re: php + mysql - timstamp - calculate hours remaining

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