Compare curent time with db datetime

[zzz]

I need to take datatime entry from db and compare to current time to allow some action if at least 2 hours elapsed.

if ($myDateTime > at least 2 hours) {
// do something
}

[bradgrafelman]

$query = 'SELECT IF(DATE_SUB(NOW(), INTERVAL 2 HOUR) > date_field, 1, 0)
FROM myTable
WHERE something = 5
LIMIT 1';
$exec = mysql_query($query);
$response = mysql_result($exec, 0);

if($response == 1) {
    // 2 hours have elapsed.. do something!
}

[zzz]

Thanks. I was trying to avoid using db query. I already have a value output, can it be formatted and the calculated a difference using just php without involving mysql?

[bradgrafelman]

What do you mean, you already have a value output? Do you mean you already have the date field retrieved from the DB in another query? If so, you could add that if() statement onto the other query, or use [man]time/man in PHP:

if((time() - 60*60*2) > $date_from_db) {

[NogDog]

I might even add an additional field to the original query:

$query = "SELECT `col1`, `col2`, `col3`, IF(DATE_ADD(`date_col`, INTERVAL 2 HOUR) < NOW, 1, 0) AS `old` WHERE . . . ";

Then when operating on a query result row:

while($row = mysql_fetch_assoc($query_result))
{
   if($row['old'])
   {
      // record is > 2 hours old
   }
   else
   {
      // record is < 2 hours old
   }
}

[zzz]

bradgrafelman wrote:

What do you mean, you already have a value output? Do you mean you already have the date field retrieved from the DB in another query? If so, you could add that if() statement onto the other query, or use [man]time/man in PHP:

if((time() - 60*60*2) > $date_from_db) {

Yes I already have the value from db in the following format: 2007-12-04 10:10:10. I rather not modify db query in this case but do it purely with PHP.

So if my $date_from_db = '2007-12-04 10:10:10'; how would I make the string above work? I need to compare date first and then time? Or do I need to convert both to some string format?

[bradgrafelman]

Probably would help to convert it to the same units, yes. Use [man]strtotime/man to convert the date from the DB to a Unix timestamp.

[zzz]

Here's what I came up with:

$myDateTimeString = '2007-12-04 10:10:10'; // some time yesterday

if (strtotime($myDateTimeString) < strtotime("+2 hours")) {
  echo 'Event took place more then 2 hours ago';
}

Please feel free to let me know if I'm wrong with this.

[zzz]

ahh, I think I'm still wrong in my assumptions... perhaps I need to add my time stamp to the second value to give a point of reference...

$myDateTimeString = '2007-12-04 10:10:10'; // some time yesterday 

if (strtotime($myDateTimeString) < strtotime("+2 hours", $myDateTimeString )) { 
  echo 'Event took place more then 2 hours ago'; 
}

Is this correct? because I still do not get expected reslts...

[zzz]

OK, I think I've figured it out:

$sixHrsAgo = time() - (6 * 60 * 60);

if (strtotime($response->cr_resp_posted) > $sixHrsAgo) { ... }

[bradgrafelman]

Or just use the same code I posted above...

if((time() - 60*60*2) > strtotime($date_from_db)) { 

EDIT: Posted at same time.

[zzz]

I had to make a long loop to come to this on my own! It's a confirmation!

[bradgrafelman]

So does that mean your issue is resolved? If so, don't forget to mark this thread resolved.