The DATE change from MySQL to NodeJS

Question

Hi I´m using MySQL with NodeJS (https://www.npmjs.com/package/mysql)

In Node Js I´m using MomentJS (MomentJS)

I do an INSERT with a DATETIME, I get the date this way

incomes.registerdate = this.FechaRegistro.utc().format('YYYY-MM-DDTHH:mm:ss') //2019-11-20 22:02:42

The Date is storage correctly, but when I select the row in NodeJs the date change

RowDataPacket {
  item: '501021',
  description: 'CLAMP, 2" MUFFLER',
  price: 13,
  quantity: 13,
  rack: 'B-55',
  status: '1',
  registerdate: 2019-11-21T04:02:42.000Z,
  trackingnumber: 'test'
}

Why? I want get the same time that I inserted.

NodeJs is runing in localhost and MySQL is in a Server(Azure)

UPDATE

I found the problem.

The package mysql (https://www.npmjs.com/package/mysql), force date types (TIMESTAMP, DATETIME, DATE) to be returned like Object type Date(), but when NodeJS use the string got from MySQL is used as localtime, but is UTC time.

In the conection we have an option to force date types to be returned as strings

var mysql = require('mysql'),
    conf = require('./db-conf'),
    dbOptions = {
        host : conf.mysql.host,
        port : conf.mysql.port,
        user : conf.mysql.user,
        password : conf.mysql.pass,
        database : conf.mysql.db,
        dateStrings:true
    },
    myConn = mysql.createPool(dbOptions);


module.exports = myConn;

When I put this option in true all work fine, but how can I forse NodeJS to use the time in UTC instead of local

Answer 1

Best practice:

Always store datetime as UTC in the database.

incomes.registerdate = moment(this.FechaRegistro).toISOString()

When you display it in your app, use the UTC value returned from database and parse it with momentJS.

var display = moment('2019-11-21T04:02:42.000Z').format('YYYY-MM-DD H:ma').

It will parse accordingly to your app's timezone.. unless you specify otherwise.