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!

The return type is definitely not an integer but a big integer. Having said that, I can't see why MySQL has problem comparing them. — Salman A, Dec 03 '18 at 07:19

score 1 · Answer 1 · answered Dec 03 '18 at 04:32

1

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)

answered Dec 03 '18 at 04:32

fifonik

1,556
1
10
18

1

Actually, I want to find the contract which will expire in 1 month. Based on your suggestion, I edit the where condition like: where e.stage != - 1 AND c.end_time < DATE_ADD(now(), INTERVAL 1 MONTH) AND c.end_time > now() . If I use now() function in where clause, is it a good idea here? Another way, focus on the topic, could you please share something on why using '0' can get the right answer while integer 0 can't.Thanks for your answer! – J. Dr Dec 03 '18 at 06:44
Sorry for error. Did not have much time and missed that. Anyway, I gave you an idea how you usually should do this. Sure, you can use NOW() as an argument in DATE_ADD/DATE_SUB and it will be fine as server will calculate the value once and the compare the calculated value with every entry (or use index range). It would NOT calculate result of functions for every row as with your original where clause. – fifonik Dec 03 '18 at 20:37
BTW, You should consider to add index on contract.end_time. Also, you may use BETWEEN for better readability: "c.end_time BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL 1 MONTH)" – fifonik Dec 03 '18 at 20:43
Thanks for your proposal. There is still a lot of optimization of SQL statement to learn for me. ☺ – J. Dr Dec 04 '18 at 01:41

score 0 · Answer 2 · edited Jun 20 '20 at 09:12

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

Must be a query optimizer bug if it depends on the order of tests in `AND`. — Barmar, Dec 03 '18 at 06:52

Mysql 5.6 timestampdiff problem with return result

2 Answers2