Difference in COUNT depending on how datetime is read

user_9450614 0
Viewed 33

I'm trying to figure out why there are small differences in two sales report functions for my company and I've pinpointed it to the fact that they use different methods in collecting the data from a month, but I can't figure out why there is a difference in the methods used.

The orders_product_datetime is of the datetime format, so there shouldn't be variations in the data.

Here are the stripped down SQL-queries:

SELECT COUNT(*) as amount FROM orders_product WHERE year(orders_product_datetime) = '2020' AND month(orders_product_datetime) = '01'

Gives me 4162 rows.

SELECT COUNT(*) as amount FROM orders_product WHERE orders_product_datetime >='2020-01-01' AND orders_product_datetime <= '2020-01-31'

Gives me 4032 rows.

Question: What could be causing the difference between the way they are calculated?

Bonus question: Which method would be the most accurate to show each row for the entire month?

mysql
1 Answers

When filtering datetime column, if time is not provided the time will be considered to be '00:00:00' so this causes the max limited date to leave out some of the rows. so the best way to fix is to provide complete datetime or less than next date

SELECT COUNT(*) as amount 
FROM orders_product 
WHERE orders_product_datetime >= '2020-01-01' 
  AND orders_product_datetime < '2020-02-01'

So the max date now changed from <='2020-01-31' to <'2020-02-01' or you might use <='2020-01-31 23:59:59'.

user_78878830
Related