CODEWITHSUNDEEP
mysqlnode.jstimezone
János
János

Reputation: 35060

NodeJS responded MySQL timezone is different when I fetch directly from MySQL

When I request MySQL directly, I get back date in UTC (I set UTC in MySQL server), but with NodeJS I get UTC+2 local time zone data, why? How can I set NodeJS to get UTC?

enter image description here enter image description here

Upvotes: 28

Views: 39630

Answers (8)

Charles
Charles

Reputation: 455

i wrote a function for automatically resolving the timezone of the server it's being used from for mysql / mariadb, and made it into a tiny package for anyone interested: https://www.npmjs.com/package/mysql-tz

yarn add mysql-tz

import mariadb, { PoolConnection } from "mariadb";
import mysqlTz from "mysql-tz";

const maria_pool = mariadb.createPool({
  database: "SomeDatabase",
  user: 'username',
  password: 'password',
  timezone: mysqlTz(),
  connectionLimit: 25
});

for e.g., i'm on the east-coast of north america so calling "mysqlTz" would return "GMT-0400"

Upvotes: 0

Marco Blos
Marco Blos

Reputation: 1046

I know this question is already answered, and old, but if you are using mysql2 library and having problems setting the timezone to UTC, you can do it using "Z" timezone. Z stands for Zulu, explanation from here

const mysql = require('mysql2')
const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  database: 'test',
  password: 'mypass',
  timezone: 'Z',
})

Setting this timezone configuration means that every Date (Javascript Date object) will be translated TO utc when sent to database and FROM utc when it comes from the database. Here is the implementation, from mysql2 GitHub repository.

Upvotes: 8

Wilfredo Pomier
Wilfredo Pomier

Reputation: 1121

Old question, but it can be resolved setting the TZ environment variable to 'UTC+0'. There are several ways to achieve that, a few of them are:

  • In bash:
    • $ TZ='UTC+0' node index.js
  • Inside the code:
    • process.env.TZ = "UTC+0"
  • In Visual Code alter the launch.json configuration file. E.g:
    {
        "type": "node",
        "request": "launch",
        "name": "Launch Program",
        "program": "${fileDirname}/${fileBasename}",
        "env": {"TZ": "UTC+0"}  // <-- ADD THIS
    }

Upvotes: 1

DavidA
DavidA

Reputation: 628

after falling to that problem again and again i've found the desired solution.

My nodeJS app fetching data from mySQL db via Sequelize ORM.

Make sure the timezone is the same everywhere.

config.js:

const timezone = 'UTC'
process.env.TZ = timezone

sequelize_config.js:

const sequelize = new Sequelize(database, user, password, 
  options: {
    host,
    dialect: 'mysql',
    port,

    dialectOptions: {
       /* useUTC: true, **deprecated** */ 
       timezone: 'utc'
    },
  }
}

Hope it will save someone's time from falling to this loop... :)

Upvotes: 5

Aleks Towers
Aleks Towers

Reputation: 59

Try setting the timezone value to "UTC+0", that worked for me.

Upvotes: 3

Bala
Bala

Reputation: 33

You can also set the dateStrings property to DATETIME.

dateStrings: Force date types (TIMESTAMP, DATETIME, DATE) to be returned as strings rather then inflated into JavaScript Date objects. (Default: false)

Refer: https://github.com/mysqljs/mysql#connection-options

Upvotes: 2

Azias
Azias

Reputation: 587

Although this is an old question, I had a similar problem and adding the config timezone: 'utc' did not solve the problem (it get worse).

The solution I finally used is to add the config dateStrings : true such that I have a raw string date and mysql module does not do itself the conversion to a javascript date.

Then I use moment.utc(thedatestring) to obtain a suitable javascript object (in the database, I save all dates as UTC in DATETIME columns, independently of the configuration of the host). Using Moment.js.

Upvotes: 20

J&#225;nos
J&#225;nos

Reputation: 35060

I have added timezone in index.js when initializing mysql connection

var db_config = {
  host     : 'localhost',
  user     : 'xxx',
  password : '',
  database : 'xxx',
  timezone: 'utc'  //<-here this line was missing
};

Upvotes: 94

Related Questions