Plant Wars

I learn something every time I look at the MySQL Reference Manual: Date and Time Functions.  I should really look at it more often.

Too often – always – I do not have enough time to spend time adding new features to Plant Wars.  Between having a full time day job and a baby at home, I’m a little pressed for time. That means that as soon as I come up with a way to do something, I hit the ground running. This means I typically end up with very sub-optimal solutions.

As a rather embarrassing example, consult the following code snippet I was using to determine if a user had been active in the past 10 minutes. Any time a user is active, the LastActive attribute in the Users table is updated. Here, this value has been stored in the $lastActive variable.

$lastActive = explode(" ", $lastActive);
$date = explode("-", $lastActive[0]);
$time = explode(":", $lastActive[1]);
$online = false;
if (date("d") == $date[2] && date("m") == $date[1] && date("Y") == $date[0]) { // user was active today
   if (date("H") == $time[0] && date("i") <= $time[1]+10) { // user was active in last 10 minutes - MODIFY TO CHECK IF USER WAS ACTIVE IN LAST 10 MINUTES EVEN THOUGH IT WAS THE PREVIOUS HOUR OF THE DAY
      $online = true;
   }
}

As you can see, even doing it in this ridiculously drawn out way, it still would mess up if, for example, a user had been inactive at 4:59 and it is now 5:01.

After consulting the MySQL date functions again, I have come up with a much more eloquent (and sensible) solution:

	$query = "SELECT *, NOW()<DATE_ADD(LastActive, INTERVAL 10 MINUTE) AS Active FROM Users WHERE Id='".$id."'";
	$result = mysql_query($query);
	$row = mysql_fetch_assoc($result);

Now to test if a user has been active in the past 10 minutes, I just check if ($row["Active"]).

To explain, NOW() returns the current date and time. DATE_ADD(date, INTERVAL expression unit) adds the specified amount to the date. Don’t use plural units (MINUTE is correct, MINUTES is not).

To summarize, it is best to do your research into the capabilities your available tools, as it will often save you time, make your application faster, your code more readable, and just generally be a better solution.

Update 3/3/2009

As wwosik pointed out on reddit, this is an acceptable solution for determining if a single user has been active in the past 10 minutes. But what if you want to find all active users? It is costly to have MySQL execute the date functions for every row when it is unnecessary.

To solve this problem, we need to calculate the date/time 10 minutes in the past and then we can just store this value and compare the LastActive attribute of each row to it, without needing to calculate it for every user.

To do this, I use the mktime() and date() functions.

// int mktime ([ int $hour= date("H") [, int $minute= date("i") [, int $second= date("s") [, int $month= date("n") [, int $day= date("j") [, int $year= date("Y") [, int $is_dst= -1 ]]]]]]] )
$activeThresholdUnix = mktime(date("H"), date("i")-10, date("s"), date("m"), date("d"), date("Y"));
// Since mktime returns the Unix timestamp, we need to convert it into something comparable with the SQL timestamp
$activeThreshold = date("Y-m-d H:i:s", $activeThresholdUnix);
$query = "SELECT Id, Name, Level FROM Users WHERE LastActive>'".$activeThreshold."' ORDER BY Level DESC";

There is probably a better way to calculate the time 10 minutes ago, but this is certainly a step in the right direction; it is much more efficient than performing unnecessary calculations for every row.