How to get the maximum/minimum value's time

Community
1

I want to get the maximum and minimum values, as well the maximum value 's time and the minimum value 's time.
for example:

CREATE MATERIALIZED VIEW weather_1h AS
SELECT
  ts,
  max(temperature) maxTemp,
--maxTempTime
  min(temperature) minTemp
--minTempTime
FROM weather_behavior
SAMPLE BY 1h;

I have tried this link： How to get the maximum/minimum value’s time - QuestDB Community
but error： window function on base table is not supported for materialized views

2

all the codes:

CREATE MATERIALIZED VIEW weather_1h AS
DECLARE 
    @sample_interval := 1h,
    @sample_interval_str := '1h'
with w_max_min as (
  select *, 
  FIRST_VALUE(ts::long) over(partition by timestamp_floor(@sample_interval_str, ts) order by temperature ASC) as ranking_min,
  FIRST_VALUE(ts::long) over(partition by timestamp_floor(@sample_interval_str, ts) order by temperature DESC) as ranking_max
  FROM weather_behavior
)
SELECT 
        ts, 
        max(temperature) AS max_value,
        ranking_max::TIMESTAMP,
        min(temperature) AS min_value,        
        ranking_min::TIMESTAMP
    FROM w_max_min   
    SAMPLE BY @sample_interval
3

I am writing on the phone and no access to a proper keyboard, so please excuse the lack of SQL.

I think an option here might be first calculating min/max, then doing two asof joins, on each of the values.

4

Actually maybe not asof join, as it would join only on same or previous timestamp. You might try asof join but reversing the order, or go with inner joins, but then you need to limit to 1 the result of each join

5

I think when obtaining the maximum/minimum values, we often also obtain the moments when they occur. Is there a built-in function method available to simplify this operation?

6

sys os:window11
questdb version：9.0.0

7

It should be possible to achieve this without window functions, by joining back to the table. This is an example using demo.questdb.io

WITH btcusd AS (
  trades WHERE timestamp IN today() AND symbol = 'BTC-USD'
), minmax AS (
   SELECT
    timestamp,
    max(price) maxPrice,
    min(price) minPrice,
  FROM btcusd
  SAMPLE BY 1h
), min_joined AS (
  SELECT minmax.timestamp as hour, 
         minPrice,
         btcusd.timestamp as minTime
  FROM minmax
  JOIN btcusd
  ON price = minPrice
  LIMIT 1
), max_joined AS (
  SELECT minmax.timestamp as hour, 
         maxPrice,
         btcusd.timestamp as maxTime
  FROM minmax
  JOIN btcusd
  ON price = maxPrice
  LIMIT 1
)
SELECT minPrice, minTime, maxPrice, maxTime 
FROM min_joined 
JOIN max_joined ON hour;
minPrice minTime maxPrice maxTime
117019.1 2025-07-22T04:42:43.249000Z 117525.3 2025-07-22T08:26:07.048000Z

It will need a closer look, but in principle, you get your min/max result set and self-join it. Maybe this puts you on a closer path?

8

I think this might not lead to the result I desire.
the result I desire is like this. example:

hour maxValue minValue maxTime minTime
2025-01-01 00:00:00 1.11 0.11 2025-01-01 00:30:10 2025-01-01 00:01:55
2025-01-01 01:00:00 1.11 0.11 2025-01-01 01:30:10 2025-01-01 01:01:55
2025-01-01 02:00:00 1.11 0.11 2025-01-01 02:30:10 2025-01-01 02:01:55
2025-01-01 03:00:00 1.11 0.11 2025-01-01 03:30:10 2025-01-01 03:01:55
2025-01-01 04:00:00 1.11 0.11 2025-01-01 04:30:10 2025-01-01 04:01:55
9

Okay, for now, the simplest will either be a join, or just doing it in two queries (get the max values first, then find the timestamps second).

We’re discussing this internally and will see what would be the best way to make this easy i.e. returning other parts of the row that match the aggregate.

10

Thank you very much for your reply.This is exactly the result I expected: “returning other parts of the row that match the aggregate”,hope for further progress