プログラムで時刻を扱う場合、専用の関数やオブジェクトを使うのが基本です。データベースで時刻を扱う場合も同じです。今回は、MySQLで時刻を扱うためのデータ型、timestamp型の使い方について解説します。
時刻を登録する方法
Webシステムなどでデータベースを利用する場合、Web画面から入力された時点の日時をデータベースに登録するケースがあります。この場合、プログラム側でタイムスタンプのテキストを作り、これを登録するのが一般的です。しかし、データベース管理ソフトには、タイムスタンプを登録する機能があるので、これを利用しましょう。まずは、MySQLのtimestampの基本について解説します。
時刻を格納するデータタイプ
SQLが使えるデータベース管理ソフトでは、日付と時刻を登録するデータ型が使えます。これはMySQLも例外ではありません。そして、MySQLには、日時を登録するデータ型として、timestamp型とdatetime型の2つが利用できます。
なお、ITの基礎を学んだ方なら、コンピュータが時計を内蔵しており、その時計が出力する数字を使って、時刻を表示したり、ファイルを作成した日付を作っていることをご存じでしょう。SQLのデータベースでtimestamp型でテーブルを作成すると、その時計が出力する数字を格納します。そして、表示する場合は、その数字を日付や時刻に変換します。
timestampの実態は整数
先ほど解説したように、コンピュータの内蔵時計が出力するのは数字を使い、それをtimestanpとして登録することでデータベースにその時点の日付と時刻を記録できます。
このようにtimestampが実は数字というのはデータベースばかりではありません。例えばExcelでも日付や時刻が扱えますが、表示形式を日付や時刻にしているだけで、表示形式を変えれば数字として表示されます。そして、数字なので日数や時間を足したり引いたりも可能です。
さらに、テキストで書かれた日付のフォーマットをtimestampの数字に変換する関数もあるので、そういった関数を活用すれば、日付の文字列同志で日数や時間を計算もできます。
timestampの2038年問題
かつてIT業界では、西暦2000年の1月1日にコンピューターが暴走する、という問題を抱えていました。これは、当時のコンピュータのリソース不足のため西暦を表す数字を2桁だけで管理していたため、1999年の次は1900年になってしまうのが原因でした。
これと同じような問題が、MySQLのtimestampにもあります。timestampは2038年に最大値に達し、時計が1970年に戻ってしまうことでシステムが誤動作する、というものです。これは、MySQLのtimestampは、UNIXのtimestampと同じ仕組みを採用しており、1970年1月1日を0として、32ビットの整数で計算しているため、2038年になると32ビットを使いきってしまう、というものです。
しかし、MySQLのdatetime型は、32bitの整数ではありません。そのため、ネット上には、新しく作るシステムならtimestampではなく、datetimel型の使用を推奨している方もいます。
timestamp型とdatetime型
MySQLで日付と時刻を保存するテーブルを作る場合、timestamp型とdatetime型の2つの型が使えます。そこで次からは、この2つのデータ型の違いについて解説します。
timestamp型
timestamp型は日付と時刻の両方を含むデータ型であり、表現できる範囲は「1970-01-01 00:00:01」から「2038-01-19 03:14:07」までです。これは、先ほど解説したtimestampの2038年問題と同じ32ビットの数字による制限を抱えています。
timestamp型を使ったテーブル登録例
create table ts ( id int not null auto_increment primary key, col timestamp not null ) auto_increment = 1;
datetime型
datetime型も日付と時刻の両方を含むデータ型ですが、表現できる範囲は「1000-01-01 00:00:00」から「9999-12-31 23:59:59」です。これは、datetime型が64ビットのデータを利用しているためで、32ビットのtimestampのような制限はありません。しかし、timestampなら4バイトで済むところ、8バイト使ってしまうので、管理するデータベースが大きくなってしまいます。
datetime型を使ったテーブル登録例
create table dt ( id int not null auto_increment primary key, col datetime not null ) auto_increment = 1;
timestamp型の使い方
MySQLのtimestamp型は、実際には32ビットの数字ですが、決まったフォーマットに従えば、日付や時刻を文字列でも登録できます。次から、timestamp型のデータの使い方を紹介します。
timestamp型のデータの登録例
日付を登録する場合は、日付と時間をテキスト形式で指定すれば、timestamp型で登録されます。この場合、日付は「YYYY-MM-DD」、時刻は「hh:mm:dd」の形式です。
timestamp型のフォーマット
YYYY-MM-DD hh:nn:dd
timestamp型の例
insert into ts (col) value ('2020-04-01 08:30:00');
この例では、2020年4月1日の8時30分の時刻を登録する例です。
今の時刻を登録する例
ySQLで、今の時刻を取得する関数はnow()で、timestamp型のフォーマットで今の時刻を出力します。そして、この関数を利用することで、SQLを処理した時刻をテーブルに格納することが可能です。
now()の使用例
insert into ts (col) value (now());
自動でtimestampを登録させる方法
MySQLには、レコードを追加したり更新した際に、自動でtimestamp型の更新時刻を登録する機能を利用できます。
defaultでcurrent_timestampを指定する
レコードを追加したり更新した際に自動でtimestampを追加させるには、テーブル作成時にdefaultでcurrent_timestampを指定します。
なお、defaultは、テーブルにデータを追加する際に、カラムに値が指定されていない場合に定数などを指定する機能で、演算式も記述できます。この場合、timestamp型のカラムの値が指定されていない場合に、今の時刻、current_timestampを登録するように設定します。なお、current_timestampは、先ほど紹介したnow()と同じ機能です。
current_timestampをdefualtで指定した例
create table ts_auto ( id int not null auto_increment primary key, chk text, uptime timestamp not null default current_timestamp on update current_timestamp );
この例では、レコードを新たに作成するか、chkの内容を更新した際に、自動的に更新したtimestampをuptimeに登録します。そして、次のようなSQLでデータを追加したり書き換えることで、uptimeに自動で更新した日時が記録されます。
insert into ts_auto (chk) value ('checked');
update ts_auto set chk='re-checked';
timestamp型は古い規格
これまで解説したように、MySQLのデータベースで日時を扱うならtimestamp型が使えます。日時のフォーマットに従って登録すれば、自動的にtimestampの数字に変換してくれて、この数字を使うことで日付や時間の計算も可能です。
しかし、これは古い規格に基づいており、2038年問題を抱えるなど、今後、使われなくなる可能性もあります。もし、新たにデータベースを作るのなら、制限の無いdatetime型を検討してください。
