SendGrid UNIX Time から EXCEL の日付＋時間に値を変換する方法

SendGrid の Suppressions からダウンロードした CSV ファイルの日付を見てみると、数字が書いてあります。

問い合わせて聞いてみたところ、どうやらこれは UNIX Time で記載してあるらしいです。

問い合わせ

とりあえず、このままだと見てわからないので、とりあえず EXCEL で確認できる方法をメモした記事です。

そもそも UNIX Time とは

UNIX Time とは、基準時刻 (1970/01/01 00:00:00 UTC) からの経過秒数、ということみたいです。

これでわかったのは

• 経過秒数なので、ミリ秒を含まないから整数値
• UTC 基準だから日本標準時の JST にするには +09:00 必要

なので、1970 01/01 00:01:00 UTC なら１時間経過しただけなので、 じゃないかと思います。大きな数字が記録されているのも納得です。

ざっくり計算してみます。

１日で 86,400。１年で 31,536,000。2020 年は 1970 年から 50 年先なので、1,576,800,000。だいぶ created の日付に近そう。

EXCEL 日付との変換式

UNIX Time to UTC（協定世界時）

• (#UNIX Time# / 86400) + 25569
  • 86,400 は１日の秒数
  • 25,569 は UNIX Time の基準時刻に相当するシリアル値

25,569 は、EXCEL のセルの書式設定で「1970/1/1」を入力してから、数値に戻すと「25,569」になります。ざっくりと EXCEL は 1900/01/01 基準で「１」なんで UNIX Time の 70 年 x 365 で 25,550。閏年は４年に一度なんで + 17.5 日。

UNIX Time to JST（日本標準時 UTC+0900）

• ((#UNIX Time# + 32400) / 86400) + 25569
  • 86,400 は１日の秒数
  • 32,400 は＋９時間
  • 25,569 は UNIX Time の基準時刻に相当するシリアル値

結果

問題なく変換できました。

CSV エクスポート

SendGrid の CSV エクスポートはこちら。

参考

• IT覚書 - Excel形式の日時をUnix時間に変換する式

THE MODEL(MarkeZine BOOKS) マーケティング・インサイドセールス・営業・カスタマーサクセスの共業プロセス

事例で学ぶサブスクリプション

カスタマーサクセス――サブスクリプション時代に求められる「顧客の成功」10の原則