user1464262 user1464262 - 7 months ago 15
PHP Question

Mysql select Query total different in 2 different time

This is my mysql query to get yesterday's total. It run from CRON and i am getting different total in 3 different time. It run 3 times per day.
7AM - 6067
12PM - 6670
7PM - 6670

600 different??

SELECT COUNT(*) FROM `users_table` WHERE date(CONVERT_TZ(`reg_date`, '-0:00', '-7:00')) = DATE_ADD(CURDATE(), INTERVAL -1 day)

Answer

Because every time, it depends on your current time. You need to consider only the day.

SELECT COUNT(*) FROM `users_table`
WHERE date(CONVERT_TZ(`reg_date`, '-0:00', '-7:00')) >= DATE_SUB(CONCAT(CURDATE(), ' 00:00:00'), INTERVAL 1 DAY)
AND date(CONVERT_TZ(`reg_date`, '-0:00', '-7:00')) <= DATE_SUB(CONCAT(CURDATE(), ' 23:59:59'), INTERVAL 1 DAY)