Maryam Pashmi Maryam Pashmi - 2 months ago 11
MySQL Question

Strange behavior with count function in MySQL 5.6

I have a fix query as below, some strange behaviors which I try to understand it is whenever I run this query in different moments I got different results:( 134 , 143, 147, 152, 158)

This is how define in created table:

`user_registration_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,


select count(*) from users where
user_registration_timestamp
>= "2016-10-03 12:33:26"
and
user_registration_timestamp
< "2016-10-03 15:33:26";

Why this happened? timestamp is fixed, it is not variable, so what are those reasons for seeing this strange behavior with MySQL?

Answer

Your query will return different counts as long as new users get registered. You can find out the details if you replace COUNT(*) with *.

My guess is that you haven't really verified that because you're assuming that 2016-10-03 15:33:26 is date in the past thus there's no possibility of having new users. The fact is that it's a local time and as of now (2016-10-03 14:30 UTC) it's a past date in the Eastern time zones and still to come everywhere else.

If MySQL Server is not configured to use your local time zone but your code assumes so then it can be confusing.