Maryam Pashmi Maryam Pashmi - 24 days ago 4
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
>= "2016-10-03 12:33:26"
< "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?


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.