I have searched and searched for ways to do this but have found very limited information.

I have a MySQL table ‘msgdb’ that contains a field ‘ttime’ that is in the format double(25,8) (example row = 1352899856.95249200).

I need to routinely cleanup the table by removing any rows where the field ‘ttime’ is <= today’s date -5 days.

These are the only two lines of code I could find related to double to time conversion but cannot get either to work.

SELECT ADDDATE(ADDDATE(ADDDATE('1899-12-31 00:00:00',FLOOR(ttime)), INTERVAL -1 DAY),INTERVAL(MOD(ttime,1)*86400)SECOND) AS TrueDate FROM msgdb

select date('1899-12-31 00:00:00'+ INTERVAL ttime * 24*3600 SECOND) as date from msgdb

I have tried first to display any rows that match the criteria using the code below, before I started using DELETE FROM to make sure I’m getting the correct results.

 $query = "select date('1899-12-31 00:00:00'+ INTERVAL ttime * 24*3600 SECOND) as date from msgdb";
 $result = mysql_db_query ($dbname, $query, $link);
 while($row = mysql_fetch_array($result)) {
   echo $row['date'];
   echo '<br>';
 }

and also

 $query = "SELECT ADDDATE(ADDDATE(ADDDATE('1899-12-31 00:00:00',FLOOR(ttime)), INTERVAL -1 DAY),INTERVAL(MOD(ttime,1)*86400)SECOND) AS TrueDate FROM msgdb";
 $result = mysql_db_query ($dbname, $query, $link);
 while($row = mysql_fetch_array($result)) {
   echo $row['TrueDate'];
   echo '<br>';
 }

but both are returning nothing.

UPDATE: Ok so by using this code:

$query = "select ttime from msgdb";
$result = mysql_db_query ($dbname, $query, $link);
while($row = mysql_fetch_array($result)) {
        echo date('m-j-Y, H:i:s', $row[0]);
    echo '<br>';
}

I am able to see it convert ‘ttime’ field from the stored value of 1352899856.95249200 to 11-14-2012, 07:30:56.

So how would I DELETE from the table all rows where ttime is <=now – 5 days?