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

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

CodePudding user response：

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
  });