データを登録した日時や更新した日時など、データベースに現在の時刻を格納する処理、よく使われるSQLの使い方です。ここでは、標準的な現在の時刻を格納する方法と、MySQLで使える便利な書き方について紹介します。
現在の時刻を取得するには
SQLには、現在の時刻を取得し、それをそのままデータとして保存したり、文字列に変換して使用することが可能です。さらに、指定した時刻と比較することや、現在の時刻との差を抽出する条件に使うSQLもよく見かけます。
まずは、SQLで現在の時刻を取得する関数について解説します。
現在の時刻を取得する関数はCURRENT_TIMESTAMP
SQLが使えるデータベース管理ソフトで現在の時刻を取得するには、通常CURRENT_TIMESTAMP関数を使います。
CURRENT_TIMESTAMP関数の使用例 SELECT CURRENT_TIMESTAMP; 2001-12-23 14:39:53
なお、MySQLやPostgreSQLなどでは、CURRENT_TIMESTAMPの代わりに、多くのプログラム言語と同じようなNOW関数も使えます。
NOWの使用例 SELECT NOW; 2001-12-23 14:39:53
時刻を扱うなら型もチェック
データベース管理ソフトで日付と時刻を扱う場合、それらを格納する列のデータ型も、日時や時刻専用の方を用います。そして、現在の時刻を格納する場合、日付と時刻の両方を扱うTIMESTAMP型を使ってください。
ただし、データベース管理ソフトによっては、DATE型とTIMESTAMP型のほかにも、日付や時刻を扱う型があり、用途によって使い分けることが可能です。
例えば、Oralce Databaseでは、TIMESTAMPにタイムゾーンを設定した型が利用できます。
DATE
TIMESTAMP
TIMESTAMP WITH TIME ZONE
TIMESTAMP WITH LOCAL TIME ZONE
また、MySQLでは、これだけの型が使えます。
DATE
TIME
DATETIME
TIMESTAMP
YEAR
SQLから返される時刻
先ほど説明したCURRENT_TIMESTAMP関数を使って作られた日時をSELECT文で取り出すと、4桁の年、月、日を「-」でｌ区切り、時、分、秒を「:」で区切る文字列として取り出せます。
例えば、「2020-04-01 12:30:00」といった具合です。なお、この表示は、SQLで、TIMESTAMP型を文字列に変換する関数を指定することで、違うフォーマットで出力させることも可能です。
標準的な現在の時刻を扱うSQLの例
次に、ほとんどのデータベース管理ソフトで使える、標準的な現在の時刻を扱うSQLの例を紹介します。
TIMESTAMP型を含むテーブルを作る
現在の時刻を格納するには、TIMESTAMP型の列を作っておく必要があります。
現在の時刻を格納するテーブル作成用SQLの例 CREATE TABLE timestamp_tbl ( tid INT NOT null PRIMARY KEY, check INT, attime TIMESTAMP )
この例では、日時を格納できるattime列を作ります。
現在の時刻を格納する
先ほど作成したテーブルに、CURRENT_TIMESTAMP関数を使って現在の時刻を登録するSQLは次のとおりです。
現在の時刻を登録する例 INSERT INTO timestamp_tbl VALUES (1, 1, CURRENT_TIMESTAMP);
また、既にレコードが存在する場合、現在の時刻を上書きする場合は、次のSQLを使います。
現在の時刻を上書きする例 UPDATE timestamp_tbl SET attime = CURRENT_TIMESTAMP WHERE tid = 1;
時刻を取り出す例
先ほど登録した時刻を利用する場合、いろいろな使い方が可能ですが、最も簡単なSQLの例を次に紹介します。
時刻を取り出すSQLの例 SELECT attime FROM timestamp_tbl WHERE tid = 1;
このSQLを実行することで、下記のような日時を取り出せます。
SQLの実行結果
2020-04-01 12:30:00
MySQLにおける現在の時刻を扱う例
データベース管理ソフトによっては、独自の機能や関数が組み込まれており、それを使うことでプログラミング言語に近い書き方が可能です。
次から、独自機能が特に多い、MySQLで現在の時刻を扱うSQLの書き方を紹介します。
MySQLではDATETIME型を使う
先ほど、テーブルを作る際に、日時を登録するデータ型として、TIMESTAMP型を使いましたが、MySQLでは、DATETIME型も使えます。
MySQLのTIMESTAMP型は、2038年問題を抱えています。これは、MySQLのTIMESTAMP型は、UNIXと同じく32bitで時刻を管理しているためで、2038年1月19日を過ぎると、時刻を正しく扱えなくなります。
一方、DATETIME型は、’1000-01-01 00:00:00’から’9999-12-31 23:59:59’までをサポートしており、TIMESTAMP型のような問題は発生しません。そのため、MySQLやmariadbで新規に時刻を扱うテーブルを作るのなら、DATETIME型を利用してください。
MySQLはNOW関数を使う
先ほど、現在の時刻を得る関数として、CURRENT_TIMESTAMPを紹介しましたが、MySQLではNOW関数を使います。なお、MySQLでもCURRENT_TIMESTAMP関数は使えますが、これはNOW関数の別名に過ぎません。
MySQLの現在の時刻を登録する例 INSERT INTO timestamp_tbl VALUES (1, 1, NOW);
現在の時刻を自動的に登録する
今使われているデータベース管理ソフトでは、国際標準のSQLに準拠しつつ、独自の機能を実装しています。中には、別のソフトでも使えると便利だと思える、特別な機能も少なくありません。
そういった機能の中に、テーブルを作成する際、レコードが更新されたら、自動で現在の時刻を登録する機能をDEFAULTとして登録する機能があります。
MySQLのDEFAULT機能
オープンソースのデータベース管理ソフトとして利用者の多いMySQLは、国際標準のSQLに準拠しつつ、多くの独自の拡張機能を持つソフトです。
MySQLでは、DEFUALT句で、ON UDATEを使うことで、レコードが更新されたタイミングで、現在の時刻を自動で格納させられます。
自動で現在の時刻を格納するテーブルの例 CREATE TABLE test_ts ( id INTEGER PRIMARY KEY, name VARCHAR(100) NOT NULL, last_modified DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP );
上のテーブルにデータを格納する例 INSERT INTO test_ts (id, name) VALUES (1, 'TEST1');
上の例で、次のようにnameを書き換える場合、last_modifiedが自動で更新されます。
上のテーブルのデータの更新例 UPDATE test_ts SET name = "TEST2" WHERE id = 1;
まとめ
これまで紹介したように、標準的なSQLでは、UPDATE文でCURRENT_TIMESTAMP関数を使うことで、簡単に現在の時刻を格納できます。
さらに、MySQLではNOW関数が使え、ON UPDATEを利用して自動で現在の時刻を格納させることも可能です。
なお、他のデータデータベース管理ソフトでも、それぞれ特有の機能を組み合わせることで、MySQLのON UDATEと同じ機能を実現できます。興味のある方は、より詳しいSQLの使い方も学んでください。
