MySQL で UNIX タイム値を整形して表示する方法

Posted by
ぴろり
Posted at
2015/01/30 16:17
Trackbacks
関連記事 (0)
Post Comment
コメントできます
Category
開発メモ カテゴリ
カバーイメージ

 MySQL データベースにおいて、UNIX 時間 で記録された数値 1293766496 を、2010-12-31 12:34:56 のような年月日・時分秒の形式で表示するための覚書き。

この記事を Delicious に追加する   このエントリーをはてなブックマークに追加  

背景

 MySQL データベースに時間情報を記録する際、カラムのデータ形式として TIMESTAMP 型や DATETIME 型が用意されています。このカラムにデータを入出力するときには、YYYY-MM-DD hh:mm:ss の表現形式に変換して SQL を発行する必要がありますが、Perl スクリプトなど time で取得できる現在時刻は UNIX 時間で、これを先の形式に毎度々々変換してから操作するのが面倒だったりします。 …まぁ、ベストなのはそういうライブラリを一発噛まして使えば済む話なのかもしれませんが、「とりあえず動けばいいや」ということで、UNIX 時間を INT 型のカラムにそのまま放り込んでしまっているテーブルが手元にあるわけですcrying.gif
 この 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 関数を使う方法の一例としてとりあえず。

この記事を Delicious に追加する   このエントリーをはてなブックマークに追加  


この記事のアーカイブ

全ての記事 »
2015年
全てのカテゴリ »
電算室 » 開発メモ
全てのタグ »
, , , ,

関連記事/トラックバック

関連記事/トラックバックはまだありません

この記事にトラックバックを送るには?

コメントを投稿する

 
 (必須, 匿名可, 公開, トリップが使えます)
 (必須, 匿名可, 非公開, Gravatar に対応しています)
 (必須)
スパム コメント防止のため「投稿確認」欄に ランダムな数字 CAPTCHAについて を入力してから送信してください。お手数ですがご協力のほど宜しくお願いいたします。