Knex returns DATETIME fields applying offset of my machine timezone

Question

I have following row saved in timezone Europe/Madrid at database:

dateA = '2019-03-26 15:00:00'
dateB = '2019-03-26 14:00:00'

When selecting the entry:

let entry = this.query().findById(id)

I get following values (console.log(entry)):

{ 
  dateA: 2019-03-26T06:00:00.000Z,
  dateB: 2019-03-26T05:00:00.000Z 
}

What is applying this convertion?

My enviroment:

Im using knex, objection and moment
Moment is configured with moment.tz.setDefault('Europe/Madrid')
My local machine timezone is set to UTC+09:00 (Chita) for testing

I tried:

Setting a connection timezone on knex creation:

const connection = {
  user: dbCreds.username,
  password: dbCreds.password,
  host: hostname,
  database: dbCreds.database,
  timezone: '-1:00'
}
const pool = knex({
  client: 'mysql2',
  connection,
  pool: {
    min: 2,
    max: 30
  }
})

I still get the same result

score 0 · Accepted Answer · answered Mar 27 '19 at 10:50

Finally found out whats going on.

Knex, when retrieving the info of the datetime field checks the timezone of the database. You can check it using:

SELECT @@global.time_zone, @@session.time_zone;

This returns SYSTEM SYSTEM in my case. So its using my machine timezone. To test it, I changed it to UTC.

So now, when reading the following value:

dateA = '2019-03-26 15:00:00'
dateB = '2019-03-26 14:00:00'

Knex assumes its in UTC, so the value in UTC is returned:

{ 
  dateA: 2019-03-26T15:00:00.000Z,
  dateB: 2019-03-26T14:00:00.000Z 
}

Seems not possible to configure MySQL to use 'Europe/Madrid' as default timezone.

Knex returns DATETIME fields applying offset of my machine timezone

1 Answers1