Migrating timezone timestamps

Question

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
                  test(
                    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

score 1 · Answer 1 · answered Mar 22 '20 at 22:06

You can do only one thing

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

2020-03-22 20:38:48.347

see jsfiddle

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

Migrating timezone timestamps

1 Answers1