I am trying to migrate this node library https://github.com/LocalSEOGuide/lighthouse-reporter from Postgres to MySQL and getting an error error: ER_TRUNCATED_WRONG_VALUE: Incorrect datetime value: '2020-03-22T20:38:48.347Z' for column 'fetch_time' at row 1

I've isolated the query where the error happens and built a little test:

     await db.query('drop table test');
     await db.query(`CREATE TABLE IF NOT EXISTS
                    id SERIAL PRIMARY KEY,
                    fetch_time datetime

    const query_text = "INSERT INTO test SET ?"
    const query_params = { "fetch_time":"2020-03-22T20:38:48.347Z" }
    await db.query(query_text, query_params);

It seems that the reporter is returning a timestamp with a Z at the end, but i cant figure out how to get it properly stored in to mysql. Any suggestions would be very helpful!

I've also tried with fetch_time as a timestamp

  • Did it get properly stored? I see ? versus fetch_time. – Rick James Apr 3 '20 at 3:17

You can do only one thing

var mystring = "2020-03-22T20:38:48.347Z";
mystring = mystring.replace(/Z/, '');
mystring = mystring.replace(/T/, ' ');

2020-03-22 20:38:48.347

see jsfiddle

Replace the T and the Z the resulting string is no problem for Mysql

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.