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

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

https://qiita.com/kurukuruz/items/1b3082aababee4947168

https://qiita.com/kurukuruz/items/d5b6c1a36ea2f4ffde61

### 実行環境

- 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
```ts:Entity定義
@Entity()
export class DtTypeormDemo {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  label: string;

  @Column("datetime")
  dtStr: string;

  @Column("datetime")
  dtDate: Date;

  @CreateDateColumn()
  createdAt: Date;
}
```
## create & save
```ts:レコード作成
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();
```

```zsh:実行結果（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
}
```

```zsh:実行結果（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）

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

```sql
show variables like '%time_zone%';
```

|Variable_name|Value|
|---|---|
|system_time_zone|JST|
|time_zone|SYSTEM|

```sql
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

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

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

await ds.destroy();
```

```zsh:実行結果（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
  }
]
```

```zsh:実行結果（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型を変換するよう定義する。

```ts: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

※実行結果が長いのでブロックごとに分割して記載する。
```zsh:実行結果（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}
```
```zsh:実行結果（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}
```
```zsh:実行結果（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}
```
```zsh:実行結果（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}
```
```zsh:実行結果（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}
```
```zsh:実行結果（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}
```

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

```sql
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

```ts:全レコード取得
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();
```

```zsh:実行結果（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"
  }
]
```

```zsh:実行結果（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時とでタイムゾーンが異なるとズレる**