MySQLの時間型でミリ秒を扱うケースについて

こんにちは。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:テーブル定義

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

 

ステップ2:current_date を入れてみる

  1. mysql> insert into aa values (
  2.       current_date,
  3.       current_date,
  4.       current_date,
  5.       current_date,
  6.       current_date
  7. );
  8. 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 を入れてみる

  1. mysql> insert into aa values (
  2.       current_time,
  3.       current_time,
  4.       current_time,
  5.       current_time,
  6.       current_time
  7. );
  8. 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 を入れてみる

  1. mysql> insert into aa values (
  2.       current_timestamp,
  3.       current_timestamp,
  4.       current_timestamp,
  5.       current_timestamp,
  6.       current_timestamp
  7. );
  8. 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() を入れてみる

  1. mysql> insert into aa values (
  2.       now(),
  3.       now(),
  4.       now(),
  5.       now(),
  6.       now()
  7. );
  8. 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桁 を入れてみる

  1. mysql> insert into aa values (
  2.       now(2),
  3.       now(2),
  4.       now(2),
  5.       now(2),
  6.       now(2)
  7. );
  8. 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 で入れてみる

  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. );
  8. 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 で入れてみる

  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. );
  8. 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

 

Category

Tag

%d人のブロガーが「いいね」をつけました。