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.
var d = [[1196463600000, 0], [1196550000000, 0], [1196636400000, 0], ...];
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:
@staticmethod def statsByDay(query): data = PubBase.sqlQuery(query) dl =  for d in data: if d != None and d != None: dl.append([int(d), int(d)]) return dl