日付データのハック

以前のブログのデータを，Xoopsに移そうと考えている．以前のブログ(はてな)のデータベースは csv と xml と movabletype の3種類のデータ構造でダウンロードできるが，日付データは普通に2006-07-08 の形である．

さて，MySQL 上でのこの WebLog のデータをダウンロードしてみると，日付データは
　1153876200
　1153990720
　1154094062
　1154184623
という形で何らかの値になっているようである．
どんな形式で日付が保存されているか検索してみるものの，「xoop Mysql 日付 値」程度の検索語だと見つけられないので，自分でハックすることにした．

ちなみに一番上から
　7/26
　7/27
　7/28
　7/29
である．
値の差をとると
　114520
　103342
　90561
ということで，1日で10万くらいずつ増えている．
1日は86400秒なので，おそらくこの値はある時点からの秒数なのだろう．
あとから書き込んだため，書き込み時刻を 0:00 にした8/8，8/9 の値
　1154962800
　1155049200
の差をとると確かに 86400 なので，秒数で管理していることは確かだ．

エクセルに読み込んで考える．
エクセルは整数で日付，小数で時刻を表している．
7/26 の 1153876200 を 86400 秒で割れば日付部分が取り出せるだろう．
　1153876200 / 86400 ＝ 13355.04861
13355 はエクセルでは 1936/7/24 である．
エクセルの日付は1900/01/01 を「1」とする整数なので，今年の日付の値は38000を超える．
13355 はエクセルの値と30年離れていることがわかる．ということは MySQL は 1970/01/01 を 「1」としているのだろう．値に「1970/01/01」を足してみる．
　=1153876200/86400+"1970/01/01"
として秒数まで表示する書式にすると
　2006/7/26 1:10:00
となり大正解．
しかし書き込み時刻を 0:00 にした8/8，8/9 にした
　1154962800
　1155049200
の部分は
　2006/8/7 15:00:00
　2006/8/8 15:00:00
となる．9時間遅れているということはxoops ではGMTに換算して保存していることがわかる．JST は GMT より9時間進んでいるので，
　=1154962800/86400+"1970/01/01"+9/24
で
　2006/8/7 0:00:00
と正しい JST になった．

ということは，以前のブログの日付データを元にエクセルでシリアル値を計算し，
　(シリアル値-9/24-"1970/01/01")*86400
で MySQL の日付データになることがわかった．

これで以前のブログを自分のサイトに移すめどがちょっとだけ立った．あとはデータ構造を一気に同じにするスクリプトを考えねば．