CODEWITHSUNDEEP
phpmysql
user2558771
user2558771

Reputation: 33

comparing datetime is 1 hour ahead

I'm trying to count the rows with a datetime less that 10 minutes ago but the current time its being compared to seems to be 1 hour ahead so Imm getting 0 results, if I go into my table and put some fields forward an hour then I get results.

Getting results:

$stmt = $db->query('SELECT check_log FROM members WHERE check_log >= DATE_SUB(NOW(), INTERVAL 10 MINUTE)');
$row_count = $stmt->rowCount();
echo $row_count.' Members online.';

The datetime of the field of of typing this is 2013-07-11 16:54:12 and I'm getting no results but if I manually change the date time to 2013-07-11 17:54:12 I get 1 result the datetime was input seconds ago using:

$date = date("Y-m-d H:i:s");

The 17:54:12 is my local time and 16:54:12 seem to be my servers time, is my compare trying to look into the future or is it using my local time as a reference?

Upvotes: 0

Views: 1656

Answers (2)

Prasanth
Prasanth

Reputation: 5258

Always store date times in php's timezone.

One function you can particularly make use of is strtotime.

$now = strtotime("now"); // current timestamp
$hour_later = strtotime("+1 hour"); // hour later
$now = date("Y-m-d H:i:s", $now);
$hour_later = date("Y-m-d H:i:s", $hour_later);

Upvotes: 0

tylerl
tylerl

Reputation: 30857

PHP and MySQL don't agree on the current timezone.

Pass the desired time in as a literal from PHP to SQL instead of using NOW().

Upvotes: 2

Related Questions