MYSQL "timezone" connection option not working

[tareqdayya]

Issue type:

[ ] question [X] bug report [ ] feature request [] documentation issue

Database system/driver: MySql 2

[ ] cordova [ ] mongodb [ ] mssql [X] mysql / mariadb [ ] oracle [ ] postgres [ ] cockroachdb [ ] sqlite [ ] sqljs [ ] react-native [ ] expo

TypeORM version: 0.2.24 [X] latest [ ] @next [ ] 0.x.x (or put your version here)

Steps to reproduce or a small repository showing the problem: Hi everyone,

The timezone option in the connection options doesn't seem to be taking any effect. In the docs, it says that it's supported inside mysql / mariadb. I've tried using 'Z', 'local' and '+00:00' as values, as suggested by the docs, but i didn't notice any change. The timezone of the mysql server remained the same (local, AKA system_time_zone I believe).

As a workaround, right now, i'm using the following query on startup:

await connection.manager.query(
      'SET @@global.time_zone = \'+00:00\', @@session.time_zone = \'+00:00\';',
    );

Thanks,

[kazakovc]

I think at the moment, timezone is only supported for Postgres.

[Yunfly]

mysql doesn't support timezone now?

[Yunfly]

when I config timezone : "+08:00" on a config option, it doesn't work.

when query timezone in code :

 const res = await this.countRepo.query("show variables like '%time_zone%'")
 console.log({ res, })

// log
query: show variables like '%time_zone%'
{
  res: [
    RowDataPacket { Variable_name: 'system_time_zone', Value: 'UTC' },
    RowDataPacket { Variable_name: 'time_zone', Value: '+00:00' }
  ]
}

only if I set timezone in codes like: await this.repo.query("set time_zone='+08:00'") it works!

but when I log connectionConfig in mysql node_modules, it both show timezone: '+08:00'

what's going?

[DDDKnightmare]

I'm seeing this while using version 0.2.32.

According to the docs, timezone connectionOption works on mysql / mariadb.

typeorm's timezone connection option is used as an offset applied to read and saved Dates, not as the timezone used in the opened sessions, like:

typeorm timezone = "+08:00";
db SESSION.time_zone = "+08:00"

current time = '2021-06-08 00:00:000Z'

timestamp read from db (as char) = '2021-06-08 08:00:00.000';
// current_timestamp() (no TZ, read as if it were in UTC)

time on dateObject created from select = '2021-06-00:00:00.000Z'
// db timestamp (no tz) - typeorm.timezone

entity timestamp set to new Date(time) -> '2021-06-08 00:00:000Z'
timestamp persisted = '2021-06-08 08:00:00.000'

So I advise you to set typeorm's timezone = SESSION.time_zone; (to not change SESSION.time_zone every new session, set it to GLOBAL.time_zone ).

[plus1998]

Make sure that the time zone of MySQL running environment is consistent with that of typeorm

[DJEDAINI]

add this to connection configutation option:

timezone: '+8'

it should works then

[flyingcrp]

Actual.its not work. I lost 3h for this .

[kouhin]

I found that sequelize runs SET timezone every time a new connection is created.

https://github.com/sequelize/sequelize/blob/b382a3239137bebdef26912f75780041efa0ad98/src/dialects/mysql/connection-manager.ts#L118

This patch may work:

const AppDataSource = new DateSource({
  // ...
});

AppDataSource.driver = new Proxy(AppDataSource.driver, {
  set: (target, prop, value, receiver) => {
    let { timezone } = target.options;
    if (timezone) {
      const SET_TIMEZONE_QUERY = `SET time_zone = '${timezone}'`;
      if (prop === 'pool') {
        value.on('connection', (connection) => {
          connection.query(SET_TIMEZONE_QUERY);
        });
        value.query(SET_TIMEZONE_QUERY);
      }
    }
    return Reflect.set(target, prop, value, receiver);
  },
});

[aixiamomo]

timezone: '+8:00'

it should works then

[ganeshappentus]

no update on it from last 2 years

[richenlin]

set mysql database timezone:

set global time_zone = '+8:00'; set time_zone = '+8:00';

and typeorm database options:

const opt = {
    ...
    timezone: "Z" 
}

new DataSource(opt);