Timestamp comparison in MySQL and PHP ( UTC, +0000, Timezones )

Question

I'm trying to determine whether a string that represents the date and time, given in a JSON Twitter feed is within a range of timestamp columns in MySQL.

Here's the example string:

'Sat, 31 Oct 2009 23:48:37 +0000',

The +0000 according to the API ( created_at ) indicates it is indeed UTC. Now, I'm using strtotime and date just to confirm the time. With:

$t = 'Sat, 31 Oct 2009 23:48:37 +0000';
$timestamp = strtotime($t);

echo date('M d Y H:m:s', $timestamp);

I get Oct 31 2009 19:10:37. If I remove the +0000 I get Oct 31 2009 23:10:37. So the difference between having +0000 and not having it is 4 hours. I'm guessing because of my local timezone ( Maryland, USA = America/New_York ) and that differing from the UTC obviously.

I'm not quite sure if I should be stripping the +0000 or using it when trying to determine if this timestamp is within the range of the two timestamps stored in my database, which are 2009-10-30 23:16:38 and 2009-11-25 12:00:00. I feel silly and a bit confused now, when I populated these timestamps the YYYY-MM-DD H:M:S came from a Javascript date time picker, an example format is 10/31/2009 11:40 am and I use STR_TO_DATE like so:

STR_TO_DATE("10/31/2009 11:40 am", "%m/%d/%Y %l:%i %p")'),

Should I leave the +0000 or strip it? Mentally taps out

Answer 1

In PHP you can simply use the substring function to break down the json twitter time into its components as so

//'Sat, 31 Oct 2009 23:48:37 +0000' $hour = substring($jsontime,18,2); $minute = substring($jsontime,22,2); ...

$phpDatetime mktime($hour,$minute,$second,$month,$day,$year);

From there I think you already have it. Dont' forget to adjust for GMT differences.

Answer 2

You should of course leave the timezone information in, provided you're also properly setting the server timezone. Otherwise what's the point, all your time comparisons will be 4 hours off. :o)

To compare the time you should leave it as UNIX timestamp, i.e. the result of strtotime.

$twitterTS    = strtotime('Sat, 31 Oct 2009 23:48:37 +0000');
$localStartTS = strtotime('Sat, 31 Oct 2009 19:00:00'); // timezone is -0400 implicitly
$localEndTS   = strtotime('Sat, 31 Oct 2009 20:00:00');

if ($localStartTS <= $twitterTS && $twitterTS <= $localEndTS) {
    // twitter timestamp is within range
}

To clarify: Before comparing times from different timezones, make sure they're all converted to the same timezone. Comparing London time 20:00 to New York time 20:00 without timezone information will yield incorrect results. strtotime will convert all times to your local timezone; if timezone information is present in the input it will honor it and convert the time appropriately, otherwise it'll assume the time is already localized. If all the times in your database are local, you should absolutely make sure to localize all timestamps you want to compare against them.
An alternative strategy would be to always convert all times to UTC before storing or comparing them.

Take your pick, just do so consistently.