CODEWITHSUNDEEP
phpmysqltime
A_Elric
A_Elric

Reputation: 3568

Check if mysql datetime is > 1 hour old using php

Here's the code that I have now:

<?php while($row = mysql_fetch_array( $result )){ ?>
<tr>
    <td><?php echo $row["ID"] ?></td>
    <td><?php echo $row["LOCATION"] ?></td>
    <?php 
        if ($row["STATUS"] != "OK")
        { 
        ?><td bgcolor="red"><?php echo $row["STATUS"] ?></td><?php 
        } else{ 
        ?><td><?php echo $row["STATUS"] ?></td><?php 
        } 
        ?><td><?php echo $row["TIME"] ?></td>
</tr>
<?php } ?>

What I want to do is make another if statement similar to the one that I did for status, but highlight anything older than 1 hour as red. I know that MySql is running in UMT and the server itself is running in EST, and didn't know if that would cause an issue either (as I suck with php).

Upvotes: 0

Views: 3030

Answers (3)

Ja͢ck
Ja͢ck

Reputation: 173652

If the database server is in UTC, you can compare times using strtotime():

$now = time();

while ... {

    if (strtotime("{$row['TIME']} GMT") + 3600 < $now) {
         // it's old, dude!
    }
}

You could also add an expression in your query that determines the age:

`TIME` < DATE_SUB(NOW(), INTERVAL 1 HOUR) AS is_old

Then, in PHP:

if ($row['is_old']) {
    // it's old dude!
}

Upvotes: 1

Nerdwood
Nerdwood

Reputation: 4032

Best way would be to use your SQL query to calculate if it's older than 1 hour, that way you have no "timezone issues".

SELECT *, IF(HOUR(TIMEDIFF(NOW(), DateTimeField)) >= 1, 1, 0) AS OlderThanAnHour

Upvotes: 4

Benjamin Paap
Benjamin Paap

Reputation: 2759

I love to use DateTime for such tasks because often it is more readable than some timestamp calculation. Maybe you like that approach too.

$inOneHour = new DateTime();
$inOneHour->modify('-1 hour');
$time = new DateTime($row['TIME']);

if ($time < $inOneHour) {
    /* your code */
}

Upvotes: 1

Related Questions