Returning JSON arrays with Date types for Google Charts

Question

I have a 'Timelines' chart from Google Charts. It requires a JavaScript Date type when populating with data.

My init code is like this:

var container = document.getElementById('divChart1');
var chart = new google.visualization.Timeline(container);
var dataTable = new google.visualization.DataTable();

dataTable.addColumn({ type: 'string', id: 'Name' });
dataTable.addColumn({ type: 'string', id: 'Category' });
dataTable.addColumn({ type: 'date', id: 'Start' });
dataTable.addColumn({ type: 'date', id: 'End' });

Now I'm trying to populate it via AJAX and the Date types are giving me problems.

The rows need to be populated like so:

dataTable.addRows([
  ['Aaa', 'A', new Date(2014, 1, 1), new Date(2016, 12, 31)],
  ['Bbb', 'B', new Date(2014, 1, 1), new Date(2016, 5, 31)]]);

Is there any way I can return a serialized collection from my AJAX service and just parse it directly, or do I need to be iterating the collection and newing up a JavaScript Date each time?

When I try dataTable.addRows(JSON.parse(result.chartData)); I get the following error: Error: Type mismatch. Value 2015-08-26T11:59:23.889004+02:00 does not match type date in column index 2

For info purposes, this is how it looks on the AJAX service:

List<List<object>> chartData = new List<List<object>>();

chartData.Add(new List<object>() { 
    "Aaa",
    "A",
    DateTime.Now,
    DateTime.Now.AddMonths(3)
});

return JsonConvert.SerializeObject(chartData);

edit: Well I've got it working. Still tweaking but this is the gist:

chartData.Add(new List<object>() { 
    "Aaa",
    "A",
    DateTime.Now.Year + "#" + DateTime.Now.Month + "#" + DateTime.Now.Day,
    DateTime.Now.AddMonths(3).Year + "#" + DateTime.Now.AddMonths(3).Month + "#" + DateTime.Now.AddMonths(3).Day
});

var result = $.parseJSON(result.chartData);
$.each(result, function (k, v) {
    var s = v[2].split('#');
    var e = v[3].split('#');
    dataTable.addRow([v[0], v[1], new Date(s[0], s[1], s[2]), new Date(e[0], e[1], e[2])]);
});

Not going to use this as the official answer because it doesn't answer the question.

Answer 1

Update

While the JSON was created as requested in your original question, it appears JSON.parse('new Date(2014, 1, 1)') does not work because JavaScript date constructors are not strictly valid JSON.

Thus it appears you should serialize your DateTime as a string and pass a reviver function to JSON.parse() that recognizes the date string and constructs a JavaScript Date. For example:

• How to parse JSON to receive a Date object in JavaScript? shows how to create a reviver function that recognizes dates in Microsoft format. To output dates in this format use the setting JsonSerializerSettings.DateFormatHandling = DateFormatHandling.MicrosoftDateFormat.

• For a date string in ISO date format, see the examples of revivers here, here: JavaScript JSON Date Parsing and real Dates, or here: How to use JSON.parse reviver parameter to parse date string.

Original Answer

You could extend JavaScriptDateTimeConverter to write and read dates in the format new Date(2014, 1, 1 [, H [, M [, S [, MS]]]]) (I am extending this answer which only does reading not writing):

public class JavaScriptYMDDateTimeConverter : JavaScriptDateTimeConverter
{
    public bool StripTimeOfDay { get; set; }

    public override void WriteJson(JsonWriter writer, object value, JsonSerializer serializer)
    {
        if (value is DateTime)
        {
            // https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Date
            // Note: Where Date is called as a constructor with more than one argument, the specifed arguments represent local time.
            var date = ((DateTime)value).ToLocalTime();

            writer.WriteStartConstructor("Date");
            writer.WriteValue(date.Year);
            writer.WriteValue(date.Month - 1);
            writer.WriteValue(date.Day);

            if (!StripTimeOfDay)
            {
                var written = date.Date;
                var epsilon = new TimeSpan(TimeSpan.TicksPerMillisecond);

                // Only write hours, min, sec, ms if needed.
                if (date < written - epsilon || date > written + epsilon)
                {
                    writer.WriteValue(date.Hour);
                    written = written.AddHours(date.Hour);
                }

                if (date < written - epsilon || date > written + epsilon)
                {
                    writer.WriteValue(date.Minute);
                    written = written.AddMinutes(date.Minute);
                }

                if (date < written - epsilon || date > written + epsilon)
                {
                    writer.WriteValue(date.Second);
                    written = written.AddSeconds(date.Second);
                }

                if (date < written - epsilon || date > written + epsilon)
                {
                    writer.WriteValue(date.Millisecond);
                    written = written.AddMilliseconds(date.Millisecond);
                }
            }
            writer.WriteEndConstructor();
        }
        else
        {
            // DateTimeOffset
            base.WriteJson(writer, value, serializer);
        }
    }

    public override object ReadJson(JsonReader reader, Type objectType, object existingValue, JsonSerializer serializer)
    {
        Type type = (Nullable.GetUnderlyingType(objectType) ?? objectType);
        bool isNullable = (Nullable.GetUnderlyingType(objectType) != null);

        var token = JToken.Load(reader);
        if (token == null || token.Type == JTokenType.Null)
        {
            if (!isNullable)
                throw new JsonSerializationException(string.Format("Null value for type {0} at path {1}", objectType.Name, reader.Path));
            return null;
        }
        if (token.Type != JTokenType.Constructor)
        {
            throw new JsonSerializationException(string.Format("Invalid Date constructor \"{0}\" at path {1}", token.ToString(), reader.Path));
        }
        var constructor = (JConstructor)token;
        if (!string.Equals(constructor.Name, "Date", StringComparison.Ordinal))
        {
            throw new JsonSerializationException(string.Format("Invalid Date constructor \"{0}\" at path {1}", token.ToString(), reader.Path));
        }

        var values = constructor.Values().ToArray();

        if (values.Length == 0)
        {
            throw new JsonSerializationException(string.Format("Invalid Date constructor \"{0}\" at path {1}", token.ToString(), reader.Path));
        }
        else if (values.Length == 1)
        {
            // Assume ticks
            using (var subReader = constructor.CreateReader())
            {
                while (subReader.TokenType != JsonToken.StartConstructor)
                    subReader.Read();
                return base.ReadJson(subReader, objectType, existingValue, serializer); // Use base class to convert
            }
        }
        else
        {
            var year = (values.Length > 0 ? (int)values[0] : 0);
            var month = (values.Length > 1 ? (int)values[1] : 0) + 1; // c# months go from 1 to 12, JavaScript from 0 to 11
            var day = (values.Length > 2 ? (int)values[2] : 0);
            var hour = (values.Length > 3 ? (int)values[3] : 0);
            var min = (values.Length > 4 ? (int)values[4] : 0);
            var sec = (values.Length > 5 ? (int)values[5] : 0);
            var ms = (values.Length > 6 ? (int)values[6] : 0);

            // https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Date
            // Note: Where Date is called as a constructor with more than one argument, the specifed arguments represent local time.
            var dt = new DateTime(year, month, day, hour, min, sec, ms, DateTimeKind.Local);
            if (type == typeof(DateTimeOffset))
                return new DateTimeOffset(dt);
            return dt;
        }
    }
}

Then use it like:

        var settings = new JsonSerializerSettings { Converters = new JsonConverter[] { new JavaScriptYMDDateTimeConverter { StripTimeOfDay = true } } }; 
        return JsonConvert.SerializeObject(chartData, settings);

Which outputs

[["Aaa","A",new Date(2015,7,26),new Date(2015,10,26)]]