UNIX TIMEの日時表示

業務での話

DBに保存されている時刻がUNIX TIMEだったので明確な値が分からない。

ということで保存されているUNIX TIMEを日付表示したい場合の方法

■そもそもUNIX TIMEとは？

1970/1/1 00:00:00を起点とした秒数を示す時刻情報です。

表記は以下下線のようになります。（時刻表記が（）内の場合）

1584629734（ISO 8601形式：2020-03-19T14:55:34Z）

WIKI引用

UNIX時間（ユニックスじかん）またはUNIX時刻（ユニックスじこく、UNIX time（ユニックスタイム）、POSIX time（ポジックスタイム））とはコンピューターシステム上での時刻表現の一種。UNIXエポック、すなわち協定世界時 (UTC) での1970年1月1日午前0時0分0秒から形式的な経過秒数（すなわち、実質的な経過秒数から、その間に挿入された閏秒を引き、削除された閏秒を加えたもの）として表される。

■OracleSQL

<<抽出時>>

SELECT TO_DATE('1970-01-01', 'YYYY-MM-DD') + NUMTODSINTERVAL(UNIXTIME + 9*60*60, 'SECOND')

FROM foo;

<<抽出条件に指定>>

日付で抽出条件を設定する場合(例：2019年7月1日以降のSTを出力)

SELECT *

FROM foo

WHERE TO_CHAR(TO_DATE('1970-01-01', 'YYYY-MM-DD') + NUMTODSINTERVAL(UNIXTIME + 9*60*60, 'SECOND'), 'YYYY-MM-DD') > '2019-06-30';

別にTO_CHARにする必要はないと思いますがこれでも日時指定としては機能します。

■Excelの場合

日付書式を日時とする。

セルに以下の数式を設定します。

=(UNIXTIME + 324000) / 864000 + 25569 で出力できます。

※補足ですが、UNIX TIMEがミリセカンドレベルで保存されている場合があります。

その場合はUNIXTIMEを/1000とすれば上記式で正しく処理できます。