Q&A
createdAtのデータ型は何になっていますか？
前提・実現したいこと
TypescriptからMySQL(5.7)に接続しています。
発生している問題
[概要]
INSERT, UPDATEする時は、MySQLのタイムゾーンに従っていますが、
SELECT (FETCH)してくる時は、タイムゾーンがずれます。
実際に起きていること
(1) 以下のようなSQL文でTypescriptからUPDATEします。
SQL
1UPDATE tests SET createdAt = (NOW()) WHERE (id = 1)
すると、MySQLには、タイムゾーンに設定された時刻で
2021-09-14 02:45:29 （GMT + 2:00)
と記録されています。
(2) 次に上記のデータをFETCHします。
SQL
1SELECT * FROM tests WHERE (id = 1)
とってきたデータをコンソール出力すると、
createdAt: '2021-09-14T02:45:29.000Z '
と表示されます。
末尾のZは、GMTだと理解していますが、、、
もともと GMT + 2:00 で記録していたと考えたデータが、
Fetchする時に GMTとなっております。。
処理すればするほど、プログラム内部で２時間ずつ時間がずれてしまうのですが、
どこのタイムゾーン設定が漏れているのかアドバイスをいただけますでしょうか？
よろしくお願いいたします。
コメントありがとうございます。
timestamp型になっております。
datetime型にしてみてはどうでしょうか？
MySQL :: MySQL 5.6 リファレンスマニュアル :: 11.3.1 DATE、DATETIME、および TIMESTAMP 型
https://dev.mysql.com/doc/refman/5.6/ja/datetime.html
「MySQL は、TIMESTAMP 値を、ストレージでは現在のタイムゾーンを UTC に変換し、取得では UTC から現在のタイムゾーンに戻します。(DATETIME などのほかの型ではこれは行われません。)デフォルトでは、接続ごとの現在のタイムゾーンはサーバーの時間です。タイムゾーンは接続ごとに設定できます。」
つまりTIMESTAMP型ならDB上にはUTCで保管されるので、いつでも必要なタイムゾーンでの表示が可能だと思います。
逆にタイムゾーンの分かっている文字列状態の日付時刻をTIMESTAMP型で正しく登録するには、接続ごとの現在のタイムゾーンを正しく設定しないといけないようです。
例えば、GMTの時刻2000-01-01T00:00:00+00:00を入れたい場合は
set @tmp_time_zone=@@session.time_zone;
set time_zone=GMT;
insert into xxxx values('2000-01-01 00:00:00');
set time_zone=@tmp_time_zone;
みたいな感じにする想定のようです(8系だと別の方法があるようですが)。
この方法では煩雑なので、恐らくクライアント側では何か想定するタイムゾーンがあるでしょうから、それに接続のタイムゾーンを合わせて、入力文字列側の時刻もそのタイムゾーンだけ使うようにするのが現実的に思えます。
なおdatetime型でdbに入れた場合は、@@session.time_zoneを書き換えても日付時刻が変わらなくなってしまいます。
例えばこういう感じです。
create table if not exists tests(id numeric, createdAt timestamp, createAtDatetime datetime);
insert into tests values(1, now(), now());
select * from tests;
set @tmp_time_zone=@@session.time_zone;
set time_zone=GMT;
select * from tests;
set time_zone=@tmp_time_zone;
TypeScript(JavaScript)でどのようにMySQLにアクセスしてるのか分かりませんが、nodejsでpromise-mysql(mysqljs/mysqlのPromiseラッパー)を使ってアクセスしてみました。
import * as mysql from 'promise-mysql';
(async ()=>{
const con = await mysql.createConnection({
host: 'localhost',
user: 'homestead',
password: 'secret',
database: 'homestead',
// timezone未設定だと@session.time_zoneも変わらず、クライアント側の表示ではUTCが使われる
// timezoneがutcだと入力文字列もutcを仮定された時刻として扱われ、クライアント側の表示もUTCが使われる
// timezoneがjstだと入力文字列もjstを仮定された時刻として扱われ、クライアント側の表示もJSTが使われる(タイムゾーン表記なし)
//timezone: 'utc',
//timezone: 'jst',
});
console.log(await con.query('CREATE TABLE IF NOT EXISTS tests_sample(id NUMERIC, createdAt TIMESTAMP, createAtDatetime DATETIME)'));
console.log(await con.query('TRUNCATE TABLE tests_sample'));
console.log(await con.beginTransaction());
console.log(await con.query(`INSERT INTO tests_sample VALUES(?, NOW(), NOW())`, [1]));
console.log(await con.query(`INSERT INTO tests_sample VALUES(?, ?, ?)`, [2, '2000-01-01 00:00:00', '2000-01-01 00:00:00']));
console.log(await con.commit());
console.log(await con.query('SELECT * FROM tests_sample'));
con.end();
})();
これを実行すると、最後のSELECT文の結果は以下のようになります。
[
RowDataPacket {
id: 1,
createdAt: 2021-09-15T09:12:08.000Z,
createAtDatetime: 2021-09-15T09:12:08.000Z
},
RowDataPacket {
id: 2,
createdAt: 1999-12-31T15:00:00.000Z,
createAtDatetime: 1999-12-31T15:00:00.000Z
}
]
このケースでは@session.time_zoneはJST相当になってるようですね。ただ表示としてはTZ付きで同時刻がUTCで表示されています。
次にコード上timezone: 'utc'行のコメントを外して実行すると
[
RowDataPacket {
id: 1,
createdAt: 2021-09-15T18:15:59.000Z,
createAtDatetime: 2021-09-15T18:15:59.000Z
},
RowDataPacket {
id: 2,
createdAt: 2000-01-01T00:00:00.000Z,
createAtDatetime: 2000-01-01T00:00:00.000Z
}
]
となります。未指定のときと違って、入力時間もUTCとして扱われています。
最後にコード上timezone: 'jst'行のコメントを外して実行すると
[
RowDataPacket {
id: 1,
createdAt: '2021-09-15 18:18:46',
createAtDatetime: '2021-09-15 18:18:46'
},
RowDataPacket {
id: 2,
createdAt: '2000-01-01 00:00:00',
createAtDatetime: '2000-01-01 00:00:00'
}
]
入力時間がJSTとして扱われ、表示もJSTになっています(TZなし)。
以上から、ご質問の件は、タイムゾーン指定が未指定なだけなのではないか？と思います。
daemo さん
補足ありがとうございます。
まず、daemoさんにベストアンサーを付与したいのですが「追記・修正の欄」にコメントされており、付与できない状態です。なにかしら「回答」にコメントいただけましたら、ベストアンサーにいたしますので、ご対応いただければと思います。
次に私が頂いた情報をもとに色々試行錯誤した結果ですが、
結果的に「ダメ」でうまくいきませんでした。
MySQLに対しては、mysql2を使ってアクセスしております。
daemoさんがpromise-mysqlで実施されている通り、
タイムゾーンを指定してmysqlにアクセスしたら一旦うまくいきました。
ただ、mysql2の仕様のようなのですが、
"jst"のような指定ができず、"+09:00" や "+02:00"のような指定しかできませんでした。
この仕様のせいで、、、サマータイムのハンドリングができなかったのです。
私の使いたいタイムゾーンは、夏は "+02:00"で、それ以外の時期は"+01:00"です。
そのため、+02:00を指定してMySQLにアクセスしても、夏以外の時間は１時間ずれます。逆もしかりでした。
夏時間であれば１時間補正するとか、、コード側で対処もできるかもしれませんが、バグの温床になりそうですし、断念しました。
また、promise-mysqlに変更するなども考えたのですが、
コードの変更箇所も少なくないですし、時間的なこと、精神的な負担も考慮して、諦めました。
結果的に、mysqlにタイムスタンプをDECIMAL型で保存する という結論に至りました。
（本質問からすると、敗北感はありますが、、、）
コメント長くなりましたが、アドバイスとても助かりました。
ありがとうございました。
mysql2だったのですね。また夏時間を扱うタイムゾーンの話だったのですね。
多分これらは質問に書かないと回答者に伝わりません。
素のmysql(コマンドライン)の場合は、例えば
set time_zone='Europe/Stockholm'
のような操作が有効で、夏にはCEST(+02:00)、それ以外ではEST(+01:00)が使われるように動くようです。
mysqljs/mysql系でもset time_zone='Europe/Stockholm'をquery関数に渡せば意図したように動くようです。ただ、接続パラメータとしてのtime_zoneで'Europe/Stockholm'のような指定をしても、捨てられてしまい、元の@@session.time_zoneでそのまま動いていました。つまり、mysqljs/mysqlで繋いで最初にset time_zone='Europe/Stockholm'すれば恐らく意図した動作をすると思います。
mysql2ではまず、接続時のtimezone設定をした場合、入力文字列はそのタイムゾーンとして扱われるものの結果は全てUTCで返ってくる感じでした。ただし、おっしゃられるとおり、指定可能なタイムゾーンは'+01:00'のような形だけで、'JST'や'Europe/Stockholm'は扱えませんでした。
確認に使ったスクリプトだけ載せておきます。
import * as mysql from 'mysql2/promise';
(async()=>{
//const mysql = require('mysql2/promise');
const con = await mysql.createConnection({
host: 'localhost',
user: 'homestead',
password: 'secret',
database: 'homestead',
//timezone: 'utc',
//timezone: 'jst',
//timezone: 'Europe/Stockholm',
timezone: '+09:00',
});
console.log(await con.query('CREATE TABLE IF NOT EXISTS tests_sample(id NUMERIC, createdAt TIMESTAMP, createAtDatetime DATETIME)'));
console.log(await con.query('TRUNCATE TABLE tests_sample'));
console.log(await con.beginTransaction());
console.log(await con.query(`INSERT INTO tests_sample VALUES(?, NOW(), NOW())`, [1]));
console.log(await con.query(`INSERT INTO tests_sample VALUES(?, ?, ?)`, [2, '2000-01-01 00:00:00', '2000-01-01 00:00:00']));
console.log(await con.commit());
console.log(await con.query('SELECT * FROM tests_sample'));
console.log(await con.query(`SET time_zone='Europe/Stockholm'`));
console.log(await con.query('SELECT * FROM tests_sample'));
console.log(await con.query(`INSERT INTO tests_sample VALUES(?, ?, ?)`, [3, '2000-01-01 00:00:00', '2000-01-01 00:00:00']));
console.log(await con.query('SELECT * FROM tests_sample'));
con.end();
})();
DECIMAL型で保存する結果になったのは残念ですが、まあ仕方ないかもですね。
回答については、ちゃんと解決していないこと、また質問自体が第三者には分からないもののままなこと、から回答しません。お時間のご都合もあるでしょうし、個人的にはこのままでいいと思います。
あなたの回答
tips
プレビュー
