Postgres Time Serie should summarise previous values

Grafana Time Series Panel
1

I’m using newest version of Grafana v9.5.2 and Postgres 15.

I’ve a simplified Order table like this:

image
image1256×290 15 KB

Note that there isn’t a value for tenant2 for time 13:00 – in which case the previous value should be used. The numbers of tenants is dynamic.

I’m trying this query to create a time serie summarising todays orders for each hours (across tenants) :

SELECT 
  $__timeGroup(datetime, $__interval, previous),
  sum(orders_today) as metric
FROM orders
WHERE $__timeFilter("datetime")
GROUP BY 1
ORDER BY 1

Which results in this table inside Grafana:

image
image1004×372 18.4 KB

I expect the time serie to summarising “orders_today” for all tenants, like this:

  • 2023-06-17 12:00 - 3
  • 2023-06-17 13:00 - 7
  • 2023-06-17 14:00 - 16
  • 2023-06-17 15:00 - 16

How can I accomplish this?

Further, if i set the time range to “Last 5 years” it generates a table like this:

image
image1854×452 22.2 KB

Here I would expect the metric for 2023-06-17 to be 16.