エクセルのシリアル値から日付集計 #MongoDB

あるデータの日付集計を頼まれたのだが,値が日付どころかどう見てもunixtimeでもない。
こんな感じ。
40820.89445601852

どうやらエクセルのシリアル値がそのまんま入っているようだ。
データベースをいじらすに集計したいところ。

エクセルのシリアル値からunixtimeへの変換

Excelの日付(シリアル値)についてと、UNIX時間との変換 (JavaScript, VBA) - Qiitaに詳しく書かれているので参考にした。感謝
unixtimeは1970/01/01からの時間(今回はミリ秒とする),
excelのシリアル値は1900/01/01からの1日=1の値なので,
70(年)*365(日)+17(日うるう年の分)+2(日 1969/12/31からの1日分と,謎の1900/02/29)
をexcelのシリアル値から引くことで,unixtrimeと起点が一緒になる。
あとは単位をミリ秒に合わせるだけ。

aggregationでフィールドと四則演算

aggregationを使うとき "$excel_serial" - 25569 みたいに
フィールドの値と四則演算するときは,それを行うオペレーターを用いる。

  {$add: [<値1>, <値2>]}       // 値1 + 値2
  {$subtract: [<値1>, <値2>]}  // 値1 - 値2
  {$multiply: [<値1>, <値2>]}  // 値1 * 値2
  {$divide: [<値1>, <値2>]}    // 値1 / 値2

aggregationで演算結果からISODateに変換

演算結果を直接new Date()でISODate型に変えようとすると

> db.excel_data.aggregate([{$project: {d: Date({$multiply: [{$subtract:["$excel_serial", (70*365+17+2)]}, 24*60*60*1000]})}}])
assert: command failed: {
    "errmsg" : "exception: FieldPath 'Fri Nov 27 2015 14:56:36 GMT+0900 (JST)' doesn't start with $",
    "code" : 16873,
    "ok" : 0
} : aggregate failed

こんな感じのエラーが出る。
これは一度new Date(0)で初期状態のISODate型をつくっておいて,
それとさっき計算したunixtimeの値を足し算することで回避できる。

ISODate型から日付だけ取り出す

フォーマットを指定して変換できる.これは便利。
$dateToString (aggregation) — MongoDB Manual 3.0

{$dateToString: {format: "%Y%m%d", date: <さっきつくったISODate()>

できたもの

db.excel_data.aggregate([
    {$project: {
        d:{$dateToString: {
            format: "%Y%m%d", 
            date: {
                $add: [
                    new Date(0),
                    {$multiply:[
                        {$subtract:["$excel_serial", 70*365+17+2]},
                        24*60*60*1000
                    ]}
                ]
            }
       }}
    }}, 
    {$group: {
        _id: "$d",
        count: {$sum: 1}
    }},
    {$out: "count_date"}
])

やったぜ。