Using flot to chart data using a date range

jquery flot chart I’m working on developing Bloglines these days and one of the features I wanted as an admin was the ability to see various pieces of data related to the site over time. Ideally what I wanted were some charts like those on $g(Google Analytics) for things like:

  • users joining/day
  • votes cast/day
  • blogs submitted per day

I’ve previously experimented with the $l(Google Chart Tools) and that’s where my search started but that lead to a bit of a dead end. I found a related post on Tom Fotherby’s blog but as Greg Fitzgerald pointed out there are still a few more issues to be worked out. Since we use jQuery on the site I started searching along that vein which led me to flot and more specifically this example (caution the examples site seems really slow) which fit perfectly.

Looking at the code and markup I quickly found this was something I could have working right away. The data in JavaScript looks like this:

var d = [[1196463600000, 0], [1196550000000, 0], [1196636400000, 0], ...];

Where the first value is a $g(JavaScript timestamp) and the second is the actual data to be plotted. There are a couple of notes regarding the timestamp mentioned here:

The timestamps must be specified as Javascript timestamps, as milliseconds since January 1, 1970 00:00. This is like Unix timestamps, but in milliseconds instead of seconds (remember to multiply with 1000!).

As an extra caveat, the timestamps are interpreted according to UTC to avoid having the graph shift with each visitor’s local time zone. So you might have to add your local time zone offset to the timestamps or simply pretend that the data was produced in UTC instead of your local time zone.

We’re using $g(Postgres) and the tables I need to query all have a date_created field of timestamp without timezone. Here’s the SQL to fetch the data:

SELECT extract(epoch from date_trunc('day', date_created)) * 1000, count(*) from blog group by extract(epoch from date_trunc('day', date_created)) * 1000 order by extract(epoch from date_trunc('day', date_created)) * 1000 DESC

From the results I use the following python method to create the above data structure which is ready to feed into the flot chart:

    def statsByDay(query):
        data = PubBase.sqlQuery(query)
        dl = []
        for d in data:
            if d[0] != None and d[1] != None:
                dl.append([int(d[0]), int(d[1])])
        return dl         

I’ve been working with a lot of JavaScript frameworks/libraries lately and this is on that integrated into the project pretty flawlessly so kudos to the flot folks!