Feb 11 2014
JQuery Calendar and PHP: Handling Inserts
1) Getting Current Time in Javascript
The ISO 8601 date format can be utilized to convert the date on the browser side to a format that include timestamp information. Quoting from here:
Note that the “T” appears literally in the string, to indicate the beginning of the time element. Times are expressed in UTC (Coordinated Universal Time), with a special UTC designator (“Z”). Used in ATOM RSS feeds.
function fnISO() { // Only works in Firefox using ECMAScript 5 var now = new Date().toISOString(); alert(now); }
Result: 2009-08-06T23:36:31.390Z
2) Inserting ISO 8601 Date from PHP to MYSQL
ISO 8601 dates (as Strings) can be converted to PHP date objects using strtotime() function.
strtotime($_POST['event_time_as_ISO8601']);
At the beginning of your script I would set both the PHP default timezone, but also the MySQL timezone (for the connection) using the following:
- PHP Command:
date_default_timezone_set('UTC'); - SQL Command:
$this->MySQLi->query("SET timezone = 'UTC'");
3) Serving to Client
Dates will have to be converted to the timezone of the browser for correct viewing.
Again both the PHP and SQL connection timezone should be synchronized. Following the dates can be printed in ISO 8601 format in the JSON document using the PHP date function.
date("c", $raw['start_date']));
4) Converting ISO 8601 date to user timezone (Browser):
All that is required is to parse the ISO 8601 date and create a Date Object. As ISO 8601 contains the timezone (Z if UTC) the correct datetime will be displayed.
One Solution is to the datejs library, as described in Help parsing ISO 8601 date in Javascript .
See Also:
Key Question:
- Are the dates inserted into the database via the browser – using the same time zone as PHP and MySQL?
- Are datetimes being inserted without considering the timezone, leading to offsets and errors?
- Is the browsers time being considered both when inserting but also viewing previous events?
Resources
It is necessary to check which timezone browser is located in, and compensate for this, as well as compensate for browsers viewing events.
Below are resources for dealing with the above mentioned issues related to timestamps on Browsers/PHP/MySQL.
- Best way to handle storing/displaying dates in different timezones in PHP?
- http://www.sitepoint.com/synchronize-php-mysql-timezone-configuration/
- PHP & MySQL: Converting Stored TIMESTAMP into User’s Local Timezone
- http://isambard.com.au/blog/2009/12/10/managing-timezones-with-php-and-javascript/
