Oracle SQLでDATE型とUNIXタイムを相互に変換する

| 2011年5月4日水曜日
SQLだけでやると一文が長くなってしまうので、頻繁に利用するなら
PL/SQLで関数を作っておいた方がいいと思う。

自分の場合は一回限りなのでSQLのみでやりたかった。

※Oracle XEで検証

-- DATE型からUNIXタイムに
select trunc(to_number(
to_date('調べたい日時を入れる', 'YYYY/MM/DD HH24:MI:SS')
- to_date('1970/01/01 00:00:00', 'YYYY/MM/DD HH24:MI:SS')
) * (24 * 60 * 60)
) as unixtimestmp
from dual;

-- 例 2011年5月4日 午前1時20分30秒
select trunc(to_number(
to_date('2011/05/04 01:20:30', 'YYYY/MM/DD HH24:MI:SS')
- to_date('1970/01/01 00:00:00', 'YYYY/MM/DD HH24:MI:SS')
) * (24 * 60 * 60)
) as unixtimestmp
from dual;

結果: 1304472030

-- UNIXタイムからDATE型に
-- ここではわかりやすいようにto_charで変換して出力している
select to_char((
to_date('1970/01/01 00:00:00', 'YYYY/MM/DD HH24:MI:SS')
+ (調べたいUNIXタイムを入れる/ (24 * 60 * 60))
), 'YYYY/MM/DD HH24:MI:SS') as utc
from dual;

-- 例 1304472030 (2011年5月4日 午前1時20分30秒)
select to_char((
to_date('1970/01/01 00:00:00', 'YYYY/MM/DD HH24:MI:SS')
+ (1304472030 / (24 * 60 * 60))
), 'YYYY/MM/DD HH24:MI:SS') as utc
from dual;

結果: 2011/05/04 01:20:30

-- 調べたいUNIXタイムが標準時で記録されていた場合
-- 日本標準時で出力したければ、9時間足す必要がある
select to_char((
to_date('1970/01/01 00:00:00', 'YYYY/MM/DD HH24:MI:SS')
+ ((1304472030 + (9 * 60 * 60))/ (24 * 60 * 60))
), 'YYYY/MM/DD HH24:MI:SS') as jst
from dual;

結果: 2011/05/04 10:20:30