Monday, November 17, 2008

Use SQL and Excel to Plot Time Distribution Graphs

If you are starting off with a small number of data points (in my case 60694) that are all times, and you want to plot these into an x, y scatter chart/graph, here is an example that might help. First run the SQL against the column you want. In Oracle this looks like:
select to_char(somedatetimecolumnname,'HH24:MI:SS') as time from sometablename order by time
Then copy that data into Excel or OpenOffice Calc (note: Excel only supports up to 65536 rows). For the second column, use the function:
Fill down on the random column by selecting it and selecting all cells below it to the last row of data, and then hit Ctrl-D. Then select both columns up to 32000 rows and hit the graph/chart icon. Choose X/Y Scatter. The maximum number of rows is supposedly 32000 (and if you try to graph more than that, it will act like you only selected 32000 which cuts off/terminates your data range leaving you with an incomplete chart anyway). If you have more than 32000 rows of data, start a new selection of data from the row after the first selection to the end of the data (or up to 32000 more rows) and chart those the same way. You end up with 2 charts, but at least it is something, and might help you out.

No comments: