PostgreSQLでUNIXTIMEを使うときのTIPS

PostgreSQLで時刻を扱うとき、PHPのdate()で出力しやすいようにUNIXTIME（int型）で保存しています。SQL内での扱いが逆に面倒になるのですが、UNIXTIMEからtimestamp型に戻すTIPS。

create table test (
 accesstime integer not null
);
select to_char('1970-01-01'::date + (accesstime || 's')::interval, 'YYYY-MM') from test;

直接intからintervalに変更できないので（ERROR: cannot cast type integer to intervalが出る）secondsのsを文字列としてつなげてやるとうまくCASTできます。to_charはmktimeみたいな具合に書けます。

月の一覧を出すときはPHPで書くよりもSQLでgroup byした方が速いのでこんな感じ。

select to_char('1970-01-01'::date + (accesstime || 's')::interval, 'YYYY-MM') as m from test group by m order by m;

逆の操作は以下に載っています。
Converting a date to unix timestamp (epoch seconds)

select extract('epoch' from now()) as unixtime;

ただこれだと少なくともPostgreSQL8.1.4では小数点以下までついて面倒なので自分は関数を定義してしまっています。

unixtime.c:

#include "/usr/include/pgsql/server/postgres.h"
#include "/usr/include/pgsql/server/fmgr.h"
#include <time.h>

PG_FUNCTION_INFO_V1(unixtime);

Datum
unixtime(PG_FUNCTION_ARGS)
{
   PG_RETURN_INT32(time(NULL));
}

コンパイル:

cc -I/usr/include/pgsql/server -fpic -c unixtime.c
cc -shared -o unixtime.so unixtime.o

関数の定義:

CREATE FUNCTION unixtime() RETURNS INTEGER
   AS '/path/to/unixtime.so', 'unixtime' LANGUAGE C STRICT;

実行:

select unixtime();