MongoDBのObjectIDからUnix時間を取得するExcel式

ObjectIDとは

ObjectIDとは、MongoDBでドキュメントを作成すると、自動的に挿入される管理フィールドのことだ。
ドキュメントの一意性を担保する目的で使用され、フィールドの名前は「_id」に固定されている。

MongoDBでは、ドキュメントとはデータの行を表す用語となる。
RDBのレコードに相当する概念だ。

通常は、ドキュメントの最初のフィールドが、ObjectIDとなる。

ObjectIDの構造

ObjectIDの値は、24文字からなる16進文字列となっている。
ObjectIDを構成する文字列は、それぞれ「作成時刻」、「ランダムな値」、「カウンター」などと呼ぶべき3つの部分に分かれていて、それぞれ固有の意味を持っている。

各部分の位置と意味を以下に示す。

作成時刻

1文字目から8文字目までの8文字は、ドキュメントが作成された時刻を表す。
この時刻は、Unix時間で表現される。

ランダムな値

9文字目から18文字目までの10文字には、ランダムな値が格納されている。

ランダムな値の生成方法は公式には規定されていないらしく、実装依存になっているようだ。
実際には、マシンIDとプロセスIDから生成されているようだ。

カウンター

19文字目から24文字目までの6文字には、カウンターの値が格納されている。
カウンターは0からではなく、ランダムな初期値から開始される。

Unix時間とは

実は、おととい投稿した記事で解説していたりする。
一言で言えば、Unix系のシステムで広く使われている、時間の表現方式のことだ。

Excel形式の日時をUnix時間に変換する式

ObjectIDからUnix時間を取得するには

定義より、ObjectIDの先頭8文字がUnix時間なので、これを取得して数値化すればよい。
Excelには、16進文字列を数値に変換する関数「HEX2DEC」が用意されているので、これを利用する。

セルA1にObjectIDが入力されている場合は、以下のような式でUnix時間を取得することができる。

=HEX2DEC(LEFT(A1, 8))

この式を、セルB1に入力した例は、以下の通りとなる。

まとめ

A1セルにObjectIDが格納されている場合、以下のような式を使ってUnix時間を求めることができる。

=HEX2DEC(LEFT(A1, 8))