How can I stop Google Sheets charts from rounding down durations?

Question

I've got a Google Sheets chart that displays the average pace of a day's rowing machine workouts (e.g. average the time to row 500m).

It looks like this:

The Query I'm using to get the data is this: =QUERY(Data!A:Z, "select A, 15 / (avg(G) / 500) / (60*24) where D = 900 group by A order by A"), where A is the date, G is the distance rowed, and D is the time rowed (filtered to only include 15-minute sessions).

As you can see in the screenshot, I've formatted column B as a custom Duration that displays one decimal point after the seconds, but the chart rounds the paces down to the second before placing / labeling the points.

How can I stop Sheets from discarding the millisecond-component of the duration and make it differentiate between, for example, 0:02:05.8 and 0:02:05.6 in the height at which the points are placed?

Answer 1

This is only a partial answer as there is a glaring issue.

The chart seems to have some limitations when dealing with date and time format and couldn't plot milliseconds as you've pointed out, at least from the chart editor provided (I do not know if this can be achieved using scripts).

However, it has no such limitation when working with number formats. So by instead plotting the time value(), we can get the chart to plot the data precisely. You can then set the label of these values back to the actual time so they appear in the desired format.

Note that I've included the original time as a second series (in lighter blue) to show that the values are actually different:

Note also that this has the drawback that the y-axis label will still be in terms of values and not the time. This is pretty glaring hence it's only a partial answer.

As an aside, I don't know why swapping y-axis scale to another series failed to work. In this case, the axis took on the scale of the value series while the time series are also on the chart. WLOG, we can swap these series around so it should be possible to have the axis took on the scale of the time series while having the value series present in the chart. But for some reason, google sheet is not happy with the latter.