LoginSignup
1
2
@kurukuruz

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

Posted at

はじめに

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型カラムを持つテーブルを用意する。

create table dt_demo (
  id serial primary key,
  label varchar(255),
  date_time datetime
);

生SQL実行

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

INSERT

セッションのタイムゾーン=Asia/Tokyo

show variables like '%time_zone%';
Variable_name Value
system_time_zone JST
time_zone Asia/Tokyo 
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

show variables like '%time_zone%';
Variable_name Value
system_time_zone JST
time_zone GMT 
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

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

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

show variables like '%time_zone%';
Variable_name Value
system_time_zone UTC
time_zone Asia/Tokyo 
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

show variables like '%time_zone%';
Variable_name Value
system_time_zone UTC
time_zone GMT 
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

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

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を変化させながら実行する

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パターンのみ実行する。

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();

[
  {
    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で議論もされていない…）

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

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

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

事前知識

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

console.log(process.env.TZ);

const d = new Date("2023-04-01 12:00:00");
console.log(d.toLocaleString());
console.log(d.toISOString());
実行結果（JSTの場合）
% pnpm run dev-jst
Asia/Tokyo
2023/4/1 12:00:00
2023-04-01T03:00:00.000Z
実行結果（UTCの場合）
% pnpm run dev-utc
UTC
2023/4/1 12:00:00
2023-04-01T12:00:00.000Z

INSERT

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

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();

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

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

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();
実行結果（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
  }
]
実行結果（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のタイムゾーンが同じであればズレは相殺される。
1
2
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
1
2