Microsfot Access のタイムスタンプと UNIX Timestamp の処理について

その前に

MS Access のタイムスタンプのシリアル値(0)は 1899-12-30 00:00:00 から開始される。

MS Access のシリアル値を確認する

タイムスタンプ「0」の日時。

SELECT FORMAT(0, 'YYYY-MM-DD hh:nn:ss');
-- 1899-12-30 00:00:00 と表示される。

1899-12-30 00:00:00 のタイムスタンプを求めるには、

SELECT DateDiff('s', 0, #1899-12-30 00:00:00#);
-- 0 と表示される。

MS Access 上での UNIX タイムスタンプの値を求める

-- 1970-01-01 00:00:00 で秒数を求めるとエラーが出るので、
-- 1899-12-30 から 1970-01-01 までの日数を求める
SELECT DateDiff('d', 0, #1970-01-01#);
-- 1899-12-30 から 25,569 日目が 1970-01-01。

仮に 00:00:00 ではなく、17時58分9秒の秒数を調べる場合は、

SELECT DateDiff('s',0, #17:58:09#);
-- 64,689 が表示される
-- 9 + (58 * 60) + (17* 60 * 60) = 9 + 3480 + 61200 = 64689

そして、1970-01-01 の MS Access 上のタイムスタンプを求める

SELECT 25569 * 86400 AS "UNIX Timestamp";
-- 25569 days * seconds in a day = 25569(days) * 60(secs) * 60(mins) * 24(hours) = 25569 * 86400
-- 1899-12-30 00:00:00 から 2,209,161,600 秒後が 1970-01-01 00:00:00 になる。
-- これに一日の経過秒数を足すと、正確な秒数がわかる
-- 1970-01-01 10:58:09 = 2209161600 + 64689 = 2,209,226,289

ここまでが前準備になる。
例えば、2010年11月20日0時0分の MS Access のタイムスタンプは、

SELECT DateDiff('d',0,#2010/11/20 00:00:00#);
-- 40502 日が経過している
SELECT 40502 * 86400;
-- 3499372800 秒が経過している

となり、3499372800 秒が経過していることがわかる。この秒数から 1970-01-01 の秒数を引くと、UNIX タイムスタンプの秒数を求めることができる。

SELECT 3499372800 - 2209161600;
-- 1290211200

これで、2010-11-20 00:00:00 は 1970-01-01 00:00:00 から 1,290,211,200 秒目になることがわかる。
MS Access で求めたタイムスタンプを日付形式に戻すと、

SELECT FORMAT(3499372800/86400, 'YYYY-MM-DD hh:nn:ss');
-- 2010-11-20 00:00:00

注意: タイムゾーンについて

Access はタイムゾーンを考慮されていないため、出力されるタイムスタンプは GMT を基準としている。そのため、プログラムでタイムスタンプを日時に変換する場合は、タイムゾーンを考慮する必要がある。
例えば、2010年11月20日0時0分のタイムスタンプ「1290211200」を Perl で日時に変換する場合、

#!/usr/bin/env perl
# 日本語
use strict;
use warnings;

my $timestamp = 1290211200;

# タイムスタンプを GMT 基準で日時に変換する
my ($sec, $min, $hour, $day, $mon, $year) = gmtime($timestamp);
print "GMT で日時を出力する\n";
printf("%04d-%02d-%02d %02d:%02d:%02d\n", $year + 1900, $mon + 1, $day, $hour, $min, $sec);
print "GMT 基準の日時に時差を考慮する\n";
printf("%04d-%02d-%02d %02d:%02d:%02d\n\n", $year + 1900, $mon + 1, $day, $hour + 9, $min, $sec);

# タイムスタンプを ローカル(ここでは日本)の基準で日時に変換する
($sec, $min, $hour, $day, $mon, $year) = localtime($timestamp);
print "現地のタイムゾーンを考慮する\n";
printf("%04d-%02d-%02d %02d:%02d:%02d", $year + 1900, $mon + 1, $day, $hour, $min, $sec);

GMT で日時を出力する
2010-11-20 00:00:00
GMT 基準の日時に時差を考慮する
2010-11-20 09:00:00

現地のタイムゾーンを考慮する
2010-11-20 09:00:00

となり、Access で保存していた日時とプログラムなどでタイムスタンプから変換した日時が意図したとおりにならない場合があるので注意すること。