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

# はじめに

DBでの日時の取り扱いはややこしい。
業務で使っているMySQL・Nodeでどう書いたらどう登録／取得されるのかを備忘録としてまとめた。

### 実行環境

- MySQL 8.0（dockerの`mysql:8.0`）
- Node 18.13.0
- TypeScript 5.0.4
- mysql2 3.3.1

# TL;DR
- 時差情報のない文字列をINSERTする場合はタイムゾーンには依存せず見たままの日時文字列がそのまま登録される
- 時差情報を含む文字列をINSERTする場合はセッションのタイムゾーンでの日時に変換されてから登録される
- SELECT時のタイムゾーンには依存せず登録された日時文字列がそのまま取得される

### mysql2
- デフォルトでセッションのタイムゾーン=サーバ(接続先であるMySQL)のタイムゾーンになる
- ***コネクション作成時のdatetimeオプションは効かない***（`mysql2@3.3.1`現在）
- 文字列として扱う場合、生SQLで実行するのと同じになる
- **Dateオブジェクトとして扱う場合**、NodeのタイムゾーンでDateが解釈するが、MySQLとのやりとりにはタイムゾーンの情報が乗らないので**ズレが生じるケースがある。**

# 事前準備
datetime型カラムを持つテーブルを用意する。
```sql
create table dt_demo (
  id serial primary key,
  label varchar(255),
  date_time datetime
);
```
# 生SQL実行
## サーバのタイムゾーン=JST
### INSERT
#### セッションのタイムゾーン=Asia/Tokyo
```sql
show variables like '%time_zone%';
```

|Variable_name|Value|
|---|---|
|system_time_zone|JST|
|time_zone|Asia/Tokyo|

```sql
insert into dt_demo(label, date_time) values
('session: Asia/Tokyo, TZ: undefined', '2023-04-01 12:00:00'),
('session: Asia/Tokyo, TZ: UTC+9',     '2023-04-01 12:00:00+09:00'),
('session: Asia/Tokyo, TZ: UTC',       '2023-04-01 12:00:00+00:00');
```

#### セッションのタイムゾーン=GMT
```sql
show variables like '%time_zone%';
```
|Variable_name|Value|
|---|---|
|system_time_zone|JST|
|time_zone|GMT|

```sql
insert into dt_demo(label, date_time) values
('session: GMT, TZ: undefined', '2023-04-01 12:00:00'),
('session: GMT, TZ: UTC+9',     '2023-04-01 12:00:00+09:00'),
('session: GMT, TZ: UTC',       '2023-04-01 12:00:00+00:00');
```

### SELECT

**※セッションのタイムゾーンに関わらず同じ結果**

```sql
select * from dt_demo;
```

|id|label|date_time||
|---|---|---|---|
|1|session: Asia/Tokyo, TZ: undefined|2023-04-01 12:00:00||
|2|session: Asia/Tokyo, TZ: UTC+9|2023-04-01 12:00:00||
|3|session: Asia/Tokyo, TZ: UTC|2023-04-01 21:00:00|UTCの12時はAsia/Tokyoの21時|
|4|session: GMT, TZ: undefined|2023-04-01 12:00:00||
|5|session: GMT, TZ: UTC+9|2023-04-01 03:00:00|UTC+09:00の12時はGMTの3時|
|6|session: GMT, TZ: UTC|2023-04-01 12:00:00||

- 時差情報がない場合はその文字列がそのまま登録される(id: 1, 4)
- 時差情報がある場合はその時刻がセッションのタイムゾーンで何時であるかを計算した上で登録される(id: 3, 5)
    - ※id:2, 6では顕在化していないが同様の計算はされているはず
- 登録時に時差情報が失われるのでSELECT時はセッションのタイムゾーンに依存しない

## サーバのタイムゾーン=UTC
### INSERT
#### セッションのタイムゾーン=Asia/Tokyo
```sql
show variables like '%time_zone%';
```

|Variable_name|Value|
|---|---|
|system_time_zone|UTC|
|time_zone|Asia/Tokyo|

```sql
insert into dt_demo(label, date_time) values
('session: Asia/Tokyo, TZ: undefined', '2023-04-01 12:00:00'),
('session: Asia/Tokyo, TZ: UTC+9',     '2023-04-01 12:00:00+09:00'),
('session: Asia/Tokyo, TZ: UTC',       '2023-04-01 12:00:00+00:00');
```

#### セッションのタイムゾーン=GMT
```sql
show variables like '%time_zone%';
```
|Variable_name|Value|
|---|---|
|system_time_zone|UTC|
|time_zone|GMT|

```sql
insert into dt_demo(label, date_time) values
('session: GMT, TZ: undefined', '2023-04-01 12:00:00'),
('session: GMT, TZ: UTC+9',     '2023-04-01 12:00:00+09:00'),
('session: GMT, TZ: UTC',       '2023-04-01 12:00:00+00:00');
```

### SELECT

**※セッションのタイムゾーンに関わらず同じ結果**

```sql
select * from dt_demo;
```

|id|label|date_time||
|---|---|---|---|
|1|session: Asia/Tokyo, TZ: undefined|2023-04-01 12:00:00||
|2|session: Asia/Tokyo, TZ: UTC+9|2023-04-01 12:00:00||
|3|session: Asia/Tokyo, TZ: UTC|2023-04-01 21:00:00|UTCの12時はAsia/Tokyoの21時|
|4|session: GMT, TZ: undefined|2023-04-01 12:00:00||
|5|session: GMT, TZ: UTC+9|2023-04-01 03:00:00|UTC+09:00の12時はGMTの3時|
|6|session: GMT, TZ: UTC|2023-04-01 12:00:00||

- サーバのタイムゾーン=JSTの場合と同じ結果になる

# mysql2を利用（文字列として）

JS標準のDateクラスは曲者なのでひとまずdatetime型はstringとして取得する。
（`dateStrings: true`）

## サーバのタイムゾーン=JST
### INSERT

生SQLの結果からINSERT時のタイムゾーンが重要であることがわかったので環境変数`TZ`およびコネクション作成時のオプション`timezone`を変化させながら実行する

```ts
const conn = await createConnection({
  host: "127.0.0.1",
  user: "root",
  password: "***",
  database: "demo",
  timezone: timezone,
  dateStrings: true,
});
const data = [
  [
    `process: ${process.env.TZ}, connection: ${timezone}, TZ: undefined`,
    "2023-04-01 12:00:00",
  ],
  [
    `process: ${process.env.TZ}, connection: ${timezone}, TZ: UTC+9`,
    "2023-04-01 12:00:00+09:00",
  ],
  [
    `process: ${process.env.TZ}, connection: ${timezone}, TZ: UTC`,
    "2023-04-01 12:00:00+00:00",
  ],
];
data.forEach((row) => {
  conn.query("insert into dt_demo(label, date_time) values (?, ?)", row);
});
await conn.end();
```

|TZ|timezone|
|---|---|
|Asia/Tokyo|undefined|
|Asia/Tokyo|+09:00|
|UTC|undefined|
|UTC|+00:00|

### SELECT

生SQLの結果からSELECT時のタイムゾーンには依存しないので1パターンのみ実行する。

```ts
const conn = await createConnection({
  host: "127.0.0.1",
  user: "root",
  password: "***",
  database: "demo",
  dateStrings: true,
});

const [result] = await conn.query(
  "select * from dt_demo where label like 'process%'"
);
console.log(result);

await conn.end();
```

```ts
[
  {
    id: 7,
    label: 'process: Asia/Tokyo, connection: undefined, TZ: undefined',
    date_time: '2023-04-01 12:00:00'
  },
  {
    id: 8,
    label: 'process: Asia/Tokyo, connection: undefined, TZ: UTC+9',
    date_time: '2023-04-01 12:00:00'
  },
  {
    id: 9,
    label: 'process: Asia/Tokyo, connection: undefined, TZ: UTC',
    date_time: '2023-04-01 21:00:00'
  },
  {
    id: 10,
    label: 'process: Asia/Tokyo, connection: +09:00, TZ: undefined',
    date_time: '2023-04-01 12:00:00'
  },
  {
    id: 11,
    label: 'process: Asia/Tokyo, connection: +09:00, TZ: UTC+9',
    date_time: '2023-04-01 12:00:00'
  },
  {
    id: 12,
    label: 'process: Asia/Tokyo, connection: +09:00, TZ: UTC',
    date_time: '2023-04-01 21:00:00'
  },
  {
    id: 13,
    label: 'process: UTC, connection: undefined, TZ: undefined',
    date_time: '2023-04-01 12:00:00'
  },
  {
    id: 14,
    label: 'process: UTC, connection: undefined, TZ: UTC+9',
    date_time: '2023-04-01 12:00:00'
  },
  {
    id: 15,
    label: 'process: UTC, connection: undefined, TZ: UTC',
    date_time: '2023-04-01 21:00:00'
  },
  {
    id: 16,
    label: 'process: UTC, connection: +00:00, TZ: undefined',
    date_time: '2023-04-01 12:00:00'
  },
  {
    id: 17,
    label: 'process: UTC, connection: +00:00, TZ: UTC+9',
    date_time: '2023-04-01 12:00:00'
  },
  {
    id: 18,
    label: 'process: UTC, connection: +00:00, TZ: UTC',
    date_time: '2023-04-01 21:00:00'
  }
]
```

環境変数`TZ`・コネクション作成時のオプション`timezone`に依らず、生SQLでtime_zone=Asia/Tokyoの場合の結果と同じになった。

・・・え？

***mysql2のtimezone効いてなくね！？***（※実行環境：mysql2@3.3.1）

Issue探したらすでに報告されていた。（そして2023-05-21時点でOpenで議論もされていない…）

https://github.com/sidorares/node-mysql2/issues/1911

とりあえず本記事の動作確認・執筆時点では

- デフォルトでセッションのタイムゾーン=サーバのタイムゾーン
- `timezone`は設定しても効かない

# mysql2を利用（Dateインスタンスとして）

### 事前知識

MySQLのような時差情報のない日時文字列の場合、Date型は環境変数`TZ`によってタイムゾーンを変化させる

```ts
console.log(process.env.TZ);

const d = new Date("2023-04-01 12:00:00");
console.log(d.toLocaleString());
console.log(d.toISOString());
```

```zsh:実行結果（JSTの場合）
% pnpm run dev-jst
Asia/Tokyo
2023/4/1 12:00:00
2023-04-01T03:00:00.000Z
```

```zsh:実行結果（UTCの場合）
% pnpm run dev-utc
UTC
2023/4/1 12:00:00
2023-04-01T12:00:00.000Z
```

## INSERT

環境変数`TZ`を変化させてINSERTしてみる

```ts
const conn = await createConnection({
  host: "127.0.0.1",
  port: port,
  user: "root",
  password: "root",
  database: "demo",
});

await conn.query("insert into dt_demo(label, date_time) values (?, ?)", [
  `Date, process: ${process.env.TZ}`,
  new Date("2023-04-01 12:00:00"),
]);

await conn.end();
```

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

```sql
select * from dt_demo where label like 'Date, %'
```

|id|label|date_time|
|---|---|---|
|19|Date, process: Asia/Tokyo|2023-04-01 12:00:00|
|20|Date, process: UTC|2023-04-01 12:00:00|

- **MySQL側のタイムゾーンに関わらず**Node側の見た目の日時がそのまま登録される。
  - **MySQLとNodeでタイムゾーンが異なる場合、時刻がずれて登録される。**(id: 20)

## SELECT

```ts
console.log(process.env.TZ);
const conn = await createConnection({
  host: "127.0.0.1",
  port: port,
  user: "root",
  password: "root",
  database: "demo",
});

const [result] = await conn.query(
  "select * from dt_demo where label like 'Date, %'"
);
console.log(result);

await conn.end();
```

```zsh:実行結果（JSTの場合）
% pnpm run dev-utc
Asia/Tokyo
[
  {
    id: 19,
    label: 'Date, process: Asia/Tokyo',
    date_time: 2023-04-01T03:00:00.000Z
  },
  {
    id: 20,
    label: 'Date, process: UTC',
    date_time: 2023-04-01T03:00:00.000Z
  }
]
```

```zsh:実行結果（UTCの場合）
% pnpm run dev-utc
UTC
[
  {
    id: 19,
    label: 'Date, process: Asia/Tokyo',
    date_time: 2023-04-01T12:00:00.000Z
  },
  {
    id: 20,
    label: 'Date, process: UTC',
    date_time: 2023-04-01T12:00:00.000Z
  }
]
```

- 時差情報のない日時文字列を`Date`のコンストラクター引数に入れた形のオブジェクトになる。
    - **MySQLとNodeでタイムゾーンが異なる場合、時刻がずれて取得される。**
        - 登録時と取得時のNodeのタイムゾーンが同じであればズレは相殺される。