
MySQLの時間型でミリ秒を扱うケースについて
-
2021年10月14日
こんにちは。cocone connect株式会社でインフラエンジニアを担当している S です。
cocone connect(ココネコネクト)は、『農園婚活』や『ポケットランド』といった、アバターを主軸にした、着せ替え、コミュニケーション、ゲーム、などの要素を
持ったコンテンツ・アプリを配信・運用している会社です。
今回は、MySQLの時間型でミリ秒を扱うケースについて話していこうと思います。
自分も少し曖昧に認識していた部分があり、再確認も兼ねて簡単な実験を行いました。
背景
PostgreSQLからMySQLへのデータ移行時に2021/09/16の範囲のデータについて抽出、移行したところ「2021-09-17 00:00:00」のデータが1件混ざる、ということが起こりました。
原因
原因を調べたところ、PostgreSQL(移行元)にある「2021-09-16 23:59:59.634」のデータが、MySQL(移行先)では「2021-09-17 00:00:00」に丸められて入ってしまったのが原因でした。
環境
移行元:AWS、PostgreSQL
OS | Amazon Linux AMI release 2017.09 |
カーネル Ver | Linux 4.9.76-3.78.amzn1.x86_64 #1 SMP Fri Jan 12 19:51:35 UTC 2018 |
DB | psql (PostgreSQL) 9.4.15 |
データ型 | timestamp without time zone |
移行先:オンプレサーバー、MySQL
OS | CentOS release 6.8 (Final) |
カーネル Ver | Linux 2.6.32-642.6.1.el6.x86_64 #1 SMP Wed Oct 5 00:36:12 UTC 2016 |
DB | mysql Ver 14.14 Distrib 5.6.34, for Linux (x86_64) using EditLine wrapper |
データ型 | timestamp |
実験
では実際にMySQL 5.6.34 で試してみましょう。
ステップ1:テーブル定義
- mysql> create table aa (
- fld_date date,
- fld_datetime datetime,
- fld_timestamp timestamp,
- fld_datetime2 datetime(2),
- fld_timestamp2 timestamp(2)
- );
ステップ2:current_date を入れてみる
- mysql> insert into aa values (
- current_date,
- current_date,
- current_date,
- current_date,
- current_date
- );
- mysql> select * from aa;
実行結果
fld_date | 2021-09-18 |
fld_datetime | 2021-09-18 00:00:00 |
fld_timestamp | 2021-09-18 00:00:00 |
fld_datetime2 | 2021-09-18 00:00:00 |
fld_timestamp2 | 2021-09-18 00:00:00 |
ステップ3:current_time を入れてみる
- mysql> insert into aa values (
- current_time,
- current_time,
- current_time,
- current_time,
- current_time
- );
- mysql> select * from aa;
実行結果
fld_date | 2021-09-18 |
fld_datetime | 2021-09-18 12:03:17 |
fld_timestamp | 2021-09-18 12:03:17 |
fld_datetime2 | 2021-09-18 12:03:17.00 |
fld_timestamp2 | 2021-09-18 12:03:17.00 |
ステップ4:current_timestamp を入れてみる
- mysql> insert into aa values (
- current_timestamp,
- current_timestamp,
- current_timestamp,
- current_timestamp,
- current_timestamp
- );
- mysql> select * from aa;
実行結果
fld_date | 2021-09-18 |
fld_datetime | 2021-09-18 12:06:13 |
fld_timestamp | 2021-09-18 12:06:13 |
fld_datetime2 | 2021-09-18 12:06:13.00 |
fld_timestamp2 | 2021-09-18 12:06:13.00 |
ステップ5:now() を入れてみる
- mysql> insert into aa values (
- now(),
- now(),
- now(),
- now(),
- now()
- );
- mysql> select * from aa;
実行結果
fld_date | 2021-09-18 |
fld_datetime | 2021-09-18 12:09:50 |
fld_timestamp | 2021-09-18 12:09:50 |
fld_datetime2 | 2021-09-18 12:09:50.00 |
fld_timestamp2 | 2021-09-18 12:09:50.00 |
ステップ6:now(2)、ミリ秒2桁 を入れてみる
- mysql> insert into aa values (
- now(2),
- now(2),
- now(2),
- now(2),
- now(2)
- );
- mysql> select * from aa;
実行結果
fld_date | 2021-09-18 |
fld_datetime | 2021-09-18 12:21:01 |
fld_timestamp | 2021-09-18 12:21:01 |
fld_datetime2 | 2021-09-18 12:21:01.05 |
fld_timestamp2 | 2021-09-18 12:21:01.05 |
ステップ7:ミリ秒=50 で入れてみる
- mysql> insert into aa values (
- ‘2021-09-18 23:59:59.50’,
- ‘2021-09-18 23:59:59.50’,
- ‘2021-09-18 23:59:59.50’,
- ‘2021-09-18 23:59:59.50’,
- ‘2021-09-18 23:59:59.50’
- );
- mysql> select * from aa;
実行結果
fld_date | 2021-09-19 |
fld_datetime | 2021-09-19 00:00:00 |
fld_timestamp | 2021-09-19 00:00:00 |
fld_datetime2 | 2021-09-18 23:59:59.50 |
fld_timestamp2 | 2021-09-18 23:59:59.50 |
ステップ8:ミリ秒=49 で入れてみる
- mysql> insert into aa values (
- ‘2021-09-18 23:59:59.49’,
- ‘2021-09-18 23:59:59.49’,
- ‘2021-09-18 23:59:59.49’,
- ‘2021-09-18 23:59:59.49’,
- ‘2021-09-18 23:59:59.49’
- );
- mysql> select * from aa;
実行結果
fld_date | 2021-09-18 |
fld_datetime | 2021-09-18 23:59:59 |
fld_timestamp | 2021-09-18 23:59:59 |
fld_datetime2 | 2021-09-18 23:59:59.49 |
fld_timestamp2 | 2021-09-18 23:59:59.49 |
補足
今回の記事では文字列を日付・時間型へ変換をするSQL関数は割愛しています。実際に行う際はご注意ください。
また、切り捨てや四捨五入のSQL関数を明示的につけるべきではありますが、今回の趣旨からは外れるため割愛しています。
まとめ
今回の実験を通して、ミリ秒の精度が付いている日付データを、ミリ秒の精度が付いていない時間型の項目に入れると、基本的に丸められて(四捨五入されて)しまうことを念頭に、時間型を定義する必要があることを再確認することができました。
(MySQLでは、バージョン 5.6.4 以降でマイクロ秒 (6 桁) までの精度を持てるようになっている。対象のデータ型:time、datetime、timestamp)
例えば、単純な履歴保持の場合はそのままでも良いと思いますが、集計処理などに使うデータなどの場合は集計の期日部分だけでも、事後に確認・調整を入れる、もしくはミリ秒部分は切り捨てて保持するべきです。
境界値の仕様については我々インフラエンジニアも十分に理解した上で扱うように気を付けましょう。
cocone connectでは一緒に働く仲間を募集中です。
ご興味のある方は、こちらのリンクからぜひご応募ください。
cocone connect株式会社 採用情報
https://recruit.jobcan.jp/coconeconnect