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?
CodePudding user response:
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
.