Timeline visualisation not able to consume "time of day"

Was thinking about using Timeline visualisation in order to represent duration of small events by the day.

Idea was to use day of month (limited to past 7 days) as label, then, obviously, start time and end time of the event (there are many of them).

Problem is, this visualisation pretty much restricts my intervals to be either between two dates or, well, numbers and to my surprise it cannot support interval between two "Time of Day"s (e.g. “12:30” and “12:34”). Whilst interval between two "Time"s works well and I generally like how it looks, using it to visualise data for more than one day is a big problem.

To specify, my events are really small, from 20 seconds and up to max 15-25 minutes, thus to make any sense of data I need one day to be exactly on one “row of intervals”.

Is that a feature that Timeline cannot work with all timeframes or more like a bug? For me it would make sense that it would be able to reflect interval between two times (of day) without need of the date. Unfortunately other visualisations don’t produce output that would be plausible and look good.

Maybe I’ve missed something and there are ways how to make it work?

I’m not yet sure whether it’s something we’d consider a bug, but it does look like time_of_day is not recognized as a true “timeframe” for the purposes of the timeline viz.

It looks like hour_of_day does work, because it’s numeric, so something you could look into would be making the date/time field a totally numeric representation (like # of seconds/minutes), and then doing some value formatting or html on top of it to show the HH:MM.

Could you show an example of how would be advised to do conversion to number and formatting it? It seems that I’m struggling with that bit.

If to take literally converting hh:mm:ss to number representation, then it, obviously, screws with scale (i.e. events happening within two hours is visually shown as long lasting).

If to convert to, say, seconds (logic with epoch being start of the day), then scale would be correct, however, then I’m struggling to on level of formatting converting this back to hh:mm.

Any thoughts?

Have developed solution that works. Thank you for hints.

Wish that it could be easier - by having intervals between time_of_day.

1 Like

Could you share your solution? Part of the reason I only gave hints was that I hadn’t fully structured it out in my head.

Agree that we could certainly make this easier, I’m still going to raise it with our engineers. Thanks for flagging.

Sure.

From timestamp I’ve extracted hour, minute and seconds. “Converted” them all to seconds, so that it would represent seconds from beginning of the day. Then did something that was inspired by this topic.

Ultimately SQL looks like that
sql: (hour(${TABLE}.EVENT_DATE) * 3600 + minute(${TABLE}.EVENT_DATE) * 60 + second(${TABLE}.EVENT_DATE)) / 86400;;

Using that for both beginning of the event and end.

After that it was a no-brainer to format it as hh:mm.

There might be more elegant way, but it works and is pretty accurate, so I’m satisfied with the end result.

1 Like

We also have run into this issue several times and would +1 the request for an in-product solution to this. When looking at things like when a task finishes each day to compare to SLA we’d love to graph this in a more intuitive human readable way than ‘minutes from SLA’.