バナー画像

プログラムで時刻を扱う場合、専用の関数やオブジェクトを使うのが基本です。データベースで時刻を扱う場合も同じです。今回は、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型を検討してください。

エンジニアになりたい人に選ばれるプログラミングスクール「ポテパンキャンプ 」

ポテパンキャンプは卒業生の多くがWebエンジニアとして活躍している実践型プログラミングスクールです。 1000名以上が受講しており、その多くが上場企業、ベンチャー企業のWebエンジニアとして活躍しています。

基礎的な学習だけで満足せず、実際にプログラミングを覚えて実践で使えるレベルまで学習したいという方に人気です。 プログラミングを学習し実践で使うには様々な要素が必要です。

それがマルっと詰まっているポテパンキャンプでプログラミングを学習してみませんか?

卒業生の多くがWebエンジニアとして活躍

卒業生の多くがWeb企業で活躍しております。
実践的なカリキュラムをこなしているからこそ現場でも戦力となっております。
活躍する卒業生のインタビューもございますので是非御覧ください。

経験豊富なエンジニア陣が直接指導

実践的なカリキュラムと経験豊富なエンジニアが直接指導にあたります。
有名企業のエンジニアも多数在籍し品質高いWebアプリケーションを作れるようサポートします。

満足度高くコスパの高いプログラミングスクール「ポテパンキャンプ」

運営する株式会社ポテパンは10,000人以上のエンジニアのキャリアサポートを行ってきております。
そのノウハウを活かして実践的なカリキュラムを随時アップデートしております。

代表の宮崎もプログラミングを覚えサイトを作りポテパンを創業しました。
本気でプログラミングを身につけたいという方にコスパ良く受講していただきたいと思っておりますので、気になる方はぜひスクール詳細をのぞいてくださいませ。