w3hello.com logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML videos Categories
Get max value of year, month, week, day, hour, data from mysql table

You asked for maximum values "a week day hour etc." I fear that's kind of a vague specification.

You can get the max value for each day like so:

 SELECT date AS day,
        MAX(value1) AS maxval1
   FROM testdb
  GROUP BY date
  ORDER BY date

If you had a DATETIME column instead of separate date and time columns, this whole thing would be a lot easier and far faster.

You can get the max value for each hour like so:

 SELECT DATE_FORMAT(STR_TO_DATE(CONCAT(date,' ',time)),
'%Y-%m-&d %H:00:00') AS hour,
        MAX(value1) AS maxval1
   FROM testdb
  GROUP BY DATE_FORMAT(STR_TO_DATE(CONCAT(date,' ',time)), '%Y-%m-&d
%H:00:00')
  ORDER BY DATE_FORMAT(STR_TO_DATE(CONCAT(date,' ',time)), '%Y-%m-&d
%H:00:00')

In this query the expression

DATE_FORMAT(STR_TO_DATE(CONCAT(date,' ',time)), '%Y-%m-&d
%H:00:00')

takes the date/time combination and truncates it to the top of the most recent hour. For example, it changes '2013-04-01 08:35:20' to '2013-04-01 08:00:00"

Similar expressions work to truncate timestamps to other things, like the week (the previous Sunday at midnight) or the nearest quarter hour. Here's some background. http://www.plumislandmedia.net/mysql/sql-reporting-time-intervals/





© Copyright 2018 w3hello.com Publishing Limited. All rights reserved.