2/19/10

Working with data type Duration Google Visualizations

i have a website that works with duration measures (length of workout
for instance). Values are stored in the dB as total seconds and
displayed to the user as HH:MM:SS.0

When attempting to migrate to the Google visualizations for displaying
data and charts, I have tried a variety of methods to work with this
type of information with mixed success- some things i tried were
pretty stupid in retrospect, as I progressed, my mistakes become more
interesting.

Initial data table display
1. store "HH:MM:SS.0" as datatype=string
Appears correctly to the user, however doesn't sort correctly (sorts
alpha) and can't be charted. Bad idea.

data.setCell(0,2,'8:15');

2. store as time of day datatype=timeofday
This requires converting my seconds to the javascript time of day
array. This appeared correctly to the user (HH:MM:SS.0) and sorted
correctly - however since you can not plot timeofday datatype, when it
came time to display progress reports and ATL's this fell short.
data.setCell(0,2,[0,8,15,0]);

3. Store as datatype=number but use the display value as HH:MM:SS.0
e.g. data.setCell(0,2,495,'8:15');
495 are the total seconds, 8:15 is what appears to the user

This works well for most chart types and gives me a great deal of
control over the way times are displayed (value labels are created
server side) - and the chart lines are drawn correctly - however in
the annotated timeline - the values that appear to the user on data
point mouse over are still the total seconds (495) instead of the
desired display value (8:15)

4. Store as datatype=number, but store the value as decimal minutes
(totalseconds/60).
This allows the axis that is shown to the user during charting to use
minutes as the unit (which is good) however on mouse click tooltip the
value that is shown to the user is the decimal minute value - which is
confusing.

5. store as datatype=number, but store the value as minutes.seconds -
e.g. instead of storing 495 seconds as:
8.25 (decimal)

store as

8.15 (8 minutes, 15 seconds). Then use a formatter to make the decimal
separator ":" instead of the default "."

This supports correct sorting, correct graphing, correct ATL display
and makes a bit more sense to the user. It ceases to work when going
over 59.59 (over 1 hour duration). I have not figured out a way to
force the ATL to modify the decimal separator character.

If anyone else has struggled with similar issues i'd love to hear your
solution - if not maybe this will save some testing on some one else's
part.

No comments:

Post a Comment