1

I have such script to query userCount from start of one day 'xxxx-xx-xx 00:00:00', to end of this day 'xxxx-xx-xx 23:59:59'.

USE db;
SELECT user
FROM db.user AS user
WHERE
user.payTime BETWEEN '2017-07-12 00:00:00' AND '2017-07-12 23:59:59'
GROUP BY user.username

So now how can I query the user's payTime within the day from 2017-06-01 to 2017-07-12 ?

The first thing came out of my mind is declared a date range:

SET CURRDATE = '2017-06-01';
SET ENDDATE = '2017-07-12';

and put the script into a loop like:

WHILE CURRDATE < ENDDATE DO
   USE db;
   .......
   WHERE
   user.payTime BETWEEN 'CURRDATE 00:00:00' AND 'CURRDATE 23:59:59'
   .......
   SET CURRDATE = DATE_ADD(CURRDATE, INTERVAL 1 DAY)
END WHILE;
Zigii Wong
  • 7,766
  • 8
  • 51
  • 79
  • 1
    Maybe I'm missing something, but why can't `BETWEEN '2017-06-01 00:00:00' AND '2017-07-12 23:59:59'` work? – cwallenpoole Jul 13 '17 at 10:52
  • @cwallenpoole because i want to query the data of each specific day，but not the range of two dates – Zigii Wong Jul 13 '17 at 11:00
  • Have you tried grouping by date: https://stackoverflow.com/questions/6054144/how-can-i-group-by-date-time-column-without-taking-time-into-consideration – cwallenpoole Jul 13 '17 at 11:03

1 Answers1

0

Try this - 

SELECT user, user.payTime
FROM db.user AS user
WHERE user.payTime BETWEEN '2017-06-01 00:00:00' AND '2017-07-12 23:59:59'
GROUP BY CAST(user.payTime as DATE)
Ankit Bajpai
  • 13,128
  • 4
  • 25
  • 40