I have my data all good but i cant put it in a graph.
I am in postresql.
SELECT
DATE_TRUNC('day', "order_date"),
avg( "difference") AS "avg time"
FROM "difference_time"
where date_trunc('day',order_date)<date_trunc('day',current_date)
GROUP BY DATE_TRUNC('day', "order_date")
If you check the button on the bottom that has a line on it, that would render the chart.
By the way, I suggest you enhance the avg( "difference") AS "avg time" section of the SQL to make it a plain number so Metabase can render it better
@louisc the data that you're trying to render is impossible in the form you have. That's why I suggested you to first transform the durations into a raw number, and then try to build a chart. Data needs to be in the form of:
timestamp, data
01-01-2020, 1
01-02-2020, 2 ....
if you try to render a value that's in the form of '0 years, 0 months....' that's impossible to represent in a chart. You need to cast that into a raw number
I tried to find a way to cast an interval into raw numbers but impossible
like that:
SELECT
DATE_TRUNC('day', "order_date") as date,
cast (avg ( "difference") as time) AS "avg_time"
FROM difference_time
GROUP BY DATE_TRUNC('day', "order_date")
It still doesn't work
try using a SQL question like
"select extract(seconds from created_at::timestamp - birth_date::timestamp) from people" (these are fields from the sample database)
