在PHP和mysql中将DATETIME与time（）进行比较

Question

I would like to check how many users are online on a certain moment on a single query. The thing is I have the time kept as DATETIME (in the online field) and I would like to compare it with time(). Here is the code:

$online_margin = 10; //in minutes;

$online_margin = $online_margin*60;
$difference = time() - $online_margin;

$query = "SELECT id FROM users WHERE online > $difference";

I've tried using convert() and cast but failed, so I'd appreciate some help on why...

Answer 1

You need to convert it to Y-m-d h:i:s format before comparing it to db field,

$online_margin = 10; //in minutes;

$online_margin = $online_margin*60;
$difference = time() - $online_margin;
$difference  = date("Y-m-d h:i:s",$difference); //Convert seconds to Y-m-d h:i:s format
$query = "SELECT id FROM users WHERE online > $difference";

Alternative: You can convert your column online to unix_time for comparing,

$query = "SELECT id FROM users WHERE UNIX_TIMESTAMP(online) > $difference";