PHP date issues with daylight saving

Question

I've got a very strange bug cropping up in some PHP code I've got. The page is managing student enrolments in courses. On the page is a table of the student's courses, and each row has a number of dates: when they enrolled, when they completed, when they passed the assessment and when they picked up their certificate.

The table data is generated by PHP (drawing the data from the DB), and Javascript actually renders the table. The output from PHP is JS code which looks something like this:

var e = new Enrolment();
e.contactId = 5801;
e.enrolId = 14834;
e.courseId = 3;
e.dateEnrolled = new Date(1219672800000);
e.dateCompleted = new Date(-1000);  // magic value meaning they haven't completed.
e.resultDate = new Date(1223647200000);
e.certDate = new Date(1223560800000);
e.result = 95;
e.passed = true;
enrolments[14834] = e;

In the database, all the date fields are stored as DATE (not DATETIME) fields.

The bug is that the dates are being displayed as one day off. I would suspect that this has a lot to do with the server being in an area which has daylight saving, whereas here there isn't any (meaning the server time is one hour off). This explains a lot, especially how the data preparation and rendering is being done in two different timezones. That is: the server is saying to the client that the person completed at midnight on the 15th August, and the client is interpreting that as 11pm on the 14th and therefore displaying 14th August.

But here's the confusing part: it's only doing that for the resultDate and certDate fields! I've copied the data to my local server and have found that the production server is actually sending a different timestamp (one which is off by 1 hour) just for those two fields, whereas the dateEnrolled field is the same.

Here's the output using the exact same code and data from the database:

// local server (timezone GMT+1000)
e.dateEnrolled = new Date(1219672800000);   // 26 Aug 2008 00:00 +10:00
e.dateCompleted = new Date(-1000);
e.resultDate = new Date(1223647200000);     // 11 Oct 2008 00:00 +10:00
e.certDate = new Date(1223560800000);       // 10 Oct 2008 00:00 +10:00

// production server (timezone GMT+1100)
e.dateEnrolled = new Date(1219672800000);   // 26 Aug 2008 00:00 +10:00
e.dateCompleted = new Date(-1000);
e.resultDate = new Date(1223643600000);     // 10 Oct 2008 23:00 +10:00 **
e.certDate = new Date(1223557200000);       // 09 Oct 2008 23:00 +10:00 **

I can understand if this was a problem with Daylight Saving not being accounted for, but notice how the dateEnrolled is the same?

The PHP code which converts the MySQL date to a unix timestamp is this:

list ($year, $month, $day) = explode ('-', $mysqlDT);
$timestamp = mktime (0,0,0, $month, $day, $year);

Any ideas about how to fix this?

Answer 1

Ok, I just figured out why it's mucking up one date but not the other. Daylight savings wasn't in effect in August. facepalm

Answer 2

1. always store dates/datetimes in GMT/UTC
2. take a good look at the query that retrieves these values, anything different about the ones being adjusted?
3. if not, are they all timestamp or date or datetime?

Answer 3

It is mosly likely to be day light saving issue. The reason why it doing it only for resultDate and certDate is that dateEnrolled is in August, daylight saving normally begins/ends in late September or early October.

Answer 4

Set the date.timezone ini setting to your app's timezone, using apache.conf, .htaccess or ini_set().

Answer 5

Thats because you use mktime which is locale specific. That is it will convert it to the number of seconds from 00:00:00 1970-1-1 GMT, and that is offset by 1 hour with one timezone.

You should also remember that the javascript does use the same timezone as the browser, not the web page.

e.resultDate = new Date(year, month - 1, day);

This will make sure the date is the same for every viewer from every timezone.

Or you can use gmmktime and use the UTC methods in Date.