MySql, PHP, dates and times

Over  the weekend I was writing some PHP code and got into all sorts of bother with trying to compare two dates, one from PHP and the other from a database stored field in MySql. What I wanted to do was this: retrieve each record from the database, and check if a certain DateTime field was greater than 20 minutes ago. A standard and easy thing to be trying to do you would think. Not for me it wasn’t.

I do remember having trouble with this kind of thing in the past, and it came back to haunt me again. Now maybe I was just being a bit dumb here, and people may read this post and laugh at me, and go ahead, and then post a better method of doing what I wanted to do.

Anyway, here is what I eventually came up with. I set a PHP variable to be the current time, less 20 minutes:

$twentyMinutesAgo = time() - (60 * 20);

The SQL that retrieves the field from the database, then looks like this:

SELECT UNIX_TIMESTAMP(date_field) AS date_field_timestamp FROM mytable

So now all I have to do is check if the value in date_field_timestamp is greater than that in $twentyMinutesAgo to see if it’s less than 20 minutes ago. Easy.

But this also brings me onto something else, and something that I didn’t know much about more’s the pity, and that’s the vast array of date manipulation functions that come with MySql that you can use in a query, e.g. UNIX_TIMESTAMP. From this simple task I was carrying out, I came across a whole host of useful date and time functions that I have already found highly useful. Why perform extra PHP date and time manipulation when you can get the database to do all the work and return the exact format that you want?

It may be old news to some, but hey, I thought I’d share it with you anyway