FullCalendar Date differs when stored and retrieved from database

Question

I'm using the FullCalendar and my event object's start date is stored into the database with

copiedEventObject.start = date;

I later retrieve it and initialize my calendar with:

        $.ajax({
            type: "POST",
            url: "Default.aspx/getCalendarEventsData",
            data: {},
            contentType: "application/json",
            dataType: "json",
            success: function (msg) {
                // Replace the div's content with the page method's return.

                calendarStoredEvents = $.map(msg.d, function (item) {
                    return {
                        title: item.EventTitle,
                        start: item.start,
                        id: item.EventID,
                        staffID: item.staffID
                    };
                });
                initializeCalendar(calendarStoredEvents);
            }
        });

Where item.start is:

2013-06-30T16:00:00.000Z

The problem now is that. If I stored my event object when it is one Wednesday, it retrieves and is displayed on Tuesday instead. I read up a little and it has something to do with the TimeZone. I have tried adding:

ignoretimezone = false (which failed, then tried toggling it to true)

ignoretimezone = true (which also failed, then tried to add timezone)

currentTimezone: 'Asia/Singapore' (which didn't work as well)

This is the value that is stored in the database:

            drop: function (date, allDay) { // this function is called when something is dropped

                // retrieve the dropped element's stored Event Object
                var originalEventObject = $(this).data('eventObject');

                // we need to copy it, so that multiple events don't have a reference to the same object
                var copiedEventObject = $.extend({}, originalEventObject);

                // assign it the date that was reported
                copiedEventObject.start = date;
                copiedEventObject.allDay = allDay;

                // render the event on the calendar
                // the last `true` argument determines if the event "sticks" (http://arshaw.com/fullcalendar/docs/event_rendering/renderEvent/)
                $('#calendar').fullCalendar('renderEvent', copiedEventObject, true);

                // is the "remove after drop" checkbox checked?
                if ($('#drop-remove').is(':checked')) {
                    // if so, remove the element from the "Draggable Events" list
                    $(this).remove();
                }
                // write to database
                var eventID = copiedEventObject.id;
                var staffID = copiedEventObject.staffID;
                var start = copiedEventObject.start;
                var end = copiedEventObject.end;
                var allDay = copiedEventObject.allDay;

Edit

My website is hosted in Azure, so i guess the time comes from the server:

When I drop an event

( drop: function (date, allDay))

i used alert(start) and it displayed (my client time)

Wed Jul 17 2013 00:00:00 GMT+0800 (Malay Peninsula Standard Time)

I write into the database in my C# code behind:

    string strConnectionString = ConfigurationManager.ConnectionStrings["PCSDB"].ConnectionString;
    SqlConnection conn = new SqlConnection(strConnectionString);
    conn.Open();
    using (SqlCommand sqlCommand = new SqlCommand("INSERT INTO CalendarData (EventID, staffID, start) VALUES (@EventID, @staffID, @start)", conn))
    {
        sqlCommand.Parameters.AddWithValue("@EventID", eventID);
        sqlCommand.Parameters.AddWithValue("@staffID", staffID);
        sqlCommand.Parameters.AddWithValue("@start", start);

        int queryResult = sqlCommand.ExecuteNonQuery();
    }
    conn.Close();

When i check the database the start value becomes

2013-07-16T16:00:00.000Z

EDIT I saved the right date in my database after changing to datetime.

Now i have a problem converting it for the object. I used:

  var parsedDate = $.fullCalendar.parseDate(item.start);
  var formatedDate = $.fullCalendar.formatDate(parsedDate, "dd-MM-yyyy");

Passed in

17/7/2013 12:00:00 AM Becomes 07-05-2014

Matt Johnson-Pint · Accepted Answer

How the time is stored in your database isn't relevant. It matters only how the time is transmitted over your API and into FullCalendar. Assuming the exact string you provided "2013-06-30T16:00:00.000Z" is what is passed into FullCalendar, then the behavior you described makes sense. The UTC time you provided is being adjusted to the user's local time zone.

FullCalendar displays a calendar in the local time zone of the person who is viewing it. There is no setting to view the calendar in UTC, or in any other time zone. There is some information here where they mention being "timezone agnostic". You can think of it this way in terms of how the calendar is drawn, but you will always still have the local time zone when looking at any Date object, because that's how JavaScript works.

You have some options:

Set the ignoreTimezone option to true, and pass your input values in as strings rather than dates.
Pass the values as strings without a time zone offset (remove the Z)
Pass the values as strings with the correct time zone offset for the person viewing the data.
Pass the values as Date objects that have already been adjusted for the local time zone.

The currentTimezone option is not part of FullCalendar. It's in the gcal.js addon, for hooking FullCalender up to Google Calendar over the gcal 1.0 api. It simply passes that parameter to Google as ctz in the querystring. It only affects the way the data is returned from Google. It doesn't do anything in FullCalendar itself.

FullCalendar Date differs when stored and retrieved from database

Answers (2)

Related Questions