Hello there,
I made a query which averages time to provide time of day.
SELECT
DATE_TRUNC('month',start_time) AS "Month",
TO_TIMESTAMP(AVG(EXTRACT(EPOCH FROM (start_time::TIME))))::TIME AS "Average start time",
TO_TIMESTAMP(AVG(EXTRACT(EPOCH FROM (end_time::TIME))))::TIME AS "Average end time"
FROM calisthenics_workouts
WHERE (start_time AT TIME ZONE 'Europe/Berlin')::TIME >= '18:00:00'
GROUP BY 1
ORDER BY 1 DESC
I would like to make a line chart with two lines. One for Average start time, one for Average end time. However, the UI is not recognising the time values as valid for the Y axis.
Is there a way around that? Does something need to be configured?
Still working on this, but I don’t think Metabase can handle ‘time of day’ as a Y axis type. I’m trying to think of some hackarounds.
EDIT: Best I have come up with is to convert the times into integers (like 1:00PM becomes 1300). The scaling will be all messed up, but it will technically plot.
1 Like
Hey, thanks looking into this.
I see what you are saying. Can you share the the SQL you used to convert the times into integers?
A follow up question on this same issue.
I simplified my SQL to
SELECT
DATE_TRUNC('month',start_time),
avg(start_time::time)
FROM calisthenics_workouts
WHERE start_time::time > '18:00:00'
GROUP BY 1
ORDER BY 1 DESC;
The problem I’m having, in the average column the time is not very reading-friendly. It Metabase does not allow me to edit how the time is presented.
On PostgreSQL, avg(time) gives an interval.
When I meant “convert to an integer,” I meant using to_char() to convert the time into a number or string, like this:
select to_char('2020-01-01'::date + '18:45'::time, 'HH24MI')::int;
to_char
─────────
1845
(1 row)
Now it’s just a number and Metabase can plot that just fine.
Yes, it is a time interval.
When I query the database directly via Dbeaver Client, I got results like
19:57:41
19:32:33
Those are intervals but much more readable than what Metbase shows (as seen in my screenshot above). It does not look like it is possible to change the way Metabase presents the interval value. Or am I missing something?
I’m not aware of any way to change Metabase’s display of intervals, and I agree that it is annoying. I format intervals in the query to avoid it.
DBeaver formats it the way psql does, but that gets wonky with intervals with days in them.
Ok I see. Thank you for the help with this 
