Typeorm: may i know is the feature to convert date to UTC before saving to mysql is removed since 0.1.18. It was working fine in 0.1.17. I tried all subsequent version from 0.1.18 onward and none of it is working.

[cometta]

i created a small test case, can you help to run it and let's compare output

@Entity()
export class TestMysql {
  @PrimaryGeneratedColumn() id: number;

  @Column({ name: 'date_test', nullable: false })
  dateTest: Date;
}

        await getManager('main').transaction(async entityManager => {
          const testMysql = new TestMysql();
          testMysql['dateTest'] = new Date('2018-02-01 00:00:00+08');
          const saveResult = await entityManager.save(TestMysql, testMysql);
          console.log('-------saveResult-------');
          console.log(saveResult);

          const queryResult = await entityManager.query(
            `select * from test_mysql where id=${saveResult['id']}`
          );
          console.log('-------queryResult-------');
          console.log(queryResult);

          const findOneResult = await entityManager.findOne(TestMysql, { id: saveResult['id'] });
          console.log('-------findOneResult-------');
          console.log(findOneResult);

          const createQueryBuilderResult = await entityManager
            .createQueryBuilder(TestMysql, 'testMysql')
            .where({ id: saveResult['id'] })
            .getMany();
          console.log('-------createQueryBuilderResult-------');
          console.log(createQueryBuilderResult);
        });

0.1.17 without typeorm timezone option
-------saveResult-------
TestMysql { dateTest: 2018-01-31T16:00:00.000Z, id: 2 }
executing query: select * from test_mysql where id=2
-------queryResult-------
[ TextRow { id: 2, date_test: 2018-01-31T08:00:00.000Z } ] // not suppose to be this result ???
executing query: SELECT TestMysql.id AS TestMysql_id, TestMysql.date_test AS TestMysql_date_test FROM test_mysql TestMysql WHERE TestMysql.id=? -- PARAMETERS: [2]
-------findOneResult-------
TestMysql { id: 2, dateTest: 2018-01-31T16:00:00.000Z } //correct
executing query: SELECT testMysql.id AS testMysql_id, testMysql.date_test AS testMysql_date_test FROM test_mysql testMysql WHERE testMysql.id=? -- PARAMETERS: [2]
-------createQueryBuilderResult-------
[ TestMysql { id: 2, dateTest: 2018-01-31T16:00:00.000Z } ] //correct

0.1.19 with typeorm timezone option 'Z'
-------saveResult-------
TestMysql { dateTest: 2018-01-31T16:00:00.000Z, id: 3 }
executing query: select * from test_mysql where id=3
-------queryResult-------
[ TextRow { id: 3, date_test: 2018-01-31T08:00:00.000Z } ] //incorrect
executing query: SELECT TestMysql.id AS TestMysql_id, TestMysql.date_test AS TestMysql_date_test FROM test_mysql TestMysql WHERE TestMysql.id=? -- PARAMETERS: [3]
-------findOneResult-------
TestMysql { id: 3, dateTest: 2018-01-31T08:00:00.000Z } //incorrect
executing query: SELECT testMysql.id AS testMysql_id, testMysql.date_test AS testMysql_date_test FROM test_mysql testMysql WHERE testMysql.id=? -- PARAMETERS: [3]
-------createQueryBuilderResult-------
[ TestMysql { id: 3, dateTest: 2018-01-31T08:00:00.000Z } ] //incorrect
executing query: COMMIT

0.1.19 without typeorm timezone option . (all output are correct)
-------saveResult-------
TestMysql { dateTest: 2018-01-31T16:00:00.000Z, id: 3 }
executing query: select * from test_mysql where id=3
-------queryResult-------
[ TextRow { id: 3, date_test: 2018-01-31T16:00:00.000Z } ] //correct
executing query: SELECT TestMysql.id AS TestMysql_id, TestMysql.date_test AS TestMysql_date_test FROM test_mysql TestMysql WHERE TestMysql.id=? -- PARAMETERS: [3]
-------findOneResult-------
TestMysql { id: 3, dateTest: 2018-01-31T16:00:00.000Z } //correct
executing query: SELECT testMysql.id AS testMysql_id, testMysql.date_test AS testMysql_date_test FROM test_mysql testMysql WHERE testMysql.id=? -- PARAMETERS: [3]
-------createQueryBuilderResult-------
[ TestMysql { id: 3, dateTest: 2018-01-31T16:00:00.000Z } ] //correct
the last test outputs are correct, but, in mysql interface, it is saved as 2018-02-01 00:00:00 instead of UCT

[chriskalmar]

0.1.17 - without timezone config

-------saveResult-------
TestMysql { id: 4, dateTest: 2018-01-31T16:00:00.000Z }
executing query:  select * from test_mysql where id=4
-------queryResult-------
[ TextRow { id: 4, date_test: 2018-01-31T15:00:00.000Z } ]
executing query:  SELECT `TestMysql`.`id` AS `TestMysql_id`, `TestMysql`.`date_test` AS `TestMysql_date_test` FROM `test_mysql` `TestMysql` WHERE `TestMysql`.`id`=? -- PARAMETERS: [4]
-------findOneResult-------
TestMysql { id: 4, dateTest: 2018-01-31T16:00:00.000Z }
executing query:  SELECT `testMysql`.`id` AS `testMysql_id`, `testMysql`.`date_test` AS `testMysql_date_test` FROM `test_mysql` `testMysql` WHERE `testMysql`.`id`=? -- PARAMETERS: [4]
-------createQueryBuilderResult-------
[ TestMysql { id: 4, dateTest: 2018-01-31T16:00:00.000Z } ]
executing query:  COMMIT

0.1.19 - with timezone config 'Z'

-------saveResult-------
TestMysql { id: 6, dateTest: 2018-01-31T16:00:00.000Z }
executing query:  select * from test_mysql where id=6
-------queryResult-------
[ TextRow { id: 6, date_test: 2018-01-31T15:00:00.000Z } ]
executing query:  SELECT `TestMysql`.`id` AS `TestMysql_id`, `TestMysql`.`date_test` AS `TestMysql_date_test` FROM `test_mysql` `TestMysql` WHERE `TestMysql`.`id`=? -- PARAMETERS: [6]
-------findOneResult-------
TestMysql { id: 6, dateTest: 2018-01-31T15:00:00.000Z }
executing query:  SELECT `testMysql`.`id` AS `testMysql_id`, `testMysql`.`date_test` AS `testMysql_date_test` FROM `test_mysql` `testMysql` WHERE `testMysql`.`id`=? -- PARAMETERS: [6]
-------createQueryBuilderResult-------
[ TestMysql { id: 6, dateTest: 2018-01-31T15:00:00.000Z } ]
executing query:  COMMIT

0.1.19 - without timezone config

-------saveResult-------
TestMysql { id: 5, dateTest: 2018-01-31T16:00:00.000Z }
executing query:  select * from test_mysql where id=5
-------queryResult-------
[ TextRow { id: 5, date_test: 2018-01-31T16:00:00.000Z } ]
executing query:  SELECT `TestMysql`.`id` AS `TestMysql_id`, `TestMysql`.`date_test` AS `TestMysql_date_test` FROM `test_mysql` `TestMysql` WHERE `TestMysql`.`id`=? -- PARAMETERS: [5]
-------findOneResult-------
TestMysql { id: 5, dateTest: 2018-01-31T16:00:00.000Z }
executing query:  SELECT `testMysql`.`id` AS `testMysql_id`, `testMysql`.`date_test` AS `testMysql_date_test` FROM `test_mysql` `testMysql` WHERE `testMysql`.`id`=? -- PARAMETERS: [5]
-------createQueryBuilderResult-------
[ TestMysql { id: 5, dateTest: 2018-01-31T16:00:00.000Z } ]
executing query:  COMMIT

for me it's the same, mysql stores 2018-01-31 17:00:00.
but other than that it looks like it should work for you.

of course when you work in the database directly you will need to convert the timezone yourself.
e.g.:

SELECT id, date_test, CONVERT_TZ(date_test, '+8:00', 'UTC') FROM  test_mysql;

[cometta]

for your last case 0.1.19 - without timezone config , Aren't it not suppose to save in mysql as 2018-01-31 17:00:00 ? It should be stored in mysql as UTC right?

[chriskalmar]

ok, I think I know what's causing the difference. even on my side I had results that diverted from prior tests.
I was trying your mysql driver mysql2 and since then things stopped producing correct results.
try to remove mysql2, install mysql and set the timezone option to +0.

this should at least give you correct results with typeorm and on the database side as well.
once we've established that deciding on the right mysql driver will be a different topic altogether.

[chriskalmar]

looks like there is an easy fix for using mysql2.
mysql2 doesn't have a timezone setting in the connection config but it respects the TZ environment setting.

try this:

TZ=UTC  <your node command>

[cometta]

i did many round of tests. Below is the minimum changes that required to get it work on 0.1.19 onward
process.env.TZ=UTC
verified it saves in mysql as UTC and retrieve as UTC perfectly. No need to add tyeporm option timezone.

[chriskalmar]

great, I'm glad it finally works for you. 🎉
to sum it up for others that might have the same issue there are 2 solutions based on the underlying mysql driver in use:

1) with mysql:

set timezone option in typeorm to +0

2) with mysql2:

run the node process with the timezone environment variable TZ=UTC

I think you can close the issue now

[pleerock]

@chriskalmar thank you very much for your help!

[chriskalmar]

@pleerock happy to help 😃

[chanlito]

@chriskalmar OMG thanks a lot.

[pleerock]

@chriskalmar I actually faced this issue as well haha. So, solution is to add process.env.TZ = "UTC"; to resolve this issue and it works on any driver, am I right? If yes then probably we need to put more information in the docs about this, what do you think?

[chriskalmar]

@pleerock well, this is only mysql related, so it would make sense to mention both methods (timezone option and process.env.TZ) nearby the mysql connection docs.

I work most of the time with postgres and there I don't need to set any of those options. Postgres has a great timezone support built in and it works out of the box.
So it's not a general setting for all databases.

Maybe the best place to put it is something like Advanced Topics -> Timezone support.

[pleerock]

I also work with posgres and without process.env.TZ set to UTC it creates wrong dates (not wrong, it just treats dates read from your database as dates in a local timezone)

[chriskalmar]

What datatype are you using? timestamp with time zone or another one?
Because with timestamp with time zone it's still UTC.
I'm using a pretty standard postgres docker container (which is set to UTC by default). I moved it to another timezone and still everything get stored correctly in UTC. When fetching data I get the expected result too.
Did you send the date in full timezone notation? (e.g. 2018-05-08 12:00:00+2)

[hash]

I have 0.2.18 version and setting process.env.TZ to UTC does not help