Skip to main content
March 12, 2009
Question

Time Values In CFChart

  • March 12, 2009
  • 1 reply
  • 888 views
Hi,

Is there any way to create a chart using time values for the series?
For example I have a query that calculates the average time a user has spent on technical help calls in a month. The query works fine.

I want to display the results of the query in a bar chart.The chart does not work, it wont accept time values.
Is there a way to convert the time values to simple numbers maybe?
Im using MySQL 5.

QUERY :-

<cfquery name="avgduration" datasource="datasource">
SELECT userid, SEC_TO_TIME(AVG(TIME_TO_SEC(TIMEDIFF(closedtime,opentime)))) AS average
FROM datasource
WHERE opendate BETWEEN '2009-02-01' AND '2009-02-28' AND loginname = 'userid'
GROUP BY userid
</cfquery>

CHART :-

<cfchart format="png"
chartheight="300"
chartwidth="450"
showxgridlines="yes"
showygridlines="yes"
xaxistitle="Operators"
yaxistitle="AVG Call Duration"
title="AVG Call Duraration By Operator">

<cfchartseries type="bar"
datalabelstyle="value"
query="avgduration"
itemcolumn=""
valuecolumn="average">
</cfchartseries>
</cfchart>

Many thanks for any comments
This topic has been closed for replies.

1 reply

BKBK
Community Expert
Community Expert
March 20, 2009
I don't think the cfchart tag can convert the format hh:mm:ss into a number. I would display the time in seconds, minutes or hours. it requires just a minor change of the select statement and cfchart's yaxistitle attribute. the possibilities are

select TIME_TO_SEC(TIMEDIFF(closedtime,opentime)) as average
yaxistitle = "AVG Call Duration in Seconds"

select TIME_TO_SEC(TIMEDIFF(closedtime,opentime))/60 as average
yaxistitle = "AVG Call Duration in Minutes"

select TIME_TO_SEC(TIMEDIFF(closedtime,opentime))/3600 as average
yaxistitle = "AVG Call Duration in Hours"