How to add time to the output of this max/min select?

Question

I have a simple sqlite3 database for recording temperatures,the database schema is trivially simple:-

CREATE TABLE temperatures (DateTime, Temperature);

To output maximum and minimum temperatures over 1 month I have the following query:-

   SELECT datetime, max(temperature), min(temperature) from temperatures 
   WHERE datetime(DateTime) > datetime('now', '-1 month')
   GROUP BY strftime('%d-%m', DateTime)
   ORDER BY DateTime;

How can I get the times for maxima and minima as well? Does it need a sub-query or something like that?

Answer 1

Use window functions MIN(), MAX() and FIRST_VALUE() instead of aggregation:

SELECT DISTINCT date(DateTime) date, 
       MAX(temperature) OVER (PARTITION BY date(DateTime)) max_temperature, 
       FIRST_VALUE(time(datetime)) OVER (PARTITION BY date(DateTime) ORDER BY temperature DESC) time_of_max_temperature,
       MIN(temperature) OVER (PARTITION BY date(DateTime)) min_temperature,
       FIRST_VALUE(time(datetime)) OVER (PARTITION BY date(DateTime) ORDER BY temperature) time_of_min_temperature
FROM temperatures 
WHERE datetime(DateTime) > datetime('now', '-1 month')
ORDER BY date;

If your DateTime column contains values in the ISO format YYYY-MM-DD hh:mm:ss there is no need for datetime(DateTime).
You can use directly DateTime.