Mysql "ERROR 1292 (22007): Truncated incorrect time value: '2355:46:39.000000'" during insert

Question

I am currently computing the time difference between one datetime column and the lagged variable of another datetime column from a smaller table. Afterwards the result is inserted into a bigger, final table. This is part of a procedure where I have a few smaller tables as csv and for each of them the lag difference has to be computed and loaded into the final table (the final table is roughly 20GB and the 12 smaller tables are roughly 2.5GB each)

I have done the separate inserting without the lagged variable several times before and everything was fine. However in table 6 of 12 somewhere along the way I now get the following error and I cannot figure out why:

ERROR 1292 (22007): Truncated incorrect time value: '2355:46:39.000000'

I can provide a test example which worked for the rest of the tables:

DROP TABLE IF EXISTS single_test;

CREATE TABLE single_test(
medallion VARCHAR(64),
hack_license VARCHAR(64),
pickup_datetime DATETIME,
dropoff_datetime DATETIME,
id  INT NOT NULL,
PRIMARY KEY (id) 
);
INSERT INTO single_test VALUES
('a', '1' , '2013-01-06 00:18:35','2013-01-06 02:10:33',1),
('a', '1' , '2013-01-06 02:40:58','2013-01-06 03:40:01',2),
('b', '1' , '2013-01-06 04:07:21','2013-01-06 05:00:41',3),
('c', '1' , '2013-01-07 13:12:08','2013-01-07 13:32:27',4),
('a', '2', '2013-01-06 03:50:30','2013-01-06 04:22:13',5),
('a', '2', '2013-01-06 04:41:23','2013-01-06 04:57:04',6),
('d', '2', '2013-01-07 12:22:56','2013-01-07 13:02:14',7),
('d', '3', '2013-01-07 13:03:24','2013-01-07 15:47:31',8)
;


CREATE TABLE final_test(
medallion VARCHAR(64),
hack_license VARCHAR(64),
pickup_datetime DATETIME,
dropoff_datetime DATETIME,
id  INT NOT NULL,
delta VARCHAR(20),
current_dropoff DATETIME,
current_hack VARCHAR(64),
PRIMARY KEY (id) 
);

SET @quot= '000-00-00 19:19:19';
SET @current_hack = ''; 
INSERT INTO final_test
SELECT medallion, hack_license, pickup_datetime, dropoff_datetime, id,
IF(@current_hack = hack_license,TIMEDIFF(pickup_datetime,  @quot),NULL) as delta,
@quot:= dropoff_datetime current_dropoff, @current_hack:= hack_license
FROM single_test ORDER BY hack_license, pickup_datetime;

The result looks something like this:

SELECT * FROM final_test;
+-----------+--------------+---------------------+---------------------+----+-----------------+---------------------+--------------+
| medallion | hack_license | pickup_datetime     | dropoff_datetime    | id | delta           | current_dropoff     | current_hack |
+-----------+--------------+---------------------+---------------------+----+-----------------+---------------------+--------------+
| a         | 1            | 2013-01-06 00:18:35 | 2013-01-06 02:10:33 |  1 | NULL            | 2013-01-06 02:10:33 | 1            |
| a         | 1            | 2013-01-06 02:40:58 | 2013-01-06 03:40:01 |  2 | 00:30:25.000000 | 2013-01-06 03:40:01 | 1            |
| b         | 1            | 2013-01-06 04:07:21 | 2013-01-06 05:00:41 |  3 | 00:27:20.000000 | 2013-01-06 05:00:41 | 1            |
| c         | 1            | 2013-01-07 13:12:08 | 2013-01-07 13:32:27 |  4 | 32:11:27.000000 | 2013-01-07 13:32:27 | 1            |
| a         | 2            | 2013-01-06 03:50:30 | 2013-01-06 04:22:13 |  5 | NULL            | 2013-01-06 04:22:13 | 2            |
| a         | 2            | 2013-01-06 04:41:23 | 2013-01-06 04:57:04 |  6 | 00:19:10.000000 | 2013-01-06 04:57:04 | 2            |
| d         | 2            | 2013-01-07 12:22:56 | 2013-01-07 13:02:14 |  7 | 31:25:52.000000 | 2013-01-07 13:02:14 | 2            |
| d         | 3            | 2013-01-07 13:03:24 | 2013-01-07 15:47:31 |  8 | NULL            | 2013-01-07 15:47:31 | 3            |
+-----------+--------------+---------------------+---------------------+----+-----------------+---------------------+--------------+
8 rows in set (0,00 sec)

In contrast the ERROR message does not make much sense since I would expect TIMEDIFF to truncate any invalid input:

# Extremely Large difference
SELECT TIMEDIFF("2013-01-01 19:00:00","1900-01-01 19:00:00");
+-------------------------------------------------------+
| TIMEDIFF("2013-01-01 19:00:00","1900-01-01 19:00:00") |
+-------------------------------------------------------+
| 838:59:59                                             |
+-------------------------------------------------------+
1 row in set, 1 warning (0,00 sec)

# Invalid/ unrealistic datetime format due to to high/ to low values
SELECT TIMEDIFF("2013-01-01 19:00:00","000-00-00 19:19:19");
+------------------------------------------------------+
| TIMEDIFF("2013-01-01 19:00:00","000-00-00 19:19:19") |
+------------------------------------------------------+
| 838:59:59                                            |
+------------------------------------------------------+
1 row in set, 1 warning (0,00 sec)

# Invalid/ unrealistic datetime format due to character in values
SELECT TIMEDIFF("2013-01-01 19:00:00","000-00-00T 19:19:19");
+-------------------------------------------------------+
| TIMEDIFF("2013-01-01 19:00:00","000-00-00T 19:19:19") |
+-------------------------------------------------------+
| NULL                                                  |
+-------------------------------------------------------+
1 row in set, 1 warning (0,00 sec)

I am working with Mysql 5.7 .

I have also searched the smaller data for invalid characters from the alphabet but found nothing.

Best Regards

PS: I am aware of this SO thread, but it didn't provide any help Error Code: 1292. Truncated incorrect time value

Answer 1

The issue can be reproduced with the following script:

create table test(
  tdiff varchar(20)
);

set @dt1 = '1900-01-01 19:00:00';
set @dt2 = '2013-01-01 19:00:00';

select TIMEDIFF(@dt2, @dt1);

insert into test (tdiff) select TIMEDIFF(@dt2, @dt1);

While the SELECT statement returns 838:59:59, the INSERT statement with the same expression will raise an error:

Error: ER_TRUNCATED_WRONG_VALUE: Truncated incorrect time value: '990552:00:00'

You will have similar problems with queries like

insert into test (tdiff) select cast('abc' as char(2));

or

insert into test (tdiff) select '9999-12-31' + interval 1 day;

while the corresponding SELECT statements would return ab and NULL without errors.

The reason for the errors is the STRICT_TRANS_TABLES mode. We can argue, if that behavior makes sense - But I doubt that it will be changed.

So what can you do?

1. Use INSERT IGNORE ..

insert ignore into test (tdiff) select TIMEDIFF(@dt2, @dt1);

Using IGNORE after INSERT will convert those errors to warnings. This seems to be the simplest way.

2. Disable STRICT_TRANS_TABLES mode

You can disable the STRICT_TRANS_TABLES mode just for one statement:

set @old_sql_mode = @@sql_mode;
set session sql_mode = replace(@@sql_mode, 'STRICT_TRANS_TABLES', '');

<your INSERT statement here>;

set session sql_mode = @old_sql_mode;

3. Use a conditional expression

Since the valid range is from -838:59:59 to +838:59:59, we can check if the absolute difference in hours is less then 839 - Otherwise return some other value:

insert into test (tdiff) select 
  case when abs(timestampdiff(hour, @dt2, @dt1)) < 839
    then TIMEDIFF(@dt2, @dt1)
    else 'out of range'
  end

4. Save seconds instead of time

This would be my prefered solution. Use TIMESTAMPDIFF() to get the difference in seconds:

insert into test (tdiff) select timestampdiff(second, @dt1, @dt2);

Note that TIMESTAMPDIFF() is using a different parameter order than TIMEDIFF(). So the least DATETIME value should come first, if you want to get a positive result.