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.



Menelaos Bakopoulos

Mr. Menelaos Bakopoulos is currently pursuing his PhD both at Center for TeleInFrastruktur (CTiF) at Aalborg University (AAU) in Denmark and Athens Information Technology (AIT) in Athens, Greece. He received a Master in Information Technology and Telecommunications Systems from Athens Information Technology and a B.Sc. in Computer Science & Management Information Systems from the American College of Thessaloniki. Since April 2008 he has been a member of the Multimedia, Knowledge, and Web Technologies Group.

More Posts