How to select count today and tomorrow data less than specific time group by day?

Keywords´╝Ü mysql sql

Question: 

I have a table like a table below. I want to select count and group by day.

But the data in 1 day will start counts at 7:00:00 until tomorrow at 6:59:59 (24hr.).

For example

Day 1 data between '2019/06/01 7:00:00' and '2019/06/02 06:59:59'

Day 2 data between '2019/06/02 7:00:00' and '2019/06/03 06:59:59'

How can I code the where condition?

id | create_date | judge | -----+---------------------+---------+ 1 | 2019-06-02 8:00:00 | ok | 2 | 2019-06-02 9:00:00 | ok | 3 | 2019-06-02 10:00:00 | ok | 4 | 2019-06-02 11:00:00 | ok | 5 | 2019-06-02 15:00:00 | ok | 6 | 2019-06-03 4:00:00 | ok | 7 | 2019-06-03 5:00:00 | ok | 8 | 2019-06-03 8:00:00 | ok | 9 | 2019-06-03 9:00:00 | ok | 10 | 2019-06-03 9:00:00 | fail |

I've tried below but the result is not as expected.

SELECT COUNT(*),DAY(create_date) FROM mytable WHERE judge = 'ok' and MONTH(create_date) = '6' and YEAR(create_date) = '2019' and TIME(create_date) > '07:00:00' Group by DAY(create_date) order by DAY(create_date) ASC

Expected results COUNT(*) | DAY(create_date) | -----------+---------------------+ 7 | 2 | (from id 1 to 7) 2 | 3 | (from id 8 and 9)

Answers: 

Just subtract 7 hours for the aggregation and the date/time comparisons:

SELECT DATE(create_date - interval 7 hour) as dte, COUNT(*) 
FROM mytable
WHERE judge = 'ok' and 
      create_date >= '2019-06-01 07:00:00' AND
      create_date < '2019-07-01 07:00:00'
GROUP BY DATE(create_date - interval 7 hour) 
ORDER BY dte;

Try this-

SELECT CAST(DATE_SUB(create_date, INTERVAL 7 HOUR) AS DATE),COUNT(*) 
FROM YOUR_TABLE
GROUP BY CAST(DATE_SUB(create_date, INTERVAL 7 HOUR) AS DATE)

You could subtract seven hours from each date, truncate them to show the date only and then group them:

SELECT   DATE(DATE_SUB(create_date, INTERVAL 7 HOUR)), COUNT(*)
FROM     mytable
-- Where clause if you need it...
GROUP BY DATE(DATE_SUB(create_date, INTERVAL 7 HOUR))