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

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;

-- 例　２０１１年５月４日　午前１時２０分３０秒

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 (２０１１年５月４日　午前１時２０分３０秒)

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タイムが標準時で記録されていた場合

-- 日本標準時で出力したければ、９時間足す必要がある

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