Prisma MySQL でUTC以外の任意のタイムゾーンを利用するのが難しい件

Prisma MySQL でUTC以外の任意のタイムゾーンを利用するのが難しい件

2022-08-0816 min read

目次

  1. 概要
  2. 検証方法
  3. 結果
  4. 結果-1
  5. 余談
  6. 参考にしたサイト

概要

タイトルにもあるように、 Prisma+MySQL の構成で任意のタイムゾーンを DB で利用するのが難しいです。 素直に UTC を使うべきという感想になりました。

検証してみたので、結果を記載したいと思います。

検証方法

  • アプリケーションの実行環境は JST・UTC
  • DB の実行環境は JST・UTC

上記の 2 つの組み合わせパターンで検証を行います。

また後述しますが、アプリケーション側で生成したシステム時間と DB で生成したシステム時間をそれぞれ登録します。

登録した値を取り出し、JST に変更することで合っているかどうかを確認します。

検証した環境

  • Prisma: 4.1.1

検証するコード

import { PrismaClient, Prisma } from "@prisma/client";

// process.env.TZ = "UTC";
process.env.TZ = "Asia/Tokyo";

const prisma = new PrismaClient();

async function main() {
  await prisma.user.deleteMany();
  await prisma.user.create({
    data: {
      userInputDate1: new Date(),
    },
  });
  const allUsers = await prisma.user.findMany();
  for (let i in allUsers) {
    const row = allUsers[i];
    let obj = {
      userInputDate1: row.userInputDate1?.toLocaleString(),
      createdAt1: row.createdAt1.toLocaleString(),
      createdAt2: row.createdAt2.toLocaleString(),
      createdAt3: row.createdAt3.toLocaleString(),
      createdAt4: row.createdAt4.toLocaleString(),
      createdAt5: row.createdAt5.toLocaleString(),
    };
    console.log(row);
    console.log(obj);
  }
}

main()
  .catch((e) => {
    throw e;
  })
  .finally(async () => {
    await prisma.$disconnect();
  });

スキーマは次のように設定しておきます。

model User {
  id Int @id @default(autoincrement())
  userInputDate1 DateTime?
  createdAt1 DateTime @default(now()) @db.Timestamp(0)
  createdAt2 DateTime @default(dbgenerated("NOW()")) @db.Timestamp(0)
  createdAt3 DateTime @default(now())
  createdAt4 DateTime @default(now()) @db.DateTime(0)
  createdAt5 DateTime @default(dbgenerated("NOW()")) @db.DateTime(0)
}

dbgeneratedは DB の組み込み関数を呼び出す機能です。 これを使って MySQL のNOW()を呼び出します。

生成された型は次のようになります。

+----------------+-------------+------+-----+----------------------+-------------------+
| Field          | Type        | Null | Key | Default              | Extra             |
+----------------+-------------+------+-----+----------------------+-------------------+
| id             | int         | NO   | PRI | NULL                 | auto_increment    |
| userInputDate1 | datetime(3) | YES  |     | NULL                 |                   |
| createdAt1     | timestamp   | NO   |     | CURRENT_TIMESTAMP    | DEFAULT_GENERATED |
| createdAt2     | timestamp   | NO   |     | CURRENT_TIMESTAMP    | DEFAULT_GENERATED |
| createdAt3     | datetime(3) | NO   |     | CURRENT_TIMESTAMP(3) | DEFAULT_GENERATED |
| createdAt4     | datetime    | NO   |     | CURRENT_TIMESTAMP    | DEFAULT_GENERATED |
| createdAt5     | datetime    | NO   |     | CURRENT_TIMESTAMP    | DEFAULT_GENERATED |
+----------------+-------------+------+-----+----------------------+-------------------+

結果

それぞれの比較結果を記載していきます。

アプリ=JST,DB=JST

アプリ・DB がそれぞれ JST の場合、次のようになりました。

"2022-08-07T06:18:51.774Z" // JST 2022/8/7 15:18
// DBに登録されていた値
{
  "userInputDate1": "2022-08-07T06:18:51.761Z",
  "createdAt1": "2022-08-07T06:18:52.000Z",
  "createdAt2": "2022-08-07T15:18:51.000Z",
  "createdAt3": "2022-08-07T06:18:51.763Z",
  "createdAt4": "2022-08-07T06:18:52.000Z",
  "createdAt5": "2022-08-07T15:18:51.000Z"
}
// JSTに変換した値
{
  "userInputDate1": "2022/8/7 15:18:51",
  "createdAt1": "2022/8/7 15:18:52",
  "createdAt2": "2022/8/8 0:18:51",
  "createdAt3": "2022/8/7 15:18:51",
  "createdAt4": "2022/8/7 15:18:52",
  "createdAt5": "2022/8/8 0:18:51"
}

createdAt2 および createdAt5 で JST 基準で+9 時間ずれが発生しています。

一方で MySQL を直接参照しにいくと、、、

mysql> show variables like '%time_zone%';
+------------------+------------+
| Variable_name    | Value      |
+------------------+------------+
| system_time_zone | JST        |
| time_zone        | Asia/Tokyo |
+------------------+------------+
2 rows in set (0.00 sec)

mysql> select * from User\G
*************************** 1. row ***************************
            id: 7
userInputDate1: 2022-08-07 06:18:51.761
    createdAt1: 2022-08-07 06:18:52
    createdAt2: 2022-08-07 15:18:51
    createdAt3: 2022-08-07 06:18:51.763
    createdAt4: 2022-08-07 06:18:52
    createdAt5: 2022-08-07 15:18:51
1 row in set (0.00 sec)

createdAt2 createdAt5 以外で JST 基準で-9 時間のズレが発生していました。

アプリ=UTC,DB=JST

アプリ・DB がそれぞれ UTC・JST の場合、次のようになりました。

"2022-08-07T06:34:40.209Z" // JST 2022/8/7 15:34
{
  "userInputDate1": "2022-08-07T06:34:40.193Z",
  "createdAt1": "2022-08-07T06:34:40.000Z",
  "createdAt2": "2022-08-07T15:34:40.000Z",
  "createdAt3": "2022-08-07T06:34:40.195Z",
  "createdAt4": "2022-08-07T06:34:40.000Z",
  "createdAt5": "2022-08-07T15:34:40.000Z"
}
{
  "userInputDate1": "2022/8/7 6:34:40",
  "createdAt1": "2022/8/7 6:34:40",
  "createdAt2": "2022/8/7 15:34:40",
  "createdAt3": "2022/8/7 6:34:40",
  "createdAt4": "2022/8/7 6:34:40",
  "createdAt5": "2022/8/7 15:34:40"
}

reatedAt2 createdAt5 について以外で UTC 基準で+9 時間のずれが発生していました。

Date#toLocaleStringはアプリケーションのタイムゾーンに合わせて計算するため、このケースの場合は UTC 時間が出力される。

mysql> show variables like '%time_zone%';
+------------------+------------+
| Variable_name    | Value      |
+------------------+------------+
| system_time_zone | JST        |
| time_zone        | Asia/Tokyo |
+------------------+------------+
2 rows in set (0.00 sec)

mysql> select * from User\G;
*************************** 1. row ***************************
            id: 8
userInputDate1: 2022-08-07 06:34:40.193
    createdAt1: 2022-08-07 06:34:40
    createdAt2: 2022-08-07 15:34:40
    createdAt3: 2022-08-07 06:34:40.195
    createdAt4: 2022-08-07 06:34:40
    createdAt5: 2022-08-07 15:34:40
1 row in set (0.00 sec)

DB も同様に、createdAt2 createdAt5 以外で JST 基準で-9 時間のずれが発生していました。

アプリ=JST,DB=UTC

アプリ・DB がそれぞれ JST・UTC の場合、次のようになりました。

"2022-08-07T06:49:20.806Z" // JST 2022/8/7 15:49
{
  "id": 2,
  "userInputDate1": "2022-08-07T06:49:20.795Z",
  "createdAt1": "2022-08-07T06:49:21.000Z",
  "createdAt2": "2022-08-07T06:49:20.000Z",
  "createdAt3": "2022-08-07T06:49:20.797Z",
  "createdAt4": "2022-08-07T06:49:21.000Z",
  "createdAt5": "2022-08-07T06:49:20.000Z"
}
{
  "userInputDate1": "2022/8/7 15:49:20",
  "createdAt1": "2022/8/7 15:49:21",
  "createdAt2": "2022/8/7 15:49:20",
  "createdAt3": "2022/8/7 15:49:20",
  "createdAt4": "2022/8/7 15:49:21",
  "createdAt5": "2022/8/7 15:49:20"
}

全ての値が JST 基準でズレが発生していませんでした。

ぱっと見問題なさそうに見えますが、DB では違った結果となりました。

mysql> show variables like '%time_zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | UTC    |
| time_zone        | SYSTEM |
+------------------+--------+
2 rows in set (0.01 sec)

mysql> select * from User\G
*************************** 1. row ***************************
            id: 2
userInputDate1: 2022-08-07 06:49:20.795
    createdAt1: 2022-08-07 06:49:21
    createdAt2: 2022-08-07 06:49:20
    createdAt3: 2022-08-07 06:49:20.797
    createdAt4: 2022-08-07 06:49:21
    createdAt5: 2022-08-07 06:49:20
1 row in set (0.01 sec)

mysql> set time_zone='Asia/Tokyo';
Query OK, 0 rows affected (0.00 sec)

mysql> select * from User\G
*************************** 1. row ***************************
            id: 2
userInputDate1: 2022-08-07 06:49:20.795
    createdAt1: 2022-08-07 15:49:21
    createdAt2: 2022-08-07 15:49:20
    createdAt3: 2022-08-07 06:49:20.797
    createdAt4: 2022-08-07 06:49:21
    createdAt5: 2022-08-07 06:49:20
1 row in set (0.01 sec)

mysql>

DB は、createdAt1, createdAt2 (Timestamp 型)のもの以外は、JST で表示した場合、 -9 時間のズレが発生しました。

アプリ=UTC,DB=UTC

アプリおよび DB が UTC のケースです。

"2022-08-07T07:00:05.604Z" // JST 2022/8/7 16:00
{
  "id": 3,
  "userInputDate1": "2022-08-07T07:00:05.592Z",
  "createdAt1": "2022-08-07T07:00:06.000Z",
  "createdAt2": "2022-08-07T07:00:05.000Z",
  "createdAt3": "2022-08-07T07:00:05.594Z",
  "createdAt4": "2022-08-07T07:00:06.000Z",
  "createdAt5": "2022-08-07T07:00:05.000Z"
}
{
  "userInputDate1": "2022/8/7 7:00:05",
  "createdAt1": "2022/8/7 7:00:06",
  "createdAt2": "2022/8/7 7:00:05",
  "createdAt3": "2022/8/7 7:00:05",
  "createdAt4": "2022/8/7 7:00:06",
  "createdAt5": "2022/8/7 7:00:05"
}

出力自体は UTC 基準では問題ないように見えます。

mysql> show variables like '%time_zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | UTC    |
| time_zone        | SYSTEM |
+------------------+--------+
2 rows in set (0.01 sec)

mysql> select * from User\G
*************************** 1. row ***************************
            id: 3
userInputDate1: 2022-08-07 07:00:05.592
    createdAt1: 2022-08-07 07:00:06
    createdAt2: 2022-08-07 07:00:05
    createdAt3: 2022-08-07 07:00:05.594
    createdAt4: 2022-08-07 07:00:06
    createdAt5: 2022-08-07 07:00:05
1 row in set (0.00 sec)

mysql> set time_zone='Asia/Tokyo';
Query OK, 0 rows affected (0.00 sec)

mysql> select * from User\G
*************************** 1. row ***************************
            id: 3
userInputDate1: 2022-08-07 07:00:05.592
    createdAt1: 2022-08-07 16:00:06
    createdAt2: 2022-08-07 16:00:05
    createdAt3: 2022-08-07 07:00:05.594
    createdAt4: 2022-08-07 07:00:06
    createdAt5: 2022-08-07 07:00:05
1 row in set (0.00 sec)

DB は、createdAt1, createdAt2 (Timestamp 型)のもの以外は、JST で表示した場合、 -9 時間のズレが発生しました。

結果

アプリケーション x DB = JSTxJST UTCxJST JSTxUTC UTCxUTC
1 Prisma 生成 Timestamp 型
2 DB 生成 Timestamp 型
3 Prisma 生成 DateTime(3)型
4 Prisma 生成 DateTime 型
5 DB 生成 DateTime 型

= DB とアプリケーションの両環境で JST と UTC の両パターンの表示で問題ないことを確認

この結果から JST が必要な場合でも

  • DB は UTC で構築する
  • 型に Timestamp 型を利用する
    • ただし、Timestamp型自体は2038年問題を抱えており、対策を別途検討しなければならない

という苦しい条件での構築になると思いました。

余談

余談ですが、Prisma 側でコネクション作成時に set time_zone='Asia/Tokyo';を試しましたが上手くいかなかったです。

バージョンアップでオプションが付いてくれると嬉しいですね。

参考にしたサイト

Recommends
Prisma MySQL でUTC以外の任意のタイムゾーンを利用するのが難しい件
2022-08-08
prisma
typescript
mysql
Prisma TypeScript MySQLなプロジェクトの構築
2022-08-08
prisma
typescript
mysql
Prisma TypeScript SQLiteなプロジェクトの構築
2022-08-06
prisma
typescript
sqlite
AWS CDK v2 でVPC上にAPI Gateway + Lambda + RDS +...
2022-02-28
amazon%20aws
aws%20cdk
node.js
[NestJS]少し大きな規模のRESTfull APIを構築するディレクトリ構成を考えて...
2022-09-04
nestjs
typescript
%E3%82%A2%E3%83%BC%E3%82%AD%E3%83%86%E3%82%AF%E3%83%81%E3%83%A3
Fisher-Yates shuffleで配列シャッフル [js/ts/php]
2022-06-19
javascript
node.js
typescript
[CDK]SNS+SQS+DynamoDBでBounceとComplaint情報を収集する...
2022-04-11
amazon%20aws
node.js
typescript
[AWS CDK] Lambda で S3 オブジェクトを読み書きするStackの構築
2022-03-18
aws%20cdk
amazon%20aws
typescript
[AWS CDK] Bastion(踏み台)構築。SSMとEC2InstanceConne...
2022-03-06
amazon%20aws
aws%20cdk
node.js
[AWS CDK] Cognito を構築
2022-03-04
amazon%20aws
aws%20cdk
node.js
javascriptで累積和を解く
2022-02-27
%E3%82%A2%E3%83%AB%E3%82%B4%E3%83%AA%E3%82%BA%E3%83%A0
%E7%AB%B6%E6%8A%80%E3%83%97%E3%83%AD%E3%82%B0%E3%83%A9%E3%83%A0%E3%83%9F%E3%83%B3%E3%82%B0
atcoder
NestJSアプリケーションをwebpackでBundle
2022-02-20
javascript
typescript
nestjs
DBクライアントツールはDBeaverをおすすめしたい
2021-03-08
oracle
mysql
sqlite
MySQL8.0 で利用できるパラメータを表示する方法
2021-01-27
mysql
mariadb
centos
CentOS に MySQL8.0をインストールする
2021-01-26
mysql
mariadb
centos
New Posts
[JS]Intl.DateTimeFormatで和暦と西暦を変換
2022-08-18
javascript
[NestJS]少し大きな規模のRESTfull APIを構築するディレクトリ構成を考えて...
2022-09-04
nestjs
typescript
%E3%82%A2%E3%83%BC%E3%82%AD%E3%83%86%E3%82%AF%E3%83%81%E3%83%A3
Prisma MySQL でUTC以外の任意のタイムゾーンを利用するのが難しい件
2022-08-08
prisma
typescript
mysql
Prisma TypeScript MySQLなプロジェクトの構築
2022-08-08
prisma
typescript
mysql
Prisma TypeScript SQLiteなプロジェクトの構築
2022-08-06
prisma
typescript
sqlite
[AWS]Lambda vs Fargate. APIを実装する場合に思うこと
2022-07-30
amazon%20aws
amazon%20ecs
%E9%9B%91%E8%AB%87
macOSにzigをインストールしてHello World!する
2022-07-18
zig
mac
[AWS CDK] Cognito の OIDC プロバイダに Auth0 を設定
2022-07-03
auth0
amazon%20aws
aws%20cdk
Amazon S3 でライフサイクルポリシーを設定する
2022-06-19
amazon%20aws
amazon%20s3
AWS Certified Developer Associate に合格した
2022-06-19
amazon%20aws
%E8%B3%87%E6%A0%BC%E8%A9%A6%E9%A8%93
Fisher-Yates shuffleで配列シャッフル [js/ts/php]
2022-06-19
javascript
node.js
typescript
JavaScriptでUTF-16コードを文字列に変換
2022-06-18
javascript
node.js
[JS]乱数でランダムな整数を生成する
2022-06-18
javascript
node.js
[JS]BigIntでMathが使えない件
2022-06-12
javascript
node.js
atcoder
AWS SAPに合格しました
2022-06-11
amazon%20aws
%E8%B3%87%E6%A0%BC%E8%A9%A6%E9%A8%93
Hot posts!
Proxy環境下でcurlを実行する
2019-12-07
linux
curl
OpenCVのMatのタイプ一覧表
2018-11-25
%E7%94%BB%E5%83%8F%E5%87%A6%E7%90%86
opencv
Macでも利用できるDBクライアント MySQL PostgreSQL Oracle など
2019-12-21
linux
%E3%83%87%E3%83%BC%E3%82%BF%E3%83%99%E3%83%BC%E3%82%B9
mysql
TablePlusを使ってみる。シンプルでモダンなSQLクライアントツール
2018-09-30
%E3%83%87%E3%83%BC%E3%82%BF%E3%83%99%E3%83%BC%E3%82%B9
DBクライアントツールはDBeaverをおすすめしたい
2021-03-08
oracle
mysql
sqlite
AWS S3のアクセスキーIDとシークレットアクセスキーの取得 作業用ユーザを作成
2019-06-12
amazon%20aws
linux
amazon%20s3
AtCoderで初めて色がつくまでの話(茶色) レートが中々上がらなかった原因
2018-11-25
%E3%82%A2%E3%83%AB%E3%82%B4%E3%83%AA%E3%82%BA%E3%83%A0
%E7%AB%B6%E6%8A%80%E3%83%97%E3%83%AD%E3%82%B0%E3%83%A9%E3%83%9F%E3%83%B3%E3%82%B0
%E9%9B%91%E8%AB%87
CentOS8でEPELとPowerToolsリポジトリの有効化
2020-11-30
centos
red%20hat
EPEL
Macでターミナルからポートスキャンを行う方法。
2018-12-09
linux
mac
apple
Python + OpenCVのfillConvexPolyで複雑なポリゴンを描画する
2018-11-27
python
%E7%94%BB%E5%83%8F%E5%87%A6%E7%90%86
opencv
Date
▶︎
2022 年 (39)
▶︎
2021 年 (40)
▶︎
2020 年 (30)
▶︎
2019 年 (90)
▶︎
2018 年 (89)
▶︎
2017 年 (1)
Tags
javascript(98)
amazon%20aws(47)
linux(47)
node.js(38)
%E3%82%A2%E3%83%AB%E3%82%B4%E3%83%AA%E3%82%BA%E3%83%A0(36)
%E7%94%BB%E5%83%8F%E5%87%A6%E7%90%86(30)
html5(29)
typescript(28)
php(24)
centos(24)
python(22)
%E7%AB%B6%E6%8A%80%E3%83%97%E3%83%AD%E3%82%B0%E3%83%A9%E3%83%9F%E3%83%B3%E3%82%B0(21)
mysql(19)
mac(19)
canvas(18)
opencv(17)
%E9%9B%91%E8%AB%87(16)
wordpress(15)
atcoder(14)
docker(14)
apache(12)
%E6%A9%9F%E6%A2%B0%E5%AD%A6%E7%BF%92(12)
%E3%83%87%E3%83%BC%E3%82%BF%E3%83%99%E3%83%BC%E3%82%B9(12)
amazon%20s3(12)
red%20hat(12)
ubuntu(11)
github(10)
git(10)
vue.js(10)
%E7%94%BB%E5%83%8F%E5%87%A6%E7%90%86100%E6%9C%AC%E3%83%8E%E3%83%83%E3%82%AF(10)
mariadb(10)
aws%20cdk(9)
css3(8)
%E5%8F%AF%E8%A6%96%E5%8C%96(8)
%E5%B0%8F%E3%83%8D%E3%82%BF(8)
amazon%20lightsail(7)
react(7)
%E3%83%96%E3%83%AD%E3%82%B0(6)
cms(6)
oracle(6)
perl(6)
gitlab(6)
next.js(6)
iam(5)
amazon%20ec2(5)
%E8%B3%87%E6%A0%BC%E8%A9%A6%E9%A8%93(5)
aws%20amplify(5)
curl(4)
webassembly(4)
ssh(4)
Author
s-yoshiki
s-yoshiki
githubzenntwitterqiita
ただの備忘録です。
JavaScript/TypeScript/node.js/React/AWS/OpenCV
※このブログの内容は個人の見解であり、所属する組織等の見解ではありません。