ER_TRUNCATED_WRONG_VALUE: Incorrect datetime value: '1545042106' for column 'test123' at row 1

Question

web3.eth.getBlock(12345).then(res => {
connection.query(`INSERT INTO blockdata(bnumber,btimestamp) VALUES ("${res.number}","${res.timestamp}");`)
});

I got timestamp already.

I wanna insert data to mysql but got an

Error: ER_TRUNCATED_WRONG_VALUE: Incorrect datetime value: '1545042106' for column 'btimestamp' at row 1

This doesn't seem to be Ethereum related. Probably a better fit for stackoverflow. In any case at first glance, it looks like the column expects the data in a different format, possibly in bytes judging by b in btimestamp. Check your database column data type. — Shiri, Dec 17 '18 at 14:12

score 1 · Accepted Answer · answered Dec 17 '18 at 14:45

As @Shiri's comment, that's a MySQL format error, not related to ethereum.

You can try using MySQL's FROM_UNIXTIME() function to convert the timestamp to a DATETIME:

web3.eth.getBlock(12345).then(res => {
    connection.query(`INSERT INTO blockdata(bnumber,btimestamp) VALUES ("${res.number}",FROM_UNIXTIME(${res.timestamp}));`)
});

ER_TRUNCATED_WRONG_VALUE: Incorrect datetime value: '1545042106' for column 'test123' at row 1

1 Answers1