MySQLで時刻を扱う基本、timestamp型の使い方を解説
  • facebookページ
  • twitterページ
  • 2020.01.30

    MySQLで時刻を扱う基本、timestamp型の使い方を解説

    プログラムで時刻を扱う場合、専用の関数やオブジェクトを使うのが基本です。データベースで時刻を扱う場合も同じです。今回は、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型を使ったテーブル登録例

    datetime型

    datetime型も日付と時刻の両方を含むデータ型ですが、表現できる範囲は「1000-01-01 00:00:00」から「9999-12-31 23:59:59」です。これは、datetime型が64ビットのデータを利用しているためで、32ビットのtimestampのような制限はありません。しかし、timestampなら4バイトで済むところ、8バイト使ってしまうので、管理するデータベースが大きくなってしまいます。

    datetime型を使ったテーブル登録例

    timestamp型の使い方

    MySQLのtimestamp型は、実際には32ビットの数字ですが、決まったフォーマットに従えば、日付や時刻を文字列でも登録できます。次から、timestamp型のデータの使い方を紹介します。

    timestamp型のデータの登録例

    日付を登録する場合は、日付と時間をテキスト形式で指定すれば、timestamp型で登録されます。この場合、日付は「YYYY-MM-DD」、時刻は「hh:mm:dd」の形式です。

    timestamp型のフォーマット

    YYYY-MM-DD hh:nn:dd

    timestamp型の例

    この例では、2020年4月1日の8時30分の時刻を登録する例です。

    今の時刻を登録する例

    ySQLで、今の時刻を取得する関数はnow()で、timestamp型のフォーマットで今の時刻を出力します。そして、この関数を利用することで、SQLを処理した時刻をテーブルに格納することが可能です。

    now()の使用例

    自動でtimestampを登録させる方法

    MySQLには、レコードを追加したり更新した際に、自動でtimestamp型の更新時刻を登録する機能を利用できます。

    defaultでcurrent_timestampを指定する

    レコードを追加したり更新した際に自動でtimestampを追加させるには、テーブル作成時にdefaultでcurrent_timestampを指定します。

    なお、defaultは、テーブルにデータを追加する際に、カラムに値が指定されていない場合に定数などを指定する機能で、演算式も記述できます。この場合、timestamp型のカラムの値が指定されていない場合に、今の時刻、current_timestampを登録するように設定します。なお、current_timestampは、先ほど紹介したnow()と同じ機能です。

    current_timestampをdefualtで指定した例

    この例では、レコードを新たに作成するか、chkの内容を更新した際に、自動的に更新したtimestampをuptimeに登録します。そして、次のようなSQLでデータを追加したり書き換えることで、uptimeに自動で更新した日時が記録されます。

    timestamp型は古い規格

    これまで解説したように、MySQLのデータベースで日時を扱うならtimestamp型が使えます。日時のフォーマットに従って登録すれば、自動的にtimestampの数字に変換してくれて、この数字を使うことで日付や時間の計算も可能です。

    しかし、これは古い規格に基づいており、2038年問題を抱えるなど、今後、使われなくなる可能性もあります。もし、新たにデータベースを作るのなら、制限の無いdatetime型を検討してください。



    優良フリーランス案件多数掲載中!
    フリーランスエンジニアの案件をお探しなら
    ポテパンフリーランス

    この記事をシェア

    • Facebookシェア
    • Twitterシェア
    • Hatenaシェア
    • Lineシェア
    pickup









    ABOUT US

    ポテパンはエンジニアと企業の最適なマッチングを追求する企業です。

    READ MORE