MySQL データベースにおいて、UNIX 時間 で記録された数値 1293766496 を、2010-12-31 12:34:56 のような年月日・時分秒の形式で表示するための覚書き。
MySQL データベースに時間情報を記録する際、カラムのデータ形式として TIMESTAMP
型や DATETIME
型が用意されています。このカラムにデータを入出力するときには、YYYY-MM-DD hh:mm:ss
の表現形式に変換して SQL を発行する必要がありますが、Perl スクリプトなど time
で取得できる現在時刻は UNIX 時間で、これを先の形式に毎度々々変換してから操作するのが面倒だったりします。 …まぁ、ベストなのはそういうライブラリを一発噛まして使えば済む話なのかもしれませんが、「とりあえず動けばいいや」ということで、UNIX 時間を INT
型のカラムにそのまま放り込んでしまっているテーブルが手元にあるわけです
この UNIX 時間で表示された、例えば 1422601708 というのは何年何月何日なのよ? を、phpMyAdmin コンソールなどから確認するための方法です。
SELECT `time`, FROM_UNIXTIME(`time`) FROM `my_table` time FROM_UNIXTIME(time) -----------+--------------------- 1420062473 2015-01-01 06:47:53 1420062540 2015-01-01 06:49:00 1420062547 2015-01-01 06:49:07
FROM_UNIXTIME
という組み込み関数があるので、それを使えば OK。反対に UNIX 時間に変換する UNIX_TIMESTAMP
という組み込み関数もあるんですね。へぇ。
SELECT UNIX_TIMESTAMP( '2010-12-31 12:34:56' ) 1293766496
上記の方法を用いて、発行する SQL を修正、既存の Perl スクリプトなどのコード変更は最小限に DATETIME
型に対応できそうです。データベースのカラムを INT
型から DATETIME
型に変更して、phpMyAdmin などのコンソールで確認した際に日付情報を読みやすくしつつ、プログラムからは扱いやすい UNIX 時間のままで入出力を行えます。
my $sth = $dbh->prepare( <<SQLHEREDOC ); -- 変更前 INSERT INTO `table` ( `id`, `time` ) VALUES ( ?, ? ) -- 変更後 INSERT INTO `table` ( `id`, `time` ) VALUES ( ?, FROM_UNIXTIME( ? )) SQLHEREDOC $sth->bind_param( 1, $id ); $sth->bind_param( 2, time ); # ここはそのままでOK
my $sth = $dbh->prepare( <<SQLHEREDOC ); -- 変更前 UPDATE `table` SET `time` = ? WHERE `id` = ? -- 変更後 UPDATE `table` SET `time` = FROM_UNIXTIME( ? ) WHERE `id` = ? SQLHEREDOC $sth->bind_param( 1, time ); # ここはそのままでOK $sth->bind_param( 2, $id );
my $sth = $dbh->prepare( <<SQLHEREDOC ); -- 変更前 SELECT `time` FROM `table` WHERE `id` = ? -- 変更後 SELECT UNIX_TIMESTAMP( `time` ) FROM `table` WHERE `id` = ? $sth->bind_param( 1, $id );
もっと良い方法がありそうですが、FROM_UNIXTIME
関数と UNIX_TIMESTAMP
関数を使う方法の一例としてとりあえず。