Strange behavior with count function in MySQL 5.6

Question

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?

A deterministic query can return different data sets if the underlying data changes but I guess you wouldn't be asking if that was the case... — Álvaro González, Oct 03 '16 at 14:23
@ Álvaro González How would possible to change when the user_register_timestamp is already passed for that specific time? — pm1359, Oct 03 '16 at 14:25

score 0 · Accepted Answer · answered Oct 03 '16 at 14:28

0

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.

answered Oct 03 '16 at 14:28

Álvaro González

142,137
41
261
360

thx for your answer, but I want to be sure if this is happend, is there any comment in mysql t force eastern ts? How can I do it? – pm1359 Oct 03 '16 at 14:34
[How to set time zone of mysql?](http://stackoverflow.com/questions/930900/how-to-set-time-zone-of-mysql#19069310) – Álvaro González Oct 03 '16 at 14:37
no, how to force my query to use local ts? -i have added more info. – pm1359 Oct 03 '16 at 14:38
Please click on the above link and read the most voted answer. – Álvaro González Oct 03 '16 at 14:41
1

it seem what you said it's right, in fact mysql shows current timestamp on 2016-10-03 14:46:16 however local ts is 2016-10-03 16:47:16 – pm1359 Oct 03 '16 at 14:47

Strange behavior with count function in MySQL 5.6

1 Answers1