SQL Serverにおける日付/時刻計算のベストプラクティス

はじめに

　数か月前、経験豊かなデータベース管理者兼データベースプログラマが私に質問してきました。Microsoft SQL Serverの日付/時刻関数を使わずに、例えばGETDATE() + 10のような演算式を使って日付/時刻データを操作するうまいやり方を知らないかというのです。そのとき私は、まさに同じ件を話題にしていたインターネットフォーラムのディスカッションを思い出しました。ディスカッションの参加者は皆、Microsoftがこの便利な機能を実装し損なったという事実にうんざりしていました。そこで、私はこのトピックについてもっと掘り下げようと決意しました。その結果がこの記事です。

計算の仕組み

　SQL ServerのDATEADD()関数を使って、次の例を実行してみてください。

SELECT GETDATE();
SELECT DATEADD(dd, 1, GETDATE());
SELECT GETDATE() + 1;

Results:

2007-03-15 16:21:41.630
2007-03-16 16:21:41.630
2007-03-16 16:21:41.630

　ご覧の通り、DATEADD()関数を使っても、プラス記号（+）を使って加算しても、結果はまったく同じです。つまり、現在の日付と時刻に1日が加算されています。DATEADD()でこの結果が得られるのは予想できるでしょう。しかし、加算演算でも同じ結果になるのはなぜでしょうか？この答えを知るには、SQL Serverの日付/時刻の内部的な格納方法を詳しく調べる必要があります。

　ご存知のように、datetimeデータ型は8バイトで、4バイトには1900年1月1日からの経過日数（または過去にさかのぼった日数）が格納され、もう4バイトには午前0時からのクロックティック数（1ティック＝3.33ミリ秒）が格納されています。また、datetimeデータ型より精度は低くなりますが、4バイトのみを使用するsmalldatetimeデータ型もあります。smalldatetimeデータ型では、1900年1月1日から起算した日数と、午前0時から何分が経過したかが格納されます。数値はすべて整数として格納されます。従って、SELECT GETDATE() + 1では、実際は整数値にdatetimeデータを加算していることになります。datetime型の方が整数型よりも優先度が高いため、優先度の低い加数である整数1は、暗黙的にdatetimeデータ型に変換されます。

　次の例では、1をdatetime型に変換しています。この結果は、1900年1月1日から1日が経過した日付として扱われます。

SELECT CAST(1 as datetime);

Results:

1900-01-02 00:00:00.000

　SELECT GETDATE() + 1では、内部的に整数として解釈される2つのdatetime値が加算されます。その結果、プラス（+）記号を使う加算演算が完全に有効になります。例えば、次のコードはすべて正しいステートメントです。

SELECT GETDATE();
SELECT DATEADD(dd, 1, GETDATE());
SELECT GETDATE() + 1;
SELECT GETDATE() + 'Jan 02, 1900';

Results:

2007-03-16 23:01:37.420
2007-03-17 23:01:37.420
2007-03-17 23:01:37.420
2007-03-17 23:01:37.420

　日付/時刻の計算で加算演算子（+）を使用するには、datetime型の加数が少なくとも1つ必要で、その加数は優先度が最も高くないといけません。

　次の例を試してみてください。

SELECT DATEADD(dd, 1, 'Mar 17, 2007');
SELECT 'Mar 17, 2007' + 1;
SELECT 'Mar 17, 2007' + 'Jan 02, 1900';
SELECT GETDATE() + 1 + 'Jan 02, 1900';

　お分かりのように、最初のSELECTではSQL ServerのDATEADD()関数を使用しており、varchar型として記述されている日付を認識し、正しい結果を導き出します。2番目のSELECTは失敗し、「Conversion failed when converting the varchar value 'Mar 17, 2007' to data type int.（varchar型の値'Mar 17, 2007'をintデータ型に変換しているときに変換エラーが発生しました）」というエラーメッセージが表示されます。このエラーが発生するのは、varchar型は整数型よりも優先度が低いため、varchar型の加数を暗黙的に整数データ型に変換しなければならないからです。このような変換は不可能です。

　3番目のSELECTは動作しますが、特に意味をなしません。これは、2つのvarchar式を連結しているだけにすぎません。4番目のSELECTはとても興味深いものです。2番目と3番目の加数は、1番目の加数よりも優先度が低いため、暗黙的にdatetimeデータ型に変換する必要があります。それぞれは1日として解釈されるため、GETDATE()関数の結果に2日が加算されます。

最初の落とし穴

　この時点で、日付/時刻の計算に加減演算子を使ってもまったく問題ないと考え始めるかもしれません。ところが、そうでもないのです。例えば、2つの日付の間隔（日単位）を、次の2つの方法を使って計算してみましょう。

DECLARE @dt1 datetime, @dt2 datetime;
SELECT @dt1 = 'Mar 17, 2007 09:09:00', @dt2 = 'Mar 17, 2007 22:09:00';
SELECT DATEDIFF(dd, @dt1, @dt2);
SELECT CAST((@dt2 - @dt1) as int);

Results:

0
1

　SQL ServerのDATEDIFF()関数を使って計算するのと、減算演算子を使って計算するのでは結果が異なります。最初の結果（0）が正しく、2番目の結果（1）は間違っています。なぜこのような結果になるのでしょうか？ datetime値を整数に変換すると、その結果は一番近い整数に四捨五入されます。どのように四捨五入されるかは、datetime値の時間部分によって変わってきます。次の例を考えてみましょう。

DECLARE @dt3 datetime, @dt4 datetime;
SELECT @dt3 = 'Mar 17, 2007 11:59:59.994',
       @dt4 = 'Mar 17, 2007 11:59:59.997';
SELECT CAST(@dt3 AS int);
SELECT CAST(@dt4 AS int)
SELECT CAST(CAST(@dt3 AS int) AS datetime);
SELECT CAST(CAST(@dt4 AS int) AS datetime);

Results:

39156
39157
2007-03-17 00:00:00.000
2007-03-18 00:00:00.000

　この例では、正午少し前のタイムスタンプを2つ使用しています。この2つのタイムスタンプの間隔は3ミリ秒です。しかし2つの日付の値を整数に変換し、その結果を日付の値に再変換すると、1日のずれが生じます。同様に、異なる日付（3月16日と3月17日）を表す2つのdatetime値が、次のように同じ日付に誤って変換されることもあります。

DECLARE @dt3 datetime, @dt4 datetime;
SELECT @dt3 = 'Mar 16, 2007 12:00:01.000',
       @dt4 = 'Mar 17, 2007 11:59:59.994';
SELECT CAST(@dt3 AS int);
SELECT CAST(@dt4 AS int)
SELECT CAST(CAST(@dt3 AS int) AS datetime);
SELECT CAST(CAST(@dt4 AS int) AS datetime);

Results:

39156
39156
2007-03-17 00:00:00.000
2007-03-17 00:00:00.000

　ここで、もう1つの例を見てみましょう。例えば、ここにID、時間、価格といった販売取引内容が格納されているテーブルがあります。そして、このテーブルから、1日あたりの取引合計金額と取引数を調べる必要があるとします（よくある処理です）。非常に大きなテーブルなので、日付ではなく期間ごとに取引をグループ化し、クエリのスピードを上げることにしました。ただし、SQL ServerのDATEDIFF()関数は使用しません。代わりに、より「高度な」方法を使って、つまり、datetimeデータを整数に変換し、シンプルな算術減算を使って期間を計算します。

　このシナリオではどうなるのかを見てみましょう。

SET NOCOUNT ON;
IF OBJECT_ID('sales', 'U') IS NOT NULL
DROP TABLE sales

CREATE TABLE sales(
        transactionID int,
        transactionTime datetime,
        amount decimal(4,2));

INSERT INTO sales VALUES(1, 'Mar 17, 2007 08:00:23', 24.34);
INSERT INTO sales VALUES(2, 'Mar 17, 2007 10:33:23', 88.54);
INSERT INTO sales VALUES(3, 'Mar 17, 2007 12:00:44', 12.12);
INSERT INTO sales VALUES(4, 'Mar 17, 2007 14:23:23', 43.25);
INSERT INTO sales VALUES(5, 'Mar 17, 2007 16:45:22', 76.34);
INSERT INTO sales VALUES(6, 'Mar 17, 2007 17:11:22', 51.11);
INSERT INTO sales VALUES(7, 'Mar 17, 2007 19:45:23', 30.99);

SELECT COUNT(*) AS #Trans, SUM(amount) AS totalAmount
    FROM sales
    GROUP BY DATEDIFF(dd, 0, transactionTime);

SELECT COUNT(*) AS #Trans, SUM(amount) AS totalAmount
    FROM sales
    GROUP BY CAST(transactionTime AS int);

SELECT COUNT(*) AS #Trans, SUM(amount) AS totalAmount
    FROM sales
    GROUP BY CAST((transactionTime - 0) AS int);

Results:

#Trans      totalAmount
----------- ---------------------------------------
7           326.69

#Trans      totalAmount
----------- ---------------------------------------
2           112.88
5           213.81

#Trans      totalAmount
----------- ---------------------------------------
2           112.88
5           213.81

　SQL ServerのDATEDIFF()は、FLOOR()関数と同じように日付を処理するため、正しい結果を導きだします。具体的には、DATEDIFF()は各日付の時間部分を削除し、日付部分のみを操作します。他の方法、つまり、整数に変換したり、直接減算したりする方法では、それぞれのdatetime値が時間部分の値を考慮して一番近い整数に四捨五入されます。そのため、2番目と3番目のSELECTステートメントは誤った結果になります。

日付/時刻データの時間部分の算術演算

　ここまでは、日付/時刻値の日付部分における算術演算について説明してきました。SELECT GETDATE() + 1という式では、2番目の項は、GETDATE()関数の戻り値に加算すべき日数であると暗黙的に見なされます。しかし、現在のdatetime値に1時間または1分を加算したい場合はどうすればいいのでしょうか？ SQL ServerのDATEADD()関数を使用すれば、この計算は非常に簡単です。しかし、加減演算子を使うとなると話は厄介になってきます。

　そのやり方を確認してみましょう。1日は24時間で、分に換算すれば1,440分、秒に換算すれば86,400秒です。そこで、次のステートメントを実行します。

SELECT GETDATE() + 1/24

　これで、現在のdatetimeに1時間をプラスした値が返されると思うでしょう。しかし実際は1/24だけでは使えません。除数と非除数はどちらも整数であるため、この除算の結果はゼロになるからです。正しい結果を得るには、両方の整数をdecimalデータ型またはfloatデータ型に変換する必要があります。

-- How to add one hour
---------------------------------------
SELECT GETDATE()
SELECT DATEADD(hh, 1, GETDATE())
SELECT GETDATE () + CAST(1 AS dec(9,4))/ CAST (24 AS dec(9,4))
SELECT GETDATE () + CAST(1 AS dec)/ CAST (24 AS dec)
SELECT GETDATE () + CAST (1 AS float)/ CAST (24 AS float)

Results:

2007-03-25 20:31:13.870
2007-03-25 21:31:13.870
2007-03-25 21:31:13.867
2007-03-25 21:31:13.870
2007-03-25 21:31:13.870

-- How to add one minute
---------------------------------------
SELECT GETDATE()
SELECT DATEADD(mi, 1, GETDATE())
SELECT GETDATE () + CAST(1 AS dec(9,4))/ CAST (1440 AS dec(9,4))
SELECT GETDATE () + CAST(1 AS dec(18.9))/ CAST (1440 AS dec(18.9))
SELECT GETDATE () + CAST (1 AS float)/ CAST (1440 AS float)

Results:

2007-03-25 20:35:15.127
2007-03-25 20:36:15.127
2007-03-25 20:36:15.123
2007-03-25 20:36:15.127
2007-03-25 20:36:15.127

-- How to add one second
---------------------------------------
SELECT GETDATE()
SELECT DATEADD(ss, 1, GETDATE())
SELECT GETDATE () + CAST(1 AS dec(9,4))/ CAST (86400 AS dec(9,4))
SELECT GETDATE () + CAST(1 AS dec(18.9))/ CAST (86400 AS dec(18.9))
SELECT GETDATE () + CAST(1 AS dec(24.18))/ CAST (86400 AS dec(24.18))
SELECT GETDATE () + CAST (1 AS float)/ CAST (86400 AS float)

Results:

2007-03-25 20:42:26.617
2007-03-25 20:42:27.617
2007-03-25 20:42:27.613
2007-03-25 20:42:27.613
2007-03-25 20:42:27.613
2007-03-25 20:42:27.617

-- How to add one second, using variables
------------------------------------------

DECLARE @dec1 dec(24,18), @dec2 dec(24,18),
        @dec3 dec(24,18), @dt datetime
SELECT @dec1 = 1, @dec2 = 86400, @dt = GETDATE();
SELECT @dec3 = @dec1 / @dec2;

SELECT @dt
SELECT DATEADD(ss, 1, @dt)
SELECT @dt + @dec3
SELECT @dt + CAST (1 AS float)/ CAST (86400 AS float)

Results:

2007-03-25 20:49:16.817
2007-03-25 20:49:17.817
2007-03-25 20:49:17.813
2007-03-25 20:49:17.817

　最後の例で分かるように、SQL ServerのDATEADD()関数の動作は完璧です。しかし、加算演算子を使った方法では問題が発生することがあります。例えば、1時間または1分を加算する場合は、decimalデータ型のための十分な精度をあらかじめ見極める必要があります。精度が不十分だと、結果がわずかにずれてしまいます。1秒を加算する場合は、加算演算子とdecimalデータ型変換を利用する方法では正確な結果はまったく得られません。

　これに対して、floatデータ型に変換すれば、加算演算子を使った方法でも安全かつ正確に時間を計算できるように見えます。しかし、実際に使用し始めると問題が発生する場合があります。値が重複したり欠落したりするのです。この問題を説明するために、補助テーブルを作成してデータを投入します。

SET NOCOUNT ON;
DECLARE @max int, @cnt int;
SELECT @cnt = 10000;

IF EXISTS(SELECT * FROM sysobjects
WHERE ID = (OBJECT_ID('sequence')) AND xtype = 'U')
DROP TABLE sequence;

CREATE TABLE sequence(num int NOT NULL);
INSERT INTO sequence VALUES(1);
SELECT @max = 1;
WHILE(@max <= @cnt)
BEGIN
INSERT INTO sequence
SELECT @max + num FROM sequence;
SELECT @max = MAX(num) FROM sequence;
END

　このスクリプトを実行すると、テーブルシーケンスに16384の連続する番号が順番に挿入されます（この16,384という数値に特別な意味はありません。説明のために適当に選んだ数値です）。

　ここで、補助テーブルとSQLの日付/時刻関数を使用して、時間のシーケンスを生成します。

IF EXISTS(SELECT * FROM sysobjects
WHERE id = OBJECT_ID('test'))
DROP TABLE test;

SELECT num, DATEADD(hh, num, 'Dec 31, 2006 23:00:00') dt
    INTO test
    FROM sequence;

SELECT * FROM test;

Results:

num         dt
----------- -----------------------
1           2007-01-01 00:00:00.000
2           2007-01-01 01:00:00.000
3           2007-01-01 02:00:00.000
. . . . . . . . . . . . . . . . . .
3099        2007-05-10 02:00:00.000
3100        2007-05-10 03:00:00.000
. . . . . . . . . . . . . . . . . .
16381       2008-11-13 12:00:00.000
16382       2008-11-13 13:00:00.000
16383       2008-11-13 14:00:00.000
16384       2008-11-13 15:00:00.000

　DATEADD()関数は期待どおりに動作し、この関数によって1時間間隔のdatetime値のシーケンスが生成されます。

　ここで、今生成したシーケンスを同じDATEADD()関数を使ってロールアップします。

SELECT DISTINCT DATEADD(hh, -num, dt) FROM test

Results:

2006-12-31 23:00:00.000

　この結果を見れば、SQL Serverの日付/時間関数によって日付/時刻の値が適切に生成されていることが分かります。次に、算術演算子（+）を使用したソリューションをテストするために、次の例を実行します。

DECLARE @time float
SELECT @time = CAST(1 as float)/CAST(24 as float)

IF EXISTS(SELECT * FROM sysobjects
WHERE id = OBJECT_ID('test'))
DROP TABLE test;

SELECT num,
    (CAST('Dec 31, 2006 23:00:00' AS datetime) + @time * num) dt
    INTO test
    FROM sequence;

SELECT * FROM test;

num         dt
----------- -----------------------
1           2007-01-01 00:00:00.000
2           2007-01-01 01:00:00.000
3           2007-01-01 02:00:00.000
4           2007-01-01 03:00:00.000
5           2007-01-01 03:59:59.997
6           2007-01-01 05:00:00.000
7           2007-01-01 05:59:59.997
8           2007-01-01 07:00:00.000
9           2007-01-01 08:00:00.000
10          2007-01-01 08:59:59.997
. . . . . . . . . . . . . . . . . .
16380       2008-11-13 11:00:00.000
16381       2008-11-13 11:59:59.997
16382       2008-11-13 12:59:59.997
16383       2008-11-13 14:00:00.000
16384       2008-11-13 14:59:59.997

　これを見ると、加算演算子によって得られる結果が正しくないことが分かります。場合によっては、期待する値との間に3ミリ秒ものずれが生じています。生成された日付/時刻値のシーケンスをロールアップすると、複数の「シード値」が導き出されます。これは正しい動作ではありません。

SELECT DISTINCT DATEADD(hh, -num, dt) FROM test

Results:

2006-12-31 22:59:59.997
2006-12-31 23:00:00.000

　この3ミリ秒のずれは、ほとんどのアプリケーションで許容されると考えるかもしれません。しかし、この一見すると取るに足りない問題が、大きな問題につながる可能性があるのです。次の例を見てください。

SELECT CONVERT(varchar(100), dt, 100)
    FROM test
    ORDER BY num

Results:

Jan  1 2007 12:00AM
Jan  1 2007  1:00AM
Jan  1 2007  2:00AM
Jan  1 2007  3:00AM
Jan  1 2007  3:59AM
Jan  1 2007  5:00AM
Jan  1 2007  5:59AM
. . . . . . . . . .
Nov 13 2008  5:59AM
Nov 13 2008  6:59AM
Nov 13 2008  8:00AM
Nov 13 2008  8:59AM
Nov 13 2008  9:59AM
Nov 13 2008 11:00AM
Nov 13 2008 11:59AM
Nov 13 2008 12:59PM
Nov 13 2008  2:00PM
Nov 13 2008  2:59PM

　この例では、CONVERT()関数によって日付/時刻の書式を変更しています。その結果、ずれが3ミリ秒から1分に増えており、これは、もはや許容できる値とは言えません。問題はこれだけではありません。分または秒のシーケンスを生成するとなると、事態はさらに悪化します。次の例を見てください。

DECLARE @time float
SELECT @time = cast(1 as float)/cast(1440 as float)

IF EXISTS(SELECT * FROM sysobjects
WHERE id = OBJECT_ID('test'))
DROP TABLE test;

SELECT num,
    (CAST('Dec 31, 2006 23:59:00' AS datetime) + @time * num) dt
    INTO test
    FROM sequence;

SELECT * FROM test;

Results:

num         dt
----------- -----------------------
1           2007-01-01 00:00:00.000
2           2007-01-01 00:01:00.000
. . . . . . . . . . . . . . . . . .
1579        2007-01-02 02:17:59.997
1580        2007-01-02 02:19:00.000
1581        2007-01-02 02:19:59.997
. . . . . . . . . . . . . . . . . .
16382       2007-01-12 09:01:00.000
16383       2007-01-12 09:01:59.997
16384       2007-01-12 09:03:00.000

　このように、やはり不正確な値が生成されています。さらに、これらの値を別の書式に変換すると、日付が重複または欠落してしまいます。次の例を見てください。

SELECT CONVERT(varchar(100), dt, 100)
    FROM test
    ORDER BY num

Results:

Jan  1 2007 12:00AM
Jan  1 2007 12:01AM
. . . . . . . . . .
Jan  2 2007 12:00AM
Jan  2 2007 12:00AM
Jan  2 2007 12:02AM
Jan  2 2007 12:02AM
Jan  2 2007 12:04AM
Jan  2 2007 12:04AM
. . . . . . . . . .
Jan 12 2007  9:00AM
Jan 12 2007  9:01AM
Jan 12 2007  9:01AM
Jan 12 2007  9:03AM

　例えば、「Jan 02, 2007 12:02AM」という値は2つありますが、「Jan 02, 2007 12:03AM」はありません。

　重複している値のリストを確認するには、次のクエリを実行します。

SELECT COUNT(*), CONVERT(varchar(100), dt, 100)
FROM test
GROUP BY CONVERT(varchar(100), dt, 100)
HAVING COUNT(*) > 1
ORDER BY 2

　最後に、同じ方法で秒のシーケンスを生成します。

DECLARE @time float
SELECT @time = cast(1 as float)/cast(86400 as float)

IF EXISTS(SELECT * FROM sysobjects
WHERE id = OBJECT_ID('test'))
DROP TABLE test;

SELECT num,
    (CAST('Dec 31, 2006 23:59:59' AS datetime) + @time * num) dt
    INTO test
    FROM sequence;

SELECT * FROM test;

Results:

num         dt
----------- -----------------------
1           2007-01-01 00:00:00.000
2           2007-01-01 00:00:01.000
3           2007-01-01 00:00:02.000
4           2007-01-01 00:00:03.000
5           2007-01-01 00:00:03.997
6           2007-01-01 00:00:05.000
7           2007-01-01 00:00:06.000
8           2007-01-01 00:00:07.000
9           2007-01-01 00:00:08.000
10          2007-01-01 00:00:08.997
11          2007-01-01 00:00:09.997
. . . . . . . . . . . . . . . . . .
16382       2007-01-01 04:33:00.997
16383       2007-01-01 04:33:02.000
16384       2007-01-01 04:33:03.000

SELECT CONVERT(varchar(100), dt, 120)
    FROM test
    ORDER BY num

Results:

2007-01-01 00:00:00
2007-01-01 00:00:01
2007-01-01 00:00:02
2007-01-01 00:00:03
2007-01-01 00:00:03
2007-01-01 00:00:05
. . . . . . . . . .
2007-01-01 04:32:55
2007-01-01 04:32:56
2007-01-01 04:32:56
2007-01-01 04:32:58
2007-01-01 04:32:59
2007-01-01 04:33:00
2007-01-01 04:33:00
2007-01-01 04:33:02
2007-01-01 04:33:03

　この最後の例でも、同じ問題が起きています。また、算術演算子を使って日付/時刻を操作することで、別の問題が発生する場合もあります。例えば、不思議な結果になったり、パフォーマンスが低下したり、ここで取り上げなかった問題が発生することもあります。これらの問題はすべて、SQL Serverの日付/時刻関数を使用すれば回避できます。

日付/時刻関数の副次的なメリット

　SQL Serverの日付/時刻関数のメリットはエラーを回避できるだけではありません。この関数を使用すると、プログラマが自分の作業をより簡単かつ快適に行えるようになります。例えば、2007年のそれぞれの月の5番目の日付を生成しなければならない場合、DATEADD()関数を使用すれば簡単です。

SELECT DATEADD(mm, num - 1, 'Jan 05, 2007') AS [5th_Day]
    FROM sequence
    WHERE num <= 12

Results:

5th_Day
-----------------------
2007-01-05 00:00:00.000
2007-02-05 00:00:00.000
2007-03-05 00:00:00.000
2007-04-05 00:00:00.000
2007-05-05 00:00:00.000
2007-06-05 00:00:00.000
2007-07-05 00:00:00.000
2007-08-05 00:00:00.000
2007-09-05 00:00:00.000
2007-10-05 00:00:00.000
2007-11-05 00:00:00.000
2007-12-05 00:00:00.000

　このクエリの優れた点は、その利便性です。それぞれの月または年の日数を把握している必要はありません。必要なロジックと計算は既にSQL Server関数に含まれており、プログラマがそれを意識する必要はありません。算術演算子（+）を使用して同じ作業を行うには、ロジックを自分自身で実装する必要があります。ロジックを自分自身で実装すると、開発やデバッグに時間がかかり、エラーが発生する可能性も高くなります。

　SQL Serverの日付/時刻の計算で、算術演算子を使うべきでしょうか？いいえ。使うべきではないでしょう。非常にシンプルな計算、例えば、GETDATE() + 1についてはSQL Serverが内部的に1を日付に変換してくれるので、算術演算子を使うことは可能です。しかし、やはり算術演算子と日付を一緒に使用するのは避けた方が良いでしょう。そして、より複雑な日付/時刻計算で算術演算子を使う場合は、手を抜かずに細心の注意を払う必要があります。

著者紹介

Alex Kozak（Alex Kozak）

SAP Canadaの上級DBA/アナリスト。データベースとプログラミングに15年以上従事。MSDNライブラリにも多数投稿。