Bug #1765 LOAD TABLE time-fields set to 00:00:00 when minutes in range 1-12

• Submitted: 6 Nov 2003 4:37
• Modified: 12 Nov 2003 14:22
• Reporter: Tore Martin Hansen
• Status: Not a Bug
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 4.0.16
• OS: Windows (Win XP Pro)
• CPU Architecture: Any

[6 Nov 2003 4:37] Tore Martin Hansen

Description:
When using LOAD TABLE to import data from an ascii-file, strings in the format 18:13:00.000000
is set to to 00:00:00 
when loaded into a 'time'-field
WHEN THE MINUTE PART IF THE 'TIME'-STRING IS IN THE RANGE 1-12

Have tried changing the minute-part of the time-fields to be imported and all 'time-values' are imported correctly EXCEPT when the minute-part is in the 01-12 range.

How to repeat:
create a table with one column/field of the type 'time'

use LOAD TABLE
into the table
from  file containing the following;
18:01:00.000000
18:10:00.000000
18:12:00.000000
18:13:00.000000
05:00:00.000000
05:12:00.000000
05:15:00.000000
.
.
.
.

and you'll see that the times with the described minute-values are set to 00:00:00 while the others are correctly imported.

Suggested fix:
Suspect this to be an erroneous 'month-test' that should only be done on a 'date'-field, when importing into a 'time'-field.

[12 Nov 2003 14:22] Dean Ellis

MySQL is recognizing some of your values as DATE/TIME strings and some as TIME strings, and setting the value appropriately (at least so far as it interprets the values).

The cause is simply that MySQL 4.0 only understands 'YYYY-MM-DD HH:MM:SS', 'YYYY-MM-DD' and 'HH:MM:SS' (delimeters being optional/flexible).  By not following its accepted format you have forced it to first consider your values as DATE/TIME strings.

Thank you.