How to plot dates in Google Charts API

The Google Charts API is great but it can’t handle date-based data. Lets say you want to plot some values over a timespan, how do you do it? One way is to sample the data at regular time intervals and plot it as normal. But if the timestamps you want to plot are not regular, the trick is to convert each date into a day offset and plot the offset whilst still labelling the axis with the date text.

This is an example of plotting date-based data on the X-axis:

In, PHP you set the first date as 0 and then work out the day difference for each subsequent date. At the same time you need to store the text label for that date.

<html><body>
<?
   $dataSet["20 January 2008"]  = 12;
   $dataSet["20 February 2008"] = 22;
   $dataSet["20 January 2009"]  = 52;
   $dataSet["20 March 2009"]    = 32;
   $dataSet["20 June 2009"]     = 72;

   $firstDate = "";
   $googleChartDataY = "";
   $googleChartDataX = "";
   $maxDay = 0;

   foreach($dataSet as $date => $data):
           if ($firstDate == "")
              $firstDate = strtotime($date);

           $dayDiff = round((strtotime($date)-$firstDate) / (60 * 60 * 24));

           if ($dayDiff > $maxDay)
              $maxDay = $dayDiff;

           $googleChartDataX .= $dayDiff . ",";
           $googleChartDataY .= $data. ",";
           $googleChartLabsX .= "|". date("d/M/y",strtotime($date));
   endforeach;

   $axisScale    = "&chds=0,$maxDay,".min($dataSet).",".max($dataSet);
   $axisLabels   = "&chxt=x,y,x,y&chxl=0:{$googleChartLabsX}|1:|".min($dataSet)."|".((min($dataSet)+max($dataSet))/2)."|".max($dataSet)."|2:||Time scale||3:||Data values|";
   // Display a red line with a black dot at each point
   $dataLabels   = "&chm=D,FF0000,0,-1,1|o,000000,0,-1,4";
   $dataPoints   = "&chd=t:".substr($googleChartDataX,0,-1)."|".substr($googleChartDataY,0,-1);

   echo "<img src="http://chart.apis.google.com/chart?&cht=lxy&chs=400x150&chg=0,10{$axisScale}{$axisLabels}{$dataLabels}{$dataPoints}" />";
?>
</body></html>

I created a small WordPress plugin that uses the above code to graph custom field data over time.

4 responses to “How to plot dates in Google Charts API”

  1. KarlP

    Having some similar issues with this myself, and while you’ve got the values plotted along the x-axis correctly, have you noticed that your labels are not? Per the google api, they are placed evenly from start to finish.

    It’s actually more complicated than you’d think, as you have to create all the intermediary values for the x-axis, making sure that there are corresponding blanks in the y-data, but still having labels.

    I’d post my solution, but I’m still working on it unfortunately :(

  2. Danysworld

    Hi!
    I saw your plugin but I can’t get out of it :( It does not work :( How can I have (like you) a list with “weight(kgs)”. In that list, I only have “views” and it does something weird :( If it is possible, I can modify the code but I need some help to do it in the right place :)

    Hope to hear from you soon because I would so much use you plugin in my blog :)
    Take Care

  3. Greg Fitzgerald

    I have another solution for charting dates. The problem with caluclating differences from the first date, is it works fine when the data points are evenly spaced time intervals and tehre is no gaps in the data, otherwise the data points do not line up with the labels.

    This solution may not work under all scenarios.
    Instead, know the range of dates you are plotting, and put in a fixed number of labels. and base the data point value on the nuber of pixels per label. e.g.
    I’m charting 24 hours from midnight through to midnight, therefore i have 25 labels, i.e. 00:00 to 24:00, showing hourly intervals. Each graph will always have 25 labels and 24 spaces between them as google evenly spaces the lables along the axis.
    I make the axis 1000 pixels long, therefore each space btween the labels is 1000/24 pixels long.
    The first data point 00:00 is plotted as zero pixels across, the last data point 24:00 is = (1000/24) * 24 pixels across.
    general formula is for each data point extract the number of hours and minutes from each timevalue associated with that data point and worked out the axis value with
    (hrs * 1000/24) + (mins/60 * 1000/24).

  4. Hansinator

    Hi. I am running a personal training blog/diary and your WP plugin was very valuable and interesting. I am tracking a graph over “records”, that is, the time i use on different routes. Unfortunately, the plugin stalls when i enter too many dates and the site wont even show up. Any smart solutions?

Leave a Reply

%d bloggers like this: