はじめに
オープンソースの検索プラットフォームであるSolrを使用しているシステムにおいて、
Javaヒープメモリの使用状況を確認しようとログファイルを取得したところ、
タイムスタンプの部分が馴染みのある形でない数字10桁であった。
こんな感じ
このままでは、Excelでのグラフ化の際に困ってしまうため、
エクセルで「年/月/日 時:分:秒」に変換する方法を調査した。
※補足として、別のソフトウェアなどでは10桁ではなく13桁の場合もあるらしいです。
その場合は予め1000で割っておけば、後述の方法での計算が可能となります。
わかったこと
少し調べてみると、数字10桁の値は【UNIX時間】と呼ばれるもので、
「1970年1月1日 0時0分0秒からの経過秒数で表した時間表記」のことであることがわかった。
さらに、普段Excelで2023/03/22 11:43:31(年/月/日 時:分:秒)というような形式で表示できているのは、
マイクロソフト独自の日時表現方式である【シリアル値】を、セルの書式設定で設定して表示できているためである。
上記からSolrのメトリックログのタイムスタンプをExcelで扱えるようにするためには、
UNIX時間をシリアル値へ変換する必要がある。
ここでいうシリアル値とは「1900年1月1日」を「1」とし、1日として「1」を加算していき日付/時刻を表現する値である。
例：「1900年1月2日」は「2」
なお、時分秒については小数を使って表現している
例：「1900年1月2日12時0分0秒」は「2.5」
上記画像の「2023年3月22日」(シリアル値：45007）は「1900年1月1日」（シリアル値：１）から45006日後という意味になる。
計算方法
A.UNIX時間はUTC(協定世界時)のため、日本標準時(JST)に時差を調整する。
やり方：UNIX時間に時差9時間(UTC+9)を加算
UNIX時間10桁 ＋ 60秒×60分×9時間
＝UNIX時間10桁 ＋ 32400
B.UNIX時間の単位は秒であるが、シリアル値の単位は日であるため変換する。
やり方：Aの結果を１日分の秒で割る
Aの結果 ÷ 60秒×60分×24時間
＝Aの結果 ÷ 86400
C.シリアル値とUNIX時間では起点となる年月日に差があるため変換する。
やり方：シリアル値の起点である「1900年1月1日0時0分0秒」から
UNIX時間の起点である「1970年1月1日0時0分0秒」までのシリアル値を足す
Bの結果 ＋ 25569
A～Cを一つの計算式にまとめると
=(UNIX時間10桁 + 32400) / 86400 + 25569
まとめ
=(UNIX時間10桁 + 32400) / 86400 + 25569
をすれば、Excelで2023/03/22 11:43:31といった形で表示できるようになります。
