日付/時刻データの時間部分の算術演算
ここまでは、日付/時刻値の日付部分における算術演算について説明してきました。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を日付に変換してくれるので、算術演算子を使うことは可能です。しかし、やはり算術演算子と日付を一緒に使用するのは避けた方が良いでしょう。そして、より複雑な日付/時刻計算で算術演算子を使う場合は、手を抜かずに細心の注意を払う必要があります。
