Go to Qiita Advent Calendar 2024 Top
LoginSignup
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 1 year has passed since last update.

@kurukuruz

【備忘録】luxonとTypeORMとMySQL（とmysql2）とタイムゾーン

Last updated at Posted at 2023-05-21

はじめに

タイムゾーン検証記事第３弾にして本命のTypeORMの検証。

実行環境

  • MySQL 8.0
  • Node 18.13.0
  • TypeScript 5.0.4
  • typeorm 0.3.16
  • mysql 3.3.1
  • luxon 3.3.0

TL;DR

  • MySQLのタイムゾーンとNodeのタイムゾーンを同じにすべし
  • データ登録時においてluxonは有用

string, Date

Entity定義
@Entity()
export class DtTypeormDemo {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  label: string;

  @Column("datetime")
  dtStr: string;

  @Column("datetime")
  dtDate: Date;

  @CreateDateColumn()
  createdAt: Date;
}

create & save

レコード作成
const ds = await MyDataSource.initialize();
const repo = ds.getRepository(DtTypeormDemo);

const data = repo.create({
  label: `process: ${process.env.TZ}`,
  dtStr: "2023-04-01 12:00:00",
  dtDate: new Date(2023, 4, 1, 12, 0, 0),
});
console.log(data);

const row = await repo.save(data);
console.log(row);

await ds.destroy();
実行結果（JST）
DtTypeormDemo {
  id: undefined,
  label: 'process: Asia/Tokyo',
  dtStr: '2023-04-01 12:00:00',
  dtDate: 2023-05-01T03:00:00.000Z,
  createdAt: undefined
}
query: START TRANSACTION
query: INSERT INTO `dt_typeorm_demo`(`id`, `label`, `dtStr`, `dtDate`, `createdAt`) VALUES (DEFAULT, ?, ?, ?, DEFAULT) -- PARAMETERS: ["process: Asia/Tokyo","2023-04-01T03:00:00.000Z","2023-05-01T03:00:00.000Z"]
query: SELECT `DtTypeormDemo`.`id` AS `DtTypeormDemo_id`, `DtTypeormDemo`.`createdAt` AS `DtTypeormDemo_createdAt` FROM `dt_typeorm_demo` `DtTypeormDemo` WHERE `DtTypeormDemo`.`id` = ? -- PARAMETERS: [1]
query: COMMIT
DtTypeormDemo {
  id: 1,
  label: 'process: Asia/Tokyo',
  dtStr: '2023-04-01 12:00:00',
  dtDate: 2023-05-01T03:00:00.000Z,
  createdAt: 2023-05-21T09:47:23.461Z
}
実行結果（UTC）
DtTypeormDemo {
  id: undefined,
  label: 'process: UTC',
  dtStr: '2023-04-01 12:00:00',
  dtDate: 2023-05-01T12:00:00.000Z,
  createdAt: undefined
}
query: START TRANSACTION
query: INSERT INTO `dt_typeorm_demo`(`id`, `label`, `dtStr`, `dtDate`, `createdAt`) VALUES (DEFAULT, ?, ?, ?, DEFAULT) -- PARAMETERS: ["process: UTC","2023-04-01T12:00:00.000Z","2023-05-01T12:00:00.000Z"]
query: SELECT `DtTypeormDemo`.`id` AS `DtTypeormDemo_id`, `DtTypeormDemo`.`createdAt` AS `DtTypeormDemo_createdAt` FROM `dt_typeorm_demo` `DtTypeormDemo` WHERE `DtTypeormDemo`.`id` = ? -- PARAMETERS: [2]
query: COMMIT
DtTypeormDemo {
  id: 2,
  label: 'process: UTC',
  dtStr: '2023-04-01 12:00:00',
  dtDate: 2023-05-01T12:00:00.000Z,
  createdAt: 2023-05-21T18:51:55.452Z
}
  • stringで扱ってもDateで扱っても、レコード作成元になるインスタンス（data）とレコード作成に取得されたインスタンス（row）で差異はない。
  • サーバ上で生成された日時（createdAt）はMySQL上の日時文字列がNode側のタイムゾーンで解釈されるためMySQLのタイムゾーンとNodeのタイムゾーンが異なる場合はズレが生じる。（id: 2）

クライアントアプリで確認

show variables like '%time_zone%';
Variable_name Value
system_time_zone JST
time_zone SYSTEM 
select * from dt_typeorm_demo;
id label dtStr dtDate createdAt
1 process: Asia/Tokyo 2023-04-01 12:00:00 2023-05-01 12:00:00 2023-05-21 18:47:23.461879
2 process: UTC 2023-04-01 12:00:00 2023-05-01 12:00:00 2023-05-21 18:51:55.452754
  • MySQL側のタイムゾーンとNode側のタイムゾーンの差異を考慮せずにNode側の見たままの日時が登録されている。（id: 2）
    • トレースログのPARAMETERSでは正しいように見えたがDate型をログ出力する時と生のSQLに出力する時でロジックが異なっている…？

find

全レコード取得
const ds = await MyDataSource.initialize();
const repo = ds.getRepository(DtTypeormDemo);

const all = await repo.find();
console.log(all);

await ds.destroy();
実行結果（JST）
[
  DtTypeormDemo {
    id: 1,
    label: 'process: Asia/Tokyo',
    dtStr: 2023-04-01T03:00:00.000Z,
    dtDate: 2023-05-01T03:00:00.000Z,
    createdAt: 2023-05-21T09:47:23.461Z
  },
  DtTypeormDemo {
    id: 2,
    label: 'process: UTC',
    dtStr: 2023-04-01T03:00:00.000Z,
    dtDate: 2023-05-01T03:00:00.000Z,
    createdAt: 2023-05-21T09:51:55.452Z
  }
]
実行結果（UTC）
[
  DtTypeormDemo {
    id: 1,
    label: 'process: Asia/Tokyo',
    dtStr: 2023-04-01T12:00:00.000Z,
    dtDate: 2023-05-01T12:00:00.000Z,
    createdAt: 2023-05-21T18:47:23.461Z
  },
  DtTypeormDemo {
    id: 2,
    label: 'process: UTC',
    dtStr: 2023-04-01T12:00:00.000Z,
    dtDate: 2023-05-01T12:00:00.000Z,
    createdAt: 2023-05-21T18:51:55.452Z
  }
]
  • MySQLの日付文字列をそのままDateのコンストラクタ引数に入れた結果のDateインスタンスになっている
    • dtStrもDate型になっている
      • dateStringsの設定次第？
    • INSERT時とSELECT時とでタイムゾーンが異なるとズレる

luxon

DateTimeを暗黙的にMySQLのdatetime型に適合することはできないのでtransformerを定義する。
前述の結果からTypeORMから先はDate型にするのがよさそうなのでDate型とDateTime型を変換するよう定義する。

Entity定義
@Entity()
export class DtTypeormLuxonDemo {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  label: string;

  @Column("datetime", {
    transformer: {
      from: (value: Date) => DateTime.fromJSDate(value),
      to: (value: DateTime) => value.toJSDate(),
    },
  })
  dtDateTime: DateTime;

  @CreateDateColumn({
    transformer: {
      from: (value: Date) => DateTime.fromJSDate(value),
      to: (value?: DateTime) => value?.toJSDate(), // ★
    },
  })
  createdAt: DateTime;
}

★createdAtは本来Non Nullであるが、実際には新規レコードのためのEntity作成時に未定義になるのでundefinedを考慮する必要がある。

create & save

※実行結果が長いのでブロックごとに分割して記載する。

実行結果（JST） - 1
{"label":"process: Asia/Tokyo","dtDateTime":"2023-05-01T12:00:00.000+09:00"}
query: START TRANSACTION
query: INSERT INTO `dt_typeorm_luxon_demo`(`id`, `label`, `dtDateTime`, `createdAt`) VALUES (DEFAULT, ?, ?, DEFAULT) -- PARAMETERS: ["process: Asia/Tokyo","2023-05-01T03:00:00.000Z"]
query: SELECT `DtTypeormLuxonDemo`.`id` AS `DtTypeormLuxonDemo_id`, `DtTypeormLuxonDemo`.`createdAt` AS `DtTypeormLuxonDemo_createdAt` FROM `dt_typeorm_luxon_demo` `DtTypeormLuxonDemo` WHERE `DtTypeormLuxonDemo`.`id` = ? -- PARAMETERS: [1]
query: COMMIT
{"id":1,"label":"process: Asia/Tokyo","dtDateTime":"2023-05-01T12:00:00.000+09:00","createdAt":null}
実行結果（JST） - 2
{"label":"process: Asia/Tokyo, TZ: Asia/Tokyo","dtDateTime":"2023-05-01T12:00:00.000+09:00"}
query: START TRANSACTION
query: INSERT INTO `dt_typeorm_luxon_demo`(`id`, `label`, `dtDateTime`, `createdAt`) VALUES (DEFAULT, ?, ?, DEFAULT) -- PARAMETERS: ["process: Asia/Tokyo, TZ: Asia/Tokyo","2023-05-01T03:00:00.000Z"]
query: SELECT `DtTypeormLuxonDemo`.`id` AS `DtTypeormLuxonDemo_id`, `DtTypeormLuxonDemo`.`createdAt` AS `DtTypeormLuxonDemo_createdAt` FROM `dt_typeorm_luxon_demo` `DtTypeormLuxonDemo` WHERE `DtTypeormLuxonDemo`.`id` = ? -- PARAMETERS: [2]
query: COMMIT
{"id":2,"label":"process: Asia/Tokyo, TZ: Asia/Tokyo","dtDateTime":"2023-05-01T12:00:00.000+09:00","createdAt":null}
実行結果（JST） - 3
{"label":"process: Asia/Tokyo, TZ: UTC","dtDateTime":"2023-05-01T12:00:00.000Z"}
query: START TRANSACTION
query: INSERT INTO `dt_typeorm_luxon_demo`(`id`, `label`, `dtDateTime`, `createdAt`) VALUES (DEFAULT, ?, ?, DEFAULT) -- PARAMETERS: ["process: Asia/Tokyo, TZ: UTC","2023-05-01T12:00:00.000Z"]
query: SELECT `DtTypeormLuxonDemo`.`id` AS `DtTypeormLuxonDemo_id`, `DtTypeormLuxonDemo`.`createdAt` AS `DtTypeormLuxonDemo_createdAt` FROM `dt_typeorm_luxon_demo` `DtTypeormLuxonDemo` WHERE `DtTypeormLuxonDemo`.`id` = ? -- PARAMETERS: [3]
query: COMMIT
{"id":3,"label":"process: Asia/Tokyo, TZ: UTC","dtDateTime":"2023-05-01T12:00:00.000Z","createdAt":null}
実行結果（UTC） - 1
{"label":"process: UTC","dtDateTime":"2023-05-01T12:00:00.000+00:00"}
query: START TRANSACTION
query: INSERT INTO `dt_typeorm_luxon_demo`(`id`, `label`, `dtDateTime`, `createdAt`) VALUES (DEFAULT, ?, ?, DEFAULT) -- PARAMETERS: ["process: UTC","2023-05-01T12:00:00.000Z"]
query: SELECT `DtTypeormLuxonDemo`.`id` AS `DtTypeormLuxonDemo_id`, `DtTypeormLuxonDemo`.`createdAt` AS `DtTypeormLuxonDemo_createdAt` FROM `dt_typeorm_luxon_demo` `DtTypeormLuxonDemo` WHERE `DtTypeormLuxonDemo`.`id` = ? -- PARAMETERS: [4]
query: COMMIT
{"id":4,"label":"process: UTC","dtDateTime":"2023-05-01T12:00:00.000+00:00","createdAt":null}
実行結果（UTC） - 2
{"label":"process: UTC, TZ: Asia/Tokyo","dtDateTime":"2023-05-01T12:00:00.000+09:00"}
query: START TRANSACTION
query: INSERT INTO `dt_typeorm_luxon_demo`(`id`, `label`, `dtDateTime`, `createdAt`) VALUES (DEFAULT, ?, ?, DEFAULT) -- PARAMETERS: ["process: UTC, TZ: Asia/Tokyo","2023-05-01T03:00:00.000Z"]
query: SELECT `DtTypeormLuxonDemo`.`id` AS `DtTypeormLuxonDemo_id`, `DtTypeormLuxonDemo`.`createdAt` AS `DtTypeormLuxonDemo_createdAt` FROM `dt_typeorm_luxon_demo` `DtTypeormLuxonDemo` WHERE `DtTypeormLuxonDemo`.`id` = ? -- PARAMETERS: [5]
query: COMMIT
{"id":5,"label":"process: UTC, TZ: Asia/Tokyo","dtDateTime":"2023-05-01T12:00:00.000+09:00","createdAt":null}
実行結果（UTC） - 3
{"label":"process: UTC, TZ: UTC","dtDateTime":"2023-05-01T12:00:00.000Z"}
query: START TRANSACTION
query: INSERT INTO `dt_typeorm_luxon_demo`(`id`, `label`, `dtDateTime`, `createdAt`) VALUES (DEFAULT, ?, ?, DEFAULT) -- PARAMETERS: ["process: UTC, TZ: UTC","2023-05-01T12:00:00.000Z"]
query: SELECT `DtTypeormLuxonDemo`.`id` AS `DtTypeormLuxonDemo_id`, `DtTypeormLuxonDemo`.`createdAt` AS `DtTypeormLuxonDemo_createdAt` FROM `dt_typeorm_luxon_demo` `DtTypeormLuxonDemo` WHERE `DtTypeormLuxonDemo`.`id` = ? -- PARAMETERS: [6]
query: COMMIT
{"id":6,"label":"process: UTC, TZ: UTC","dtDateTime":"2023-05-01T12:00:00.000Z","createdAt":null}

クライアントアプリで確認

select * from dt_typeorm_luxon_demo;
id label dtDateTime createdAt
1 process: Asia/Tokyo 2023-05-01 12:00:00 2023-05-21 21:36:37.872693
2 process: Asia/Tokyo, TZ: Asia/Tokyo 2023-05-01 12:00:00 2023-05-21 21:36:37.894495
3 process: Asia/Tokyo, TZ: UTC 2023-05-01 21:00:00 2023-05-21 21:36:37.905650
4 process: UTC 2023-05-01 12:00:00 2023-05-21 21:41:38.757159
5 process: UTC, TZ: Asia/Tokyo 2023-05-01 03:00:00 2023-05-21 21:41:38.784857
6 process: UTC, TZ: UTC 2023-05-01 12:00:00 2023-05-21 21:41:38.794379
  • MySQLのタイムゾーンとNodeのタイムゾーンの差異は考慮されず見たままの日時が登録される（id: 4）
  • NodeのタイムゾーンとDateTimeインスタンスが持つタイムゾーンが異なる場合はNodeのタイムゾーンでの日時に変換してから登録される（id: 2, 5）

find

全レコード取得
const ds = await MyDataSource.initialize();
const repo = ds.getRepository(DtTypeormLuxonDemo);

const all = await repo.find();
console.log(JSON.stringify(all, null, 2));

await ds.destroy();
実行結果（JST）
[
  {
    "id": 1,
    "label": "process: Asia/Tokyo",
    "dtDateTime": "2023-05-01T12:00:00.000+09:00",
    "createdAt": "2023-05-21T21:36:37.872+09:00"
  },
  {
    "id": 2,
    "label": "process: Asia/Tokyo, TZ: Asia/Tokyo",
    "dtDateTime": "2023-05-01T12:00:00.000+09:00",
    "createdAt": "2023-05-21T21:36:37.894+09:00"
  },
  {
    "id": 3,
    "label": "process: Asia/Tokyo, TZ: UTC",
    "dtDateTime": "2023-05-01T21:00:00.000+09:00",
    "createdAt": "2023-05-21T21:36:37.905+09:00"
  },
  {
    "id": 4,
    "label": "process: UTC",
    "dtDateTime": "2023-05-01T12:00:00.000+09:00",
    "createdAt": "2023-05-21T21:41:38.757+09:00"
  },
  {
    "id": 5,
    "label": "process: UTC, TZ: Asia/Tokyo",
    "dtDateTime": "2023-05-01T03:00:00.000+09:00",
    "createdAt": "2023-05-21T21:41:38.784+09:00"
  },
  {
    "id": 6,
    "label": "process: UTC, TZ: UTC",
    "dtDateTime": "2023-05-01T12:00:00.000+09:00",
    "createdAt": "2023-05-21T21:41:38.794+09:00"
  }
]
実行結果（UTC）
[
  {
    "id": 1,
    "label": "process: Asia/Tokyo",
    "dtDateTime": "2023-05-01T12:00:00.000+00:00",
    "createdAt": "2023-05-21T21:36:37.872+00:00"
  },
  {
    "id": 2,
    "label": "process: Asia/Tokyo, TZ: Asia/Tokyo",
    "dtDateTime": "2023-05-01T12:00:00.000+00:00",
    "createdAt": "2023-05-21T21:36:37.894+00:00"
  },
  {
    "id": 3,
    "label": "process: Asia/Tokyo, TZ: UTC",
    "dtDateTime": "2023-05-01T21:00:00.000+00:00",
    "createdAt": "2023-05-21T21:36:37.905+00:00"
  },
  {
    "id": 4,
    "label": "process: UTC",
    "dtDateTime": "2023-05-01T12:00:00.000+00:00",
    "createdAt": "2023-05-21T21:41:38.757+00:00"
  },
  {
    "id": 5,
    "label": "process: UTC, TZ: Asia/Tokyo",
    "dtDateTime": "2023-05-01T03:00:00.000+00:00",
    "createdAt": "2023-05-21T21:41:38.784+00:00"
  },
  {
    "id": 6,
    "label": "process: UTC, TZ: UTC",
    "dtDateTime": "2023-05-01T12:00:00.000+00:00",
    "createdAt": "2023-05-21T21:41:38.794+00:00"
  }
]
  • MySQLのタイムゾーンとの差異は考慮されず、日時文字列をNodeのタイムゾーンでの日時として取得される。
  • INSERT時とSELECT時とでタイムゾーンが異なるとズレる
0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?