how to count data occurrence between certain DateTime range in mysql

I am sorry for this kind of newbie question, but it is quite hard for me. My MySQL table holds high-frequency stock prices. On each trading date, data starts from day 1 18:00 to day 2 17:00. I want to count the data occurrence for each trading day between the above trading hour. I know how to count for a single day just as following code

SELECT COUNT(*) FROM table WHERE date BETWEEN '2021-08-25 18:00:00' AND '2021-08-26 17:00:00'

But how to count all the days in the table?

My table looks like this:

What I want to looks like this:

Thanks in advance.

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

Appreciate that your “day” starts at 6pm. So, we can normalize each datetime by subtracting 18 hours, and then aggregate:

SELECT DATE(date - INTERVAL 18 HOUR), COUNT(*) AS cnt
FROM yourTable
GROUP BY 1;

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0