Get Timestamp from Database

Question

I have data stored in a db table, with an auto timestamp. In a module I want to then display a result based on the latest timestamp.

It's not working and I'm getting an error

Warning: strtotime() expects parameter 1 to be string

The query I've written is:

//Get Latest Update TimeStamp
$db = JFactory::getDBO();
$query = "SELECT MAX( timestamp )  
          FROM #__service_status ORDER BY timestamp DESC LIMIT 1";
$db->setQuery( $query);
$timestamp = strtotime($db);

Then I'm trying to echo it with:

<?php echo date("m-d-Y", $timestamp);?>

Answer 1

You need to add this in your code, after $db->setQuery( $query);

$db->setQuery( $query);
$var1= $db->loadResult();
$timestamp = strtotime($var1);

Hope this help

Answer 2

MySQL already provides the appropriate tools to reformat your timestamp value, so I strongly recommend that you wrap DATE_FORMAT() around your MAX() call.

SELECT DATE_FORMAT(MAX(timestamp), '%m-%d-%Y') AS `formatted` FROM `service_status`

SQLFiddle Demo

MAX() provides a more succinct query versus ORDER BY timestamp DESC LIMIT 1 which will also provide the desired output.

SELECT DATE_FORMAT(timestamp, '%m-%d-%Y') AS formatted FROM `service_status` ORDER BY timestamp DESC LIMIT 1

The take-away lessions are:

1. You don't need the redundant logic of MAX() and ORDER BY timestamp DESC LIMIT 1.
2. You can spare a couple of php date function calls (namely: strtotime() & date()) by reformatting in your query.

The final baked product can look like this:

$db = JFactory::getDBO();
try {
    $query = $db->getQuery(true)
                ->select("DATE_FORMAT(MAX(timestamp), '%m-%d-%Y')")
                ->from("#__service_status");
    // echo $query->dump();  // uncomment if you want to see what is generated
    $db->setQuery($query);
    if (!$result = $db->loadResult()) {  // declare $result then check for a "falsey" value
        echo "No Rows Found";
    } else {
        echo "Last update was $result";
    }
} catch (Exception $e) {
    echo "Syntax Error"; // , $e->getMessage();  // <- don't show these details publicly
}

*since there is only a single value returned, I have removed the column alias that I first mentioned in my raw SQL query -- loadResult() doesn't care what the field is called.

*timestamp is a mysql KEYWORD but it is not a RESERVED KEYWORD so it doesn't need to be wrapped in backticks.