MySQL Bugs: #16697: Erratic behaviour of TIMESTAMPDIFF()

Bug #16697	Erratic behaviour of TIMESTAMPDIFF()
Submitted:	21 Jan 2006 10:05	Modified:	21 Jan 2006 13:11
Reporter:	Julien Bonastre	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.15-log	OS:	Other (OpenBSD 3.7 i386-release)
Assigned to:		CPU Architecture:	Any

Description:
The behaviour and return values of this function seem to go astray depending on certain input conditions.

I have found it has trouble calculating periods of years and months or returns nonsense values.

The easiest way to explain is to demonstrate one of many variations one could use to strike upon this evil nasty, read below..

How to repeat:
Say I have two datetime arguments

TS1 = '2006-01-21 19:52:05'
TS2 = '2007-01-21 18:30:14'

Now we'd assume if I did a:
SELECT TIMESTAMPDIFF(MONTH, TS1, TS2);

we'd return with the value 12 ??

No, we get 0. Funnily enough the YEAR interval can be used instead and also returns 0.

Here's the results:
SELECT TIMESTAMPDIFF(YEAR, TS1, TS2) = 0
SELECT TIMESTAMPDIFF(DAY, TS1, TS2) = 364

And yet as can be seen the DAY interval returns 364, not far from a year there.
So say we use the same dates, but change the TS2's day from 21st to the 23rd

We can now return 12MONTH or 1YEAR.

BUT! We change the day on TS2 to an earlier value, say the 10th.

Or ANY value earlier than the date/month of TS1 to the SECOND and it will always return 0 MONTH and 0 YEAR

Even though it should really be something like 11 months and 3xx days

Suggested fix:
No idea.. Makes my life complex though now having to handle it all myself when there's already a function there.. *sigh* :-)

Thank you for a problem report. Looks like in current 5.0.19-BK (ChangeSet@1.1997, 2006-01-20 17:21:39+03:00) there is no such problem:

mysql> set @TS1 = '2006-01-21 19:52:05';
Query OK, 0 rows affected (0.00 sec)

mysql> set @TS2 = '2007-01-21 18:30:14';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT TIMESTAMPDIFF(MONTH, @TS1, @TS2);
+----------------------------------+
| TIMESTAMPDIFF(MONTH, @TS1, @TS2) |
+----------------------------------+
|                               12 |
+----------------------------------+
1 row in set (0.03 sec)

mysql> SELECT TIMESTAMPDIFF(YEAR, @TS1, @TS2);
+---------------------------------+
| TIMESTAMPDIFF(YEAR, @TS1, @TS2) |
+---------------------------------+
|                               1 |
+---------------------------------+
1 row in set (0.00 sec)

mysql> SELECT TIMESTAMPDIFF(DAY, @TS1, @TS2);
+--------------------------------+
| TIMESTAMPDIFF(DAY, @TS1, @TS2) |
+--------------------------------+
|                            364 |
+--------------------------------+
1 row in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.19    |
+-----------+
1 row in set (0.00 sec)

So, please, try to upgrade to 5.0.18, check once more and inform about the results.

Thank you so much for verifying this bug. Fantastic prompt response too, thank you again. I will try with latest version.