cocone engineering

こんにちは。cocone connect株式会社でインフラエンジニアを担当している S です。

cocone connect（ココネコネクト）は、『農園婚活』や『ポケットランド』といった、アバターを主軸にした、着せ替え、コミュニケーション、ゲーム、などの要素を

持ったコンテンツ・アプリを配信・運用している会社です。

 

今回は、MySQLの時間型でミリ秒を扱うケースについて話していこうと思います。

自分も少し曖昧に認識していた部分があり、再確認も兼ねて簡単な実験を行いました。

 

背景

PostgreSQLからMySQLへのデータ移行時に2021/09/16の範囲のデータについて抽出、移行したところ「2021-09-17 00:00:00」のデータが１件混ざる、ということが起こりました。

 

原因

原因を調べたところ、PostgreSQL（移行元）にある「2021-09-16 23:59:59.634」のデータが、MySQL（移行先）では「2021-09-17 00:00:00」に丸められて入ってしまったのが原因でした。

 

環境

移行元：AWS、PostgreSQL

 

 

移行先：オンプレサーバー、MySQL

 

実験

では実際にMySQL 5.6.34 で試してみましょう。

 

ステップ１：テーブル定義

1. mysql> create table aa (
2.       fld_date date,
3.       fld_datetime datetime,
4.       fld_timestamp timestamp,
5.       fld_datetime2 datetime(2),
6.       fld_timestamp2 timestamp(2)
7. );

 

ステップ２：current_date を入れてみる

1. mysql> insert into aa values (
2.       current_date,
3.       current_date,
4.       current_date,
5.       current_date,
6.       current_date
7. );
9. mysql> select * from aa;

 

実行結果

 

ステップ３：current_time を入れてみる

1. mysql> insert into aa values (
2.       current_time,
3.       current_time,
4.       current_time,
5.       current_time,
6.       current_time
7. );
9. mysql> select * from aa;

 

実行結果

 

ステップ４：current_timestamp を入れてみる

1. mysql> insert into aa values (
2.       current_timestamp,
3.       current_timestamp,
4.       current_timestamp,
5.       current_timestamp,
6.       current_timestamp
7. );
9. mysql> select * from aa;

 

実行結果

 

ステップ５：now() を入れてみる

1. mysql> insert into aa values (
2.       now(),
3.       now(),
4.       now(),
5.       now(),
6.       now()
7. );
9. mysql> select * from aa;

 

実行結果

 

ステップ６：now(2)、ミリ秒2桁 を入れてみる

1. mysql> insert into aa values (
2.       now(2),
3.       now(2),
4.       now(2),
5.       now(2),
6.       now(2)
7. );
9. mysql> select * from aa;

 

実行結果

 

ステップ７：ミリ秒=50 で入れてみる

1. mysql> insert into aa values (
2.       ‘2021-09-18 23:59:59.50’,
3.       ‘2021-09-18 23:59:59.50’,
4.       ‘2021-09-18 23:59:59.50’,
5.       ‘2021-09-18 23:59:59.50’,
6.       ‘2021-09-18 23:59:59.50’
7. );
9. mysql> select * from aa;

 

実行結果

 

ステップ８：ミリ秒=49 で入れてみる

1. mysql> insert into aa values (
2.       ‘2021-09-18 23:59:59.49’,
3.       ‘2021-09-18 23:59:59.49’,
4.       ‘2021-09-18 23:59:59.49’,
5.       ‘2021-09-18 23:59:59.49’,
6.       ‘2021-09-18 23:59:59.49’
7. );
9. mysql> select * from aa;

 

実行結果

 

補足

今回の記事では文字列を日付・時間型へ変換をするSQL関数は割愛しています。実際に行う際はご注意ください。

また、切り捨てや四捨五入のSQL関数を明示的につけるべきではありますが、今回の趣旨からは外れるため割愛しています。

 

まとめ

今回の実験を通して、ミリ秒の精度が付いている日付データを、ミリ秒の精度が付いていない時間型の項目に入れると、基本的に丸められて（四捨五入されて)しまうことを念頭に、時間型を定義する必要があることを再確認することができました。

(MySQLでは、バージョン 5.6.4 以降でマイクロ秒 (6 桁) までの精度を持てるようになっている。対象のデータ型：time、datetime、timestamp)

例えば、単純な履歴保持の場合はそのままでも良いと思いますが、集計処理などに使うデータなどの場合は集計の期日部分だけでも、事後に確認・調整を入れる、もしくはミリ秒部分は切り捨てて保持するべきです。

 

境界値の仕様については我々インフラエンジニアも十分に理解した上で扱うように気を付けましょう。

 

---

 

cocone connectでは一緒に働く仲間を募集中です。

ご興味のある方は、こちらのリンクからぜひご応募ください。

 

cocone connect株式会社 採用情報

https://recruit.jobcan.jp/coconeconnect