Bug #31990 MINUTE() and SECOND() return bogus results when used on a DATE
Submitted: 31 Oct 2007 15:16 Modified: 11 Jan 2008 15:57
Reporter: Edam Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0, 5.1 OS:Linux
Assigned to: Tatiana Azundris Nuernberg CPU Architecture:Any
Tags: date, minute, second
Triage: D2 (Serious)

[31 Oct 2007 15:16] Edam
Description:
It appears that using MINUTE() and SECOND() (and perhaps HOUR()?) on a DATE yields some funny results.

The behaviour looks to be as expected. MySQL appears to convert the dates to strings before attempting to extract seconds/minutes. It then tries it's hardest to find minutes/seconds in a string that doesn't really contain any meaningful data and returns it's best-guess along with warnings that the input was truncated in the process.

While this behaviour may seem as expected, it doesn't seem intuitive to me, given the context of a DATE being passed in. I would have expected them to return 0.

The exact version of MySQL I am using is 5.0.32-Debian_7etch1-log, on Debian obviously.

How to repeat:
mysql> SELECT SECOND( CAST( NOW() AS DATE ) ), MINUTE( DATE( NOW() ) );
+---------------------------------+-------------------------+
| SECOND( CAST( NOW() AS DATE ) ) | MINUTE( DATE( NOW() ) ) |
+---------------------------------+-------------------------+
|                               7 |                      20 |
+---------------------------------+-------------------------+
1 row in set, 2 warnings (0.00 sec)

mysql> SHOW WARNINGS;
+---------+------+----------------------------------------------+
| Level   | Code | Message                                      |
+---------+------+----------------------------------------------+
| Warning | 1292 | Truncated incorrect time value: '2007-10-31' |
| Warning | 1292 | Truncated incorrect time value: '2007-10-31' |
+---------+------+----------------------------------------------+
2 rows in set (0.00 sec)
[1 Nov 2007 10:07] Hartmut Holzgraefe
Verified, same on 5.1, too.

IMHO HOUR(), MINUTE(), SECOND() on DATE values should return NULL ...
[8 Nov 2007 5:08] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/37305

ChangeSet@1.2557, 2007-11-08 06:08:44+01:00, tnurnberg@mysql.com +4 -0
  Bug#31990: MINUTE() and SECOND() return bogus results when used on a DATE
  
  HOUR(), MINUTE(), ... returned spurious results when used on a DATE-cast.
  This happened because DATE-cast object did not overload get_time() method
  in superclass Item. The default method was inappropriate here and
  misinterpreted the data.
  
  Patch adds missing method; get_time() on DATE-casts now returns SQL-NULL
  on NULL input, 0 otherwise. This coincides with the way DATE-columns
  behave.
[10 Nov 2007 12:05] Tatiana Azundris Nuernberg
pushed to 5.0.52, 5.1.23 in opt
[16 Nov 2007 9:31] Bugs System
Pushed into 5.0.52
[16 Nov 2007 9:33] Bugs System
Pushed into 5.1.23-rc
[16 Nov 2007 9:35] Bugs System
Pushed into 6.0.4-alpha
[11 Dec 2007 9:13] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/39692

ChangeSet@1.2560, 2007-12-11 10:12:05+01:00, tnurnberg@mysql.com +8 -0
  Bug#31990: MINUTE() and SECOND() return bogus results when used on a DATE
  
  HOUR(), MINUTE(), ... returned spurious results when used on a DATE-cast.
  This happened because DATE-cast object did not overload get_time() method
  in superclass Item. The default method was inappropriate here and
  misinterpreted the data.
  
  Patch adds missing method; get_time() on DATE-casts now returns SQL-NULL
  on NULL input, 0 otherwise. This coincides with the way DATE-columns
  behave.
  
  Also fixes similar bug in Date-Field now.
[20 Dec 2007 7:16] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/40250

ChangeSet@1.2674, 2007-12-20 08:15:34+01:00, tnurnberg@mysql.com +1 -0
  Bug#31990: MINUTE() and SECOND() return bogus results when used on a DATE
  
  post-merge fixes
[28 Dec 2007 12:27] Tatiana Azundris Nuernberg
pushed on 12/20 to 5.0.54-opt, 5.1.23-opt, 6.0.5-opt
[11 Jan 2008 12:18] Bugs System
Pushed into 6.0.5-alpha
[11 Jan 2008 12:21] Bugs System
Pushed into 5.1.23-rc
[11 Jan 2008 12:22] Bugs System
Pushed into 5.0.56
[11 Jan 2008 15:57] Paul Dubois
Noted in 5.0.56, 5.1.23, 6.0.5 changelogs.

HOUR(), MINUTE(), and SECOND() could return non-zero values for DATE
arguments.