Question 263825 in Mysql Question posted in
The official MySQL documentation can be found here.

MySql Group by hour

i need to select only full hour values from a list of datetime entries.
But if i select more columns, the group by hour doesn’t work.
So if i don’t select the column "datetime" the group by works.

SELECT 
datetime, 
DATE_FORMAT(STR_TO_DATE(datetime, " %Y_%m%d_%H%i%s"), '%H')
FROM imagedata 
WHERE DIRECTORY = 17082901 
and `datetime` >= '2017_0721_000000' 
and `datetime` <= '2017_0921_131500'
GROUP BY datetime, DATE_FORMAT(STR_TO_DATE(datetime, " %Y_%m%d_%H%i%s"), '%H')

Example: https://www.db-fiddle.com/f/iaNrxC35uFYhDNZSeXa76A/1

Example Data:

directory   datetime            year    mo  day time
17082901    2017_0829_200000    2017    8   29  200000
17082901    2017_0829_200700    2017    8   29  200700
17082901    2017_0829_201500    2017    8   29  201500
17082901    2017_0829_203000    2017    8   29  203000
17082901    2017_0829_204500    2017    8   29  204500
17082901    2017_0829_205100    2017    8   29  205100
17082901    2017_0829_210000    2017    8   29  210000
...

I need a result like this:

29.08.17 20:00
29.08.17 21:00
...
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Here is a good solution i think:

    WITH cte AS (
SELECT id, DATETIME, DATE_FORMAT(STR_TO_DATE(datetime, " %Y_%m%d_%H%i%s"), '%Y-%m-%d %H') AS xxx,
        RANK() OVER ( PARTITION BY DATE_FORMAT(STR_TO_DATE(datetime, " %Y_%m%d_%H%i%s"), '%Y-%m-%d %H')
        ORDER BY time ASC
        ) AS r
FROM imagedata
WHERE
DIRECTORY = 17082901 
and `datetime` >= '2017_0721_000000' 
and `datetime` <= '2017_0921_131500'
)
SELECT id, DATETIME, DATE_FORMAT(STR_TO_DATE(datetime, " %Y_%m%d_%H%i%s"), '%Y-%m-%d %H') AS xxx
FROM cte
WHERE r = 1;

  2. 0

    Try this:

    SELECT 
  DATE_FORMAT(STR_TO_DATE(datetime, " %Y_%m%d_%H%i%s"), '%d.%m.%y %H:00') AS formatted_datetime
FROM imagedata 
WHERE DIRECTORY = 17082901 
  AND `datetime` >= '2017_0721_000000' 
  AND `datetime` <= '2017_0921_131500'
GROUP BY formatted_datetime
ORDER BY formatted_datetime;

    If you want to select all fields:

    WITH HourlyGroups AS (
  SELECT 
    DATE_FORMAT(STR_TO_DATE(datetime, " %Y_%m%d_%H%i%s"), '%y.%m.%d %H:00') AS formatted_datetime,
    DATE_FORMAT(STR_TO_DATE(datetime, " %Y_%m%d_%H%i%s"), '%H') AS hour
  FROM imagedata 
  WHERE DIRECTORY = 17082901 
    AND `datetime` >= '2017_0721_000000' 
    AND `datetime` <= '2017_0921_131500'
  GROUP BY formatted_datetime, hour
)
SELECT HourlyGroups.formatted_datetime, imagedata.*
FROM imagedata
JOIN HourlyGroups ON DATE_FORMAT(STR_TO_DATE(imagedata.datetime, " %Y_%m%d_%H%i%s"), '%H') = HourlyGroups.hour
ORDER BY HourlyGroups.formatted_datetime;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search