jquery full calendar displays events next day

Question

I'm trying to integrate the jquery calendar plugin into my custom cms,

My issue is that events are shown the next day that the original value (in database) is set.

This is how i am retrieving my events:

$query = "SELECT id,avatar, titulo AS title,texto as name, unix_timestamp(start_date) as start,unix_timestamp(end_date) as end, start_date, end_date 
              FROM blogs 
              WHERE (unix_timestamp(start_date) >= '$start' OR unix_timestamp(end_date) <= '$end')
                    AND post_type = 'event'
                    AND lan = '$lan'";
    //echo $query;
    $year = date('Y');
    $month = date('m');
    $result = mysql_query($query);
    $array = array();
    $i = 0;
    while ($row = mysql_fetch_array($result)) {
        $raw = $row;
        $raw['url'] = '/blog/'.urls_amigables($raw['title']).'/'.$raw['id'].'/';
        $raw['start_show'] = prettyDateTime($raw['start_date']);
        $raw['end_show'] = prettyDateTime($raw['end_date']);
        $array[$i] = $raw;

        $i++;
    }
    echo json_encode($array);

And this is how i am showing them into the jquery calendar:

$('#calendario').fullCalendar({



                        events: "/includes/json-events.php",

                        eventDrop: function(event, delta) {
                            alert(event.title + ' was moved ' + delta + ' days\n' +
                                '(should probably update your database)');
                        },

                        loading: function(bool) {
                            if (bool) $('#loading').show();
                            else $('#loading').hide();
                        },
                        eventMouseover: function( event, jsEvent, view ) { 
                            var item = $(this);
                            var image = '';
                            if(event.avatar != '')
                                image = '<img src="'+event.avatar+'" />';
                            if(item.find('.nube').length == 0){
                                var info = '<span class="nube"><h2>'+event.title+'</h2>'+image+' <p class="text">'+event.name+'</p><p>'+event.start_show+' <br /> '+event.end_show+'</p><p><a href="'+event.url+'">read_more</a></p></span>';
                                item.append(info);
                            }
                            if(parseInt(item.css('top')) <= 200){
                                item.find('.nube').css({'top': 20,'bottom':'auto'});
                                item.parent().find('.fc-event').addClass('z0');
                            }
                            if(parseInt(item.css('left')) > 500){
                                    item.find('.nube').css({'right': 0,'left':'auto'});
                                    item.parent().find('.fc-event').addClass('z0');
                            }
                            item.find('.nube').stop(true,true).fadeIn();
                            console.log(parseInt(item.css('left')));
                        },
                        eventMouseout: function( event, jsEvent, view ) { 
                            var item = $(this);
                            item.find('.nube').stop(true,true).fadeOut();
                        },
                        header: {
                                    left: 'prev,next today',
                                    center: 'title',
                                    right: 'month,agendaWeek,agendaDay'
                                },
                                eventRender: function(event, element) {

                                }

                    });

The problem here is that unix_timestamp(start_date) would generate the next day in the calendar

(ex: if is stored the start_date in day 17 of the month, in the calendar will appear in the day 18th)

and I'm not sure what i've missed. All this i made it by following their specs...

Any idea where am i failing? (jquery, mysql or timezone settings?)

-EDIT-

I kind of fixed it by

$row['start'] = $row['start'] - 60*60*24 /* One day */;

So now start_date and start make sense together (IN the calendar...)

Please tell me you know a better solution!

Answer 1

According to the chapter MySQL 5.6 Reference Manualp - 12.7 Date and Time Functions

UNIX_TIMESTAMP() assumes that its argument is a datetime value in the current time zone.

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?

Actions/Points:

1. Requires a review of your event insertion code (javascript and PHP).
2. Requires check to see which timezone your server is operating in. This can either be done through a console command (linux) or directly in php with [date_default_timezone_get()] (http://php.net/manual/en/function.date-default-timezone-get.php) .
3. Do we need to detect in which timezone and browser is located as two different viewers may upload the same datetime (but without the timezone component).

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/

Solution

A solution would be to use UTC timestamp for all inserts, and only use a different timestamp for visualization when viewing the events calendar.

1) Getting Current Time in Javascript

The ISO 8601 date format can be utilized to convert your 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.

   1: function fnISO() {
   2:     // Only works in Firefox using ECMAScript 5
   3:     var now = new Date().toISOString();
   4:     alert(now);
   5: }

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

The 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:

• http://anentropic.wordpress.com/2009/06/25/javascript-iso8601-parser-and-pretty-dates/