[MDEV-8205] timediff returns null when comparing decimal time to time string value Created: 2015-05-21  Updated: 2015-06-15  Resolved: 2015-06-15

Status: Closed
Project: MariaDB Server
Component/s: Temporal Types
Affects Version/s: 10.0.18
Fix Version/s: 10.0.20

Type: Bug Priority: Major
Reporter: Mark Punak Assignee: Alexander Barkov
Resolution: Fixed Votes: 0
Labels: decimal, timediff
Environment:

CentOS 7

Sprint: 10.0.20

 Description   

timediff function does not correctly convert decimal values to datetime when comparing a decimal time to a string time, returning null. Problem does not manifest in the datediff function.

select 
	timediff('2014-01-01 00:00:00' , '2014-01-01 01:00:00' ),
 
    timediff(20140101000000.000 , 20140101010000.000  ),
 
	timediff(20140101000000.000 , '2014-01-01 01:00:00' ),
 
	datediff('2014-01-01 00:00:00' , '2014-01-02 01:00:00' ),
 
    datediff(20140101000000.000 , 20140102010000.000  ),
 
	datediff(20140101000000.000 , '2014-01-02 01:00:00' );



 Comments   
Comment by Elena Stepanova [ 2015-05-21 ]

Thanks for the report.

Comment by Alexander Barkov [ 2015-06-10 ]

There is a related problem.

In string notation, TIMEDIFF takes into account the date part:

MariaDB [test]> select timediff('2001-01-01 10:20:30','2001-03-02 10:20:31');
 
+-------------------------------------------------------+
 
| timediff('2001-01-01 10:20:30','2001-03-02 10:20:31') |
 
+-------------------------------------------------------+
 
| -838:59:59                                            |
 
+-------------------------------------------------------+
 
1 row in set, 1 warning (0.00 sec)

while in numeric notation it ignores the date part:

MariaDB [test]> select timediff(20010101102030,20010302102031);
 
+-----------------------------------------+
 
| timediff(20010101102030,20010302102031) |
 
+-----------------------------------------+
 
| -00:00:01                               |
 
+-----------------------------------------+
 
1 row in set (0.00 sec)

Generated at Thu Feb 08 07:25:25 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.