mysqltimeamazon-rdsnode-mysql2

CURRENT_TIME Timestamp is incorrect when inserting row in MySQL, I'm using AWS RDS


edit: this was a problem with the mysql library I was using node-mysql2

I am using AWS RDS to host a MySQL database.

There is a column in my database with the following definition

createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP

When I inserted a row at 4:43 pm EST I get the following value for that column

2018-12-27T02:43:32.000Z

When I try to convert this value to EST I get

12/26/2018, 9:43:32 PM

which is incorrect.

Am I doing something incorrect or is there something I need to configure?


Solution

  • This was not a problem with AWS or MySQL

    I figured out what the problem was, node-mysql2 decided to convert the timestamp. I don't understand what conversion it was doing or why.

    The setting I used to fix it was

    let options = {
        ...,
        timezone: 'UTC', // Interpret all received timestamps as UTC. Otherwise local timezone is assumed.
        dateStrings: [
            'DATE', // DATE's are returned as strings (otherwise they would be interpreted as YYYY-MM-DD 00:00:00+00:00)
            'DATETIME' // DATETIME's return as strings (otherwise they would interpreted as YYYY-MM-DD HH:mm:ss+00:00)
        ]
    }