Openshift does not react on INTERVAL of MySQL

问题:

I have tested the table test on the locanhost and Openshift and I am facing problem that on openshift I am not getting anything back when I query the data like this WHERE arrivaltime BETWEEN curtime() - INTERVAL 2 MINUTE AND curtime() + INTERVAL 2 MINUTE although on the localhost I am getting the route 9 back. On openshift I am insert arrivaltime according to the server time. On localhost I am inserting arrivaltime according to my local time but when I change the query to this form WHERE time_format(arrivaltime,'%H:%i')= time_format(curtime() ,'%H:%i') I am getting 9back on openshift but I need my response to be+- 2 minutes. How can I fix it? or does someone have a openshift account how can test the table and the query below?

I appreciate any help.

Test table

CREATE TABLE test(
id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
 arrivaltime Time  NOT NULL,
 route INT(11) NOT NULL   
)

Inserting data:

INSERT INTO test(arrivaltime, route) values('04:16:00', 9)

This query does not work on Openshift and it works on localhost:

SELECT route FROM test
WHERE arrivaltime BETWEEN curtime() - INTERVAL 2 MINUTE AND curtime() + INTERVAL 2 MINUTE

openshift curtime()

04:15:15

when I query the test like this on openshift I am getting response but the arrivaltime should be equal to the current time.

SELECT route FROM test
WHERE time_format(arrivaltime,'%H:%i')= time_format(curtime() ,'%H:%i')

回答1:

well, I'm not really good at MySQL. So, maybe someone could improve my question here. But I guess the problem lies in your server local time format or maybe how the MySQL return the time format after you add or subtract the time.

For this problem, I'll advise to use this query instead of trying to modify it directly like that.

select route from test where arrivaltime between subtime(curtime(), '00:02:00') and addtime(curtime(), '00:02:00');

and also to make sure what the problem is, maybe you could try to check whether there is a warning or error by using this query.

show warnings;
show errors;