Search code examples
mysqlnode.jsdatetimelambda

MYSQL Select query from Node JS is giving wrong value for datetime column

Datetime value saved column START_DATE in mysql table : 2022-01-01 00:00:00

Response object after executing select query

SELECT * FROM tablename

RowDataPacket { ID: 1, START_DATE: 2021-12-31T16:00:00.000Z, },

here mysql is auto converted the date and given a wrong date value as a response.That is 2021-12-31T16:00:00.000Z instead of 2022-01-01 00:00:00. Anyone know the reason for this issue and how we can fix the same ? Note : DB is connected from a Node JS lambda application using mysql pool connection

Solution

  • The issue is with the timezone. While retrieving the datetime from database it is converting to GMT. To fix this issue we need to mention timezone as 'utc' while establishing the connection

    var connection = mysql.createConnection({
    host: '192.0.0.1',
    user: 'admin',
    password: 'admin123',
    database: 'mydb',
    timezone: 'utc' //<-- Add timezone
  });