Mysql 5.6 timestampdiff problem with return result

Question

I have two tables

CREATE TABLE `contract` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`emp_id` int(11) DEFAULT NULL ,
`sign_time` datetime DEFAULT NULL ,
`end_time` datetime DEFAULT NULL ,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

CREATE TABLE `employee_detail` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(100) DEFAULT NULL ,
 `stage` varchar(100) DEFAULT NULL ,
 PRIMARY KEY (`id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

and some data:

INSERT INTO `contract` (`id`, `emp_id`,`sign_time`, `end_time`) VALUES 
('25', '83', '2018-11-21 00:00:00', '2018-12-01 15:27:00');
INSERT INTO `contract` (`id`, `emp_id`,`sign_time`, `end_time`) VALUES 
('26', '94', '2018-11-21 00:00:00', '2018-12-01 15:23:00');

INSERT INTO `employee_detail` (`id`, `name`, `stage`) VALUES ('83', 'Michael', 
'1');
INSERT INTO `employee_detail` (`id`, `name`, `stage`) VALUES ('94', 'John', 
'1');

when I query the database with SQL:

SELECT
c.*
FROM
contract c
JOIN employee_detail e ON c.emp_id = e.id
WHERE
   e.stage != - 1
AND (
TIMESTAMPDIFF(
    MINUTE,
    '2018-11-30 09:18:23',
    c.end_time
)
) > 0
AND TIMESTAMPDIFF(
MONTH,
'2018-11-30 09:18:23',
c.end_time
) = 0

I got 0 records. But if I query with SQL:

 SELECT
c.*
FROM

contract c
JOIN employee_detail e ON c.emp_id = e.id
WHERE
   e.stage != - 1
AND (
TIMESTAMPDIFF(
    MINUTE,
    '2018-11-30 09:18:23',
    c.end_time
)
) > '0'
AND TIMESTAMPDIFF(
MONTH,
'2018-11-30 09:18:23',
c.end_time
) = '0'

which turns interger 0 to string '0', I got two right records. I searched from https://dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html#function_timestampdiff, and I found that:

Returns datetime_expr2 − datetime_expr1, where datetime_expr1 and datetime_expr2 are date or datetime expressions. One expression may be a date and the other a datetime; a date value is treated as a datetime having the time part '00:00:00' where necessary. The unit for the result (an integer) is given by the unit argument. The legal values for unit are the same as those listed in the description of the TIMESTAMPADD() function.

I'm confused about the result and the explanation of Oracle. So timestampdiff function returns one integer value but when I use it in SQL statement I got an incorrect result while if I treated it as string value I got the right answer. Could anybody explain the weird phenomenon? Thanks a lot!

Answer 1

I get inconsistent results depending on which predicate is used first in the query:

SELECT
    c.*
    , TIMESTAMPDIFF(MONTH,'2018-11-30 09:18:23',c.end_time)  diff_month1
    , case when TIMESTAMPDIFF(MONTH,'2018-11-30 09:18:23',c.end_time) = 0 then 'equal'
           when TIMESTAMPDIFF(MONTH,'2018-11-30 09:18:23',c.end_time) > 0 then 'greater'
      end diff_month
    , TIMESTAMPDIFF(MINUTE,'2018-11-30 09:18:23',c.end_time) diff_minute1
    , case when TIMESTAMPDIFF(MINUTE,'2018-11-30 09:18:23',c.end_time) = 0 then 'equal'
           when TIMESTAMPDIFF(MINUTE,'2018-11-30 09:18:23',c.end_time) > 0 then 'greater'
      end diff_minute
FROM contract c
JOIN employee_detail e ON c.emp_id = e.id
where TIMESTAMPDIFF(MONTH ,'2018-11-30 09:18:23',c.end_time) = 0
  and TIMESTAMPDIFF(MINUTE,'2018-11-30 09:18:23',c.end_time) = 0

id | emp_id | sign_time           | end_time            | diff_month1 | diff_month | diff_minute1 | diff_minute
-: | -----: | :------------------ | :------------------ | ----------: | :--------- | -----------: | :----------
25 |     83 | 2018-11-21 00:00:00 | 2018-12-01 15:27:00 |           0 | equal      |         1808 | greater    
26 |     94 | 2018-11-21 00:00:00 | 2018-12-01 15:23:00 |           0 | equal      |         1804 | greater    

SELECT
    c.*
    , TIMESTAMPDIFF(MONTH,'2018-11-30 09:18:23',c.end_time)  diff_month1
    , case when TIMESTAMPDIFF(MONTH,'2018-11-30 09:18:23',c.end_time) = 0 then 'equal'
           when TIMESTAMPDIFF(MONTH,'2018-11-30 09:18:23',c.end_time) > 0 then 'greater'
      end diff_month
    , TIMESTAMPDIFF(MINUTE,'2018-11-30 09:18:23',c.end_time) diff_minute1
    , case when TIMESTAMPDIFF(MINUTE,'2018-11-30 09:18:23',c.end_time) = 0 then 'equal'
           when TIMESTAMPDIFF(MINUTE,'2018-11-30 09:18:23',c.end_time) > 0 then 'greater'
      end diff_minute
FROM contract c
JOIN employee_detail e ON c.emp_id = e.id
where TIMESTAMPDIFF(MINUTE,'2018-11-30 09:18:23',c.end_time) = 0
  and TIMESTAMPDIFF(MONTH ,'2018-11-30 09:18:23',c.end_time) = 0

id | emp_id | sign_time | end_time | diff_month1 | diff_month | diff_minute1 | diff_minute
-: | -----: | :-------- | :------- | ----------: | :--------- | -----------: | :----------

db<>fiddle here

This trial is in MySQL 8.0, but I get the same inconsistency in MySQL 5.7.12 using rextester.com

Answer 2

You should not check if record withing specified time range like this as MySQL will not use indexes in this case. Try to alter const part and compare with column. Something like this:

WHERE

       e.stage != - 1
   AND c.end_time < DATE_SUB('2018-11-30 09:18:23', INTERVAL 1 MINUTE)
   AND c.end_time > DATE_SUB('2018-11-30 09:18:23', INTERVAL 1 MONTH)