Find the min value & datetime for each day

user_15940854 0
Viewed 207

I have a timescaleDB database that has the following shape:

I have a temperature value each 5 minutes.

dt temperature
2019-04-20 20:00:00 13
2019-04-20 20:05:00 12
2019-04-20 20:10:00 12

And I would like to get the minimum temperature value for each day and display the exact datetime when it happened. Another small constraint, in the case I have two min temperature value for one day, I should select the earlier one of the day.

The answer should look like this:

dt temperature
2019-04-20 07:10:00 10
2019-04-21 04:35:00 5
2019-04-22 02:10:00 9

I already got something close to this, the only missing part is the exact dt, I just can get the date.

SELECT DATE(dt), temperature
FROM temperature_DB
GROUP BY CAST(dt as DATE)

Thank you in advance for your help!!

timescaledb max min datetime postgresql
1 Answers

You can use DISTINCT ON syntax for that. The requirement is to have a single value for a date, so DISTINCT ON (dt:date) should be used. Everything else is described in ORDER BY part of the statement.

SELECT 
  DISTINCT ON (dt::date)
  dt,
  temperature
FROM
  temperature_DB
ORDER BY
  dt::date,
  temperature ASC, -- (first value will be lowest)
  dt ASC --(put the earliest time of lowest temperature measurement first);
user_80536550
Related