CODEWITHSUNDEEP
mysqlsql-timestampmysql-error-1292
CycleGeek
CycleGeek

Reputation: 501

MySQL 1292 Incorrect datetime value

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.

Upvotes: 20

Views: 92197

Answers (5)

Anvibabita Chandrakar
Anvibabita Chandrakar

Reputation: 1

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')

Upvotes: 0

nik
nik

Reputation: 616

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.

Upvotes: 60

CycleGeek
CycleGeek

Reputation: 501

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.

Upvotes: 2

Farside
Farside

Reputation: 10333

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.

Upvotes: 1

Rahul Tripathi
Rahul Tripathi

Reputation: 172448

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

Upvotes: 6

Related Questions