sending JSON dates to google charts API from Perl

Question

I have a small dancer application which serves up some HTML (including the javascript to call the google charts API) and for other URLs queries a database and returns the data in encoded JSON in a form you can pass to google.visualization.DataTable. The javascript queries the dancer app for the JSON data then passes it into the google charts API - A simplified version is:

    <script type="text/javascript" src="https://www.google.com/jsapi"></script>
    <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.7.1/jquery.min.js"></script>
    <script type="text/javascript">

      google.load('visualization', '1.0', {'packages':['corechart']});
      google.setOnLoadCallback(initialize);

function initialize() {
    var res = $.ajax({
        url: "/data/2",
        dataType:"json",
        async: false,
        data: "{}",
        contentType: "application/json",
        error: function(jqXHR, textStatus, errorThrown) {
             alert('textStatus ' + textStatus);
             alert('errorThrown ' + errorThrown);
        }
    });
    jsonData = res.responseText;
    var data = new google.visualization.DataTable(jsonData);

    var chart = new google.visualization.BarChart(document.getElementById('chart_div'));
    chart.draw(data, {width: 400, height: 240});

}
    </script>

The problem is that some of the data returned by the Perl includes dates/timestamps and so should have the type set to "datetime":

{"rows":[{"c":[{"v":"WHAT_CAN_I_PUT_HERE"},{"v":"2095"}]}],"cols":[{"type":"datetime","label":"DTU"},{"type":"number","label":"COUNT"}]}

In Javascript you could create a date to pass to the google charts API with:

new Date(2012, 1, 08, 09, 32, 0)

How can I send a date encoded in JSON from Perl such that the google charts API understands it? If you cannot what other options might be available to me?

Solution 1

JSON does not support datetime as a data types. But according to Google's documentation, you can just send a string with this format:

JSON does not support JavaScript Date values (for example, "new Date(2008,1,28,0,31,26)"; the API implementation does. However, the API does now support a custom valid JSON representation of dates as a string in the following format: Date(year, month, day[,hour, minute, second[, millisecond]]) where everything after day is optional, and months are zero-based.

Google Chart Tools Datasource Protocol

Solution 2

just to clarify: you should write the cell value as a string that looks like that: Date(year, month). for your example:

{"rows":[{"c":[{"v":"Date(year, month)"},{"v":"2095"}]}],"cols":[{"type":"datetime","label":"DTU"},{"type":"number","label":"COUNT"}]}

hope that helps, and correct for you as i'm using it in php and not perl

Solution 3

I was running into same problem and above solution did not work. After searching for hours I found following post and the solution in there worked.

https://groups.google.com/forum/#!msg/google-visualization-api/SCDuNjuo7xo/ofAOTVbZg7YJ

Do not include "new" in the json string so it will be just: "Date(2012, 1, 08, 09, 32, 0)"

Comments

[bohica]

Thanks, that worked although I could not get it to display the milliseconds.

[José Ramón]

You can't say "above solution did not work". If you read the solutions carefully, you see they suggest to output "Date(year, month, day)" without the "new". So what is wrong with those answers?

[Gary McGill]

It's worth emphasising (because I missed it, and still floundered around for another hour) that the date should not include the new keyword. So: Date(year, month, day) and not new Date(year, month, day).

[Gary McGill]

Ironically despite @JoséRamón 's comment, I had missed the part about omitting the new keyword, and this was the answer that worked for me! +1!

[suzanshakya]

Is there no way to pass timezone information in that string representation?