postgresql

How to get the most value for every day

id        timestamp              value
--------+---------------------+-----------
225     |2018-07-24 13:00:00  |0
226     |2018-07-24 18:33:32  |196
227     |2018-07-25 5:59:14   |290
173     |2018-07-05 8:16:14   |258
228     |2018-07-25 7:00:00   |469
175     |2018-07-07 6:00:00   |0
176     |2018-07-07 9:25:35   |236
177     |2018-07-07 17:19:13  |300
178     |2018-07-08 5:47:13   |323
179     |2018-07-08 6:49:14   |230
180     |2018-07-08 17:45:36  |270
181     |2018-07-09 5:41:13   |250
182     |2018-07-09 9:43:13   |213
183     |2018-07-09 18:37:44  |226
184     |2018-07-10 5:22:44   |380
185     |2018-07-10 7:37:14   |200
186     |2018-07-10 15:54:44  |260

SELECT max(value)  
  FROM table 
 WHERE timestamp BETWEEN '2018-07-22 00:00:00' 
                     AND '2025-04-22 23:59:59'

This is my query for a specific period, I need it to get the highest value per day or the highest per day of all the data in the table.

This is my query for a specific period, I need it to get the highest value per day or the highest per day of all the data in the table.

Solution

  • You can just group by day date by converting it from the timestamp field like this:

    SELECT timestamp::date AS day,
       MAX(value) AS max_value
  FROM yourtable
 GROUP BY day
 ORDER BY day;

    Here is a working fiddle: https://sqlfiddle.com/postgresql/online-compiler?id=b4df1f6f-81d8-469e-ada4-fbd6e43f41a6