18

I am getting this error when I try to insert '2011/03/13 02:53:50.000000000' into a timestamp column. If I change the 13 to a 15, 14, 12 or 11 it works no problem. I've also tried changing the /'s to -'s and still no-go.

I've looked through some of the other threads related to this error but none seem to apply.

I'm running version 5.7.9.

yivi
  • 42,438
  • 18
  • 116
  • 138
CycleGeek
  • 473
  • 2
  • 6
  • 14
  • Look at this.It will help you http://dev.mysql.com/doc/refman/5.7/en/datetime.html – Ankit Agrawal Feb 24 '16 at 13:06
  • The error message is 1292 Incorrect datetime value. – CycleGeek Feb 24 '16 at 13:07
  • @CycleGeek, can you please, provide some SQLfiddle snippet to illustrate this? – Farside Feb 24 '16 at 13:21
  • @Farside, here's my [SQL FIDDLE](http://sqlfiddle.com/#!9/4e1f9/1) and it seems to work. Weird? – CycleGeek Feb 24 '16 at 13:31
  • @CycleGeek, I've got v.5.7.9 on my localhost - and I don't see any issues. What's in your "show variables like 'sql_mode';"? – Farside Feb 24 '16 at 13:48
  • @Farside, only_full_group_by, strict_trans_tables, no_zero_in_date, error_for_division_by_zero, no_auto_create_user, no_engine_substitution. Also, I changed it the column to datetime(6), and it worked?! But it's still bugging me! – CycleGeek Feb 24 '16 at 13:56
  • its simple just go to accounts on auth with Heidesql not navicat only headesql u can config it easy data time update – Murat Tuncel Jun 24 '17 at 14:57

5 Answers5

57

It took me a while to figure this out...

The problem is that '2011-03-13 02:53:50' is illegal because of daylight saving time switch between 2 and 3 AM, so all time values between 2 and 3 am on any DST introduction day are invalid. Same for '2016-03-13 02:32:21', etc.

Change the system timezone to the one that does not use DST and you should be fine.

nik
  • 586
  • 4
  • 2
  • __@nik thanks for this__. I had not considered that the box I provisioned was stuck in EDT. Importing many thousands of records with timestamps and couldn't see anything wrong with the MySQL setup causing it to reject seemingly random values. – deefour Mar 26 '20 at 17:43
5

You need to try this:

STR_TO_DATE( '2011/03/13 02:53:50', '%Y/%m/%d %H:%i:%s')

or else you have to insert the dates using the dash seperator (-) like

'2011-03-13 02:53:50'

SQL FIDDLE DEMO

Rahul Tripathi
  • 168,305
  • 31
  • 280
  • 331
2

Still not sure what the issue is/was, maybe a combination of CentOS and MySQL versions. I changed the column to datatime(6) instead of timestamp(6) and I was able to import all my data successfully.

CycleGeek
  • 473
  • 2
  • 6
  • 14
1

I think you need to use some str conversions in MySQL before inserting. Or to prepare the data in the proper format, before making the query to MySQL.

The microseconds format is also wrong. MySQL documentation clearly states this:

A DATETIME or TIMESTAMP value can include a trailing fractional seconds part in up to microseconds (6 digits) precision.

UPDATE: on my localhost I've got the same version of MySQL, and it works. Tryed to execute conversion 

select str_to_date("2011-03-13 02:53:50.000000", "%Y-%m-%d %H:%i:%s.%f") as `t`

and gotten:

+----------------------------+
| t                          |
+----------------------------+
| 2011-03-13 02:53:50.000000 |
+----------------------------+
1 row in set (0.00 sec)

Here's the SQLFiddle, that confirms the thing on other version of MySQL.

I run out of ideas, I think the issue is connected to the "local glitch" in Table structure or specific version of MySQL+OS.

Farside
  • 9,923
  • 4
  • 47
  • 60
  • Then why, when I change the day to 15, 14, 12 or 11 does it work? I've also tried using 6 digits as well with no luck. – CycleGeek Feb 24 '16 at 13:16
  • @CycleGeek, do you get the correct conversions when the date is inserted, and then you try to select it back? I feel something is wrong with the string conversion itself – Farside Feb 24 '16 at 13:19
  • Yes if I insert '2011-03-12 02:53:50.000000' or '2011/03/12 02:53:50.000000' it works fine. A select shows a matching date. The 13 for some reason isn't working? – CycleGeek Feb 24 '16 at 13:23
  • 1
    @CycleGeek, try to use other formats, it's easy. Replace slashes with dashes, or use the STR_TO_DATE conversions. This will help in your case. The range for TIMESTAMP values is '1970-01-01 00:00:01.000000' to '2038-01-19 03:14:07.999999'. The fractional part should always be separated from the rest of the time by a decimal point – Farside Feb 24 '16 at 13:27
  • @CycleGeek, I checked on my end, also tryed SQLFiddle (http://sqlfiddle.com/#!9/9b758/1/0) , it works. Try to dump your DB, and to see what's wrong. – Farside Feb 24 '16 at 13:37
  • I tried str_to_date('2011-03-13 02:53:50.000000', '%Y/%m/%d %H:%i:%s.%f) and got the same error. But when I tried str_to_date('2011-03-1**2** 02:53:50.000000', '%Y/%m/%d %H:%i:%s.%f) it worked! – CycleGeek Feb 24 '16 at 13:39
  • @CycleGeek:- You are providing the format different and the dates different. Just try this: str_to_date('2011/03/13 02:53:50.000000', '%Y/%m/%d %H:%i:%s.%f) To be more clear, you are providing the dates using dash(-) and the format is using the (/) formatter – Rahul Tripathi Feb 24 '16 at 13:43
  • That was a typo, sorry. – CycleGeek Feb 24 '16 at 13:56
  • Maybe because the last number is in fact the month and not the day that's why it fails for number greater than 12 (=december) – Delphine Feb 24 '16 at 14:02
  • @Delphine, the last number is the day. 15 and 14 work fine. – CycleGeek Feb 24 '16 at 14:06
  • @CycleGeek Ok sorry I did not noticed that. Have you tried Farside update ? – Delphine Feb 24 '16 at 14:44
0

1292 (22007): Incorrect datetime value: '2004-10-11 19:08:58.503079+05:30' for column grideye.alerts.timestamp at row 1

if you are getting the above error must try this I written,

timestamp = parser.parse(data.get('timestamp'))

I tried this and this worked for me

timestamp = datetime.strptime(data.get('timestamp'), '%Y-%m-%dT%H:%M:%S.%f%z').strftime('%Y-%m-%d %H:%M:%S')
Alpha
  • 1,413
  • 3
  • 9
  • 23
Anvibabita Chandrakar
  • 1
  • 1