Bug #70188 Error Code : 1292 Incorrect datetime value '2013-03-10 02:01:03
Submitted: 29 Aug 2013 21:33 Modified: 30 Aug 2013 5:15
Reporter: Craig David Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Errors Severity:S3 (Non-critical)
Version:5.6.12 Win32 (x86) OS:Microsoft Windows
Assigned to: CPU Architecture:Any

[29 Aug 2013 21:33] Craig David
Description:
I have a table with a timestamp column (type timestamp) and I try to insert the following into the table.

The follow does not work
insert into backup.test (timestamp) values ('2013-03-10 02:01:03')
insert into backup.test (timestamp) values ('2013-03-10 02:02:03')

Data Truncation error occured on a write  of column 0Data was 0 bytes long and 0 bytes were transferred.

This works.
insert into backup.test (timestamp) values ('2013-03-11 02:01:03')
insert into backup.test (timestamp) values ('2013-03-09 02:01:03')
insert into backup.test (timestamp) values ('2013-03-10 12:01:03')

How to repeat:
Create a single timestamp column in a table. 

I have created a simple table with only one column (timestamp) and it fails.

insert into backup.test (timestamp) values ('2013-03-10 02:01:03')
insert into backup.test (timestamp) values ('2013-03-10 02:02:03')

There might other combinations but i think you get the idea.
[29 Aug 2013 21:57] Miguel Solorzano
Thank you for the bug report. Please check for timezone issue. Thanks.

Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.6.13 MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database backup;
Query OK, 1 row affected (0.02 sec)

mysql> create table backup.test (timestamp timestamp);
Query OK, 0 rows affected (0.38 sec)

mysql> insert into backup.test (timestamp) values ('2013-03-10 02:01:03');
Query OK, 1 row affected (0.05 sec)

mysql> insert into backup.test (timestamp) values ('2013-03-10 02:02:03');
Query OK, 1 row affected (0.06 sec)

mysql> select * from backup.test;
+---------------------+
| timestamp           |
+---------------------+
| 2013-03-10 02:01:03 |
| 2013-03-10 02:02:03 |
+---------------------+
2 rows in set (0.00 sec)
[29 Aug 2013 23:04] Craig David
Thank for quick response. I noticed that you were using 5.6.13 and not 5.6.12.. 
I'm not sure how the timezone could an issue as the month and day that fail are under 12.. 
Can you please let me know what and how I should work with timezones.. Only new to this MySQL.. 

The process that runs runs fine on 80,000 record until it reaches this date and time combination.

Any assistance with timezones would be great.
[30 Aug 2013 0:42] Craig David
Good news. I have worked it.

It was the timezone..
I issued the following and it works...

SELECT @@global.time_zone, @@session.time_zone;

SET global time_zone = '-5:00'; 

Thanks for you help.. I would of never thought of the timezone issue..

I guess you can close this case as not a bug.

Thanks