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?
Thanks in advance.
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.
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;