PHP date issues with summer savings
I have a very strange error occurring in some PHP code I have. The page manages student enrollment for courses. The page contains a table of courses for students, and each row has multiple dates: when they signed up, when they graduated, when they took an assessment, and when they signed their certificate.
The table data is generated by PHP (data output from the DB), and Javascript actually renders the table. Exiting PHP is JS code that 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 date fields are stored as fields DATE
(not DATETIME
).
The error is that dates are displayed on one weekend day. I suspect this has a lot to do with the fact that the server is in an area where daylight saving time is, whereas it is not here (which means that the server time is one hour). This explains a lot, especially how the preparation and rendering of data is done in two different time zones. That is: the server tells the client that the person finished at midnight on August 15th, and the client interprets this as 11pm on the 14th and therefore shows it on August 14th.
But here's the confusing part: it only does it for the resultDate and certDate fields! I copied the data to my local server and found that the production server is actually sending a different timestamp (one that's off 1 hour) for these two fields only, whereas the dateEnrolled field is the same.
Here's the output using the 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 figure out if it was a problem where the Daylight Saving function was not counted, but notice how dateEnrolled is the same?
The PHP code that converts MySQL date to unix timestamp is like this:
list ($year, $month, $day) = explode ('-', $mysqlDT);
$timestamp = mktime (0,0,0, $month, $day, $year);
Any ideas on how to fix this?
source to share
This is because you are using locale specific mktime. That is, it converts it to a number of seconds from 00:00:00 to 1970-1-1 GMT, and this is offset by 1 hour with one time zone.
You should also remember that javascript uses the same timezone as the browser, not the webpage.
e.resultDate = new Date(year, month - 1, day);
This ensures that the date is the same for every viewer from every time zone.
Or you can use gmmktime and use UTC methods on Date.
source to share