cjm2671 cjm2671 - 6 months ago 19
MySQL Question

How can I visualise a histogram of timestamps (stored in MySQL) without having to code much?

I have a big list of timestamps and I want to be able to plot them on graphs and display them in a similar way to google analytics, with the same easy ability to refresh the data and explore it interactively.

The timestamps are stored in a MySQL database, bonus points for a solution that automatically queries it.

Answer

It's a bit tricky to answer without the slightest indication of what your data is or contains, but if you're counting clicks you're looking into a simple group by statement:

select date_format(click_date, '%Y-%m-%d') as click_date,
       count(*) as num_clicks
from clicks
where :start <= click_date and click_date < :end
group by date_format(click_date, '%Y-%m-%d')