SQLメモ

 おそらく忘れるだろうSQLのtipsを書いておく。PostgreSQL 8.1でテストしている。ここにあるSQL文 が、ANSI SQLかどうかチェックをしているわけではないので、PostgreSQL独自のSQLも含まれているかもしれないので注意してほしい。

epochからの秒数を出力

 timestamp型で記録した時刻を、time(2)が返すepochからの秒数に直したい時。

extract(epoch from date_trunc('second', timestamp))
例)
chome=# SELECT now(),extract(epoch from date_trunc('second', now()));
             now              | date_part
------------------------------+------------
 2008-05-22 22:07:26.53403+09 | 1211461646
(1 row)

 ポイントは、timestamp型はマイクロ秒まで精度があるので、date_truncで秒以下を切り捨てること。あとはextractでepochからの秒数を計算すれば良い。

時刻をISO8601形式に変換

 ISO8601形式は、日付のフォーマットのひとつで詳しくはWikipediaのISO8601の項を参照のこと。

to_char(timestamp, 'YYYY-MM-DD"T"HH24:MI:SS');

chome=# SELECT now(),to_char(now(), 'YYYY-MM-DD"T"HH24:MI:SS');
              now              |       to_char
-------------------------------+---------------------
 2008-05-22 22:25:18.085479+09 | 2008-05-22T22:25:18
(1 row)

 ISO8601は、日付と時刻をアルファベットのTで区切るが、スペースで区切るのが一般的だろう。スペースで区切る場合はもちろん、"T"のところをスペースに変えれば良い。

gnuplotでグラフを書きやすそうな書式で出力する

 DBに記録した時系列データをgnuplotでプロットしたいことは、ままある。よってgnuplotでグラフを書きやすいフォーマットでDBがデータを出力してくれれば、非常に楽だ。例えばdb1というデータベースにtbl1というテーブルがあり、timestamp型のtimeというフィールドとdoubleのvalueというフィールドがあるとする。

psql -c "SELECT to_char(time, 'HH24:MI:SS'),value from tbl1 order by time;" -P format=u -P footer -F " " db1

 この出力を-oオプションかリダイレクトでファイルに書き、gnuplot上で時刻のフォーマットを指定してやればよいだろう。

set xdata time
set timefmt '%H:%M:%S'