[解決済み] datetime値の時間部分を削除する方法（SQL Server）?

質問

私が使っているのは、こんな感じです。

SELECT CAST(FLOOR(CAST(getdate() as FLOAT)) as DATETIME)

より良い、よりエレガントな方法があるのではないかと思っています。

要件です。

• 可能な限り高速であること（キャストは少ないほど良い）。
• 最終結果は datetime 型で、文字列ではありません。

どのように解決するのですか？

SQL Server 2008 およびそれ以降

SQL Server 2008 以降では、もちろん最速の方法は Convert(date, @date) . これをキャストバックすることで datetime または datetime2 を使用します。

SQL Server 2005 およびそれ以前のバージョンでは、何が本当にベストなのか？

SQL Server で日付から時間を切り捨てるのに最も速い方法について、一貫性のない主張を見たことがあります。そこで、より厳密なテストを行い、私が何か間違いを犯した場合に人々が訂正できるように、すべての人にスクリプトを提供しましょう。

フロート変換は正確ではありません。

まず、私は datetime を float に変換すると、正しく変換されないからです。時間削除を正確に行うことで逃げられるかもしれませんが、開発者に「これは安全な操作です」と暗黙のうちに伝えてしまうので、私はこれを使うのは良くないと思いますし ではありません。 . 見てみてください。

declare @d datetime;
set @d = '2010-09-12 00:00:00.003';
select Convert(datetime, Convert(float, @d));
-- result: 2010-09-12 00:00:00.000 -- oops

これは、私たちのコードやオンラインの例で人々に教えるべきことではありません。

また、それは最速の方法でもありません!

証明 - パフォーマンステスト

もしあなたが自分自身でいくつかのテストを行い、異なる方法が実際にどのように積み重なるかを見たいのであれば、このセットアップスクリプトが必要でしょう。

create table AllDay (Tm datetime NOT NULL CONSTRAINT PK_AllDay PRIMARY KEY CLUSTERED);
declare @d datetime;
set @d = DateDiff(Day, 0, GetDate());
insert AllDay select @d;
while @@ROWCOUNT != 0
   insert AllDay
   select * from (
      select Tm =
         DateAdd(ms, (select Max(DateDiff(ms, @d, Tm)) from AllDay) + 3, Tm)
      from AllDay
   ) X
   where Tm < DateAdd(Day, 1, @d);
exec sp_spaceused AllDay;  -- 25,920,000 rows

これはデータベースに427.57MBのテーブルを作成し、実行するのに15分から30分かかることに注意してください。データベースが小さく、10%の成長に設定されている場合、最初に十分なサイズを設定した場合よりも時間がかかります。

では、実際のパフォーマンス テスト スクリプトを紹介します。2600 万行の場合、非常に高価であり、メソッド間のパフォーマンスの違いを隠してしまうため、意図的にクライアントに行を返さないようにしていることに留意してください。

パフォーマンス結果

set statistics time on;
-- (All queries are the same on io: logical reads 54712)
GO
declare
    @dd date,
    @d datetime,
    @di int,
    @df float,
    @dv varchar(10);

-- Round trip back to datetime
select @d = CONVERT(date, Tm) from AllDay; -- CPU time = 21234 ms,  elapsed time = 22301 ms.
select @d = CAST(Tm - 0.50000004 AS int) from AllDay; -- CPU = 23031 ms, elapsed = 24091 ms.
select @d = DATEDIFF(DAY, 0, Tm) from AllDay; -- CPU = 23782 ms, elapsed = 24818 ms.
select @d = FLOOR(CAST(Tm as float)) from AllDay; -- CPU = 36891 ms, elapsed = 38414 ms.
select @d = CONVERT(VARCHAR(8), Tm, 112) from AllDay; -- CPU = 102984 ms, elapsed = 109897 ms.
select @d = CONVERT(CHAR(8), Tm, 112) from AllDay; -- CPU = 103390 ms,  elapsed = 108236 ms.
select @d = CONVERT(VARCHAR(10), Tm, 101) from AllDay; -- CPU = 123375 ms, elapsed = 135179 ms.

-- Only to another type but not back
select @dd = Tm from AllDay; -- CPU time = 19891 ms,  elapsed time = 20937 ms.
select @di = CAST(Tm - 0.50000004 AS int) from AllDay; -- CPU = 21453 ms, elapsed = 23079 ms.
select @di = DATEDIFF(DAY, 0, Tm) from AllDay; -- CPU = 23218 ms, elapsed = 24700 ms
select @df = FLOOR(CAST(Tm as float)) from AllDay; -- CPU = 29312 ms, elapsed = 31101 ms.
select @dv = CONVERT(VARCHAR(8), Tm, 112) from AllDay; -- CPU = 64016 ms, elapsed = 67815 ms.
select @dv = CONVERT(CHAR(8), Tm, 112) from AllDay; -- CPU = 64297 ms,  elapsed = 67987 ms.
select @dv = CONVERT(VARCHAR(10), Tm, 101) from AllDay; -- CPU = 65609 ms, elapsed = 68173 ms.
GO
set statistics time off;

ざっくりとした分析

これに関していくつか注意点があります。まず、GROUP BYや比較を行うだけであれば、変換を行う必要はありません。 datetime . したがって、最終的な値を表示する必要がない限り、これを避けることでCPUを節約することができます。変換されていない値をGROUP BYして、SELECT句にのみ変換を記述することも可能です。

select Convert(datetime, DateDiff(dd, 0, Tm))
from (select '2010-09-12 00:00:00.003') X (Tm)
group by DateDiff(dd, 0, Tm)

また、数値の変換を datetime に戻すのに少し時間がかかるだけで varchar の変換はほぼ 2 倍になるのですか？これにより、クエリの日付計算に費やされるCPUの部分が明らかになりました。CPU使用量には日付計算を伴わない部分もあり、これは上記のクエリでは19875ミリ秒に近いもののようです。そして、変換にはさらにいくらかの量がかかるので、変換が2回ある場合は、その量はおよそ2回使い切られることになります。

さらに調べると Convert(, 112) に比べて Convert(, 101) クエリではさらに CPU が消費されます (これはより長い varchar を使うため）、2 回目の変換で date への最初の変換ほどはコストがかからないからです。 varchar への変換のようなコストはかかりませんが Convert(, 112) では、同じ 20000ms の CPU ベースコストに近くなります。

上記の分析に使用した CPU 時間について、それらの計算をご紹介します。

     method   round  single   base
-----------  ------  ------  -----
       date   21324   19891  18458
        int   23031   21453  19875
   datediff   23782   23218  22654
      float   36891   29312  21733
varchar-112  102984   64016  25048
varchar-101  123375   65609   7843

• 円形 への往復にかかる CPU 時間です。 datetime .

• シングル は代替データ型（time部分を削除する副作用があるもの）への1回の変換にかかるCPU時間です。

• ベース からの引き算の計算です。 single を呼び出すと、その差分が single - (round - single) . これは、そのデータ型との変換を想定した大まかな数値であり datetime への変換がどちらの方向でもほぼ同じであると仮定した概算値です。この仮定は完璧ではありませんが、値がすべて 20000 ミリ秒に近く、唯一の例外があることから、近いと思われます。

もう一つ興味深いのは、基本コストがほぼ単一の Convert(date) メソッドの最初の 4 バイトから整数日の部分を内部的に抽出することができるため、コストはほぼ 0 になるはずです。 datetime データ型の最初の 4 バイトから整数日の部分を内部的に抽出することができるためです)。

結論

ということで、どうやら単方向の varchar 変換方式は約1.8μsかかり、一方向の DateDiff メソッドには約 0.18 μs かかります。私のテストでは、25,920,000 行に対して合計 18458 ms という最も保守的な基本 CPU 時間をベースにしているので、23218 ms / 25920000 = 0.18 μs となります。見かけ上の 10 倍の改善は多くのように見えますが、数十万行を処理するまでは、率直に言ってかなり小さいです (617k 行 = 1 秒の節約)。

この小さな絶対的な改良を考慮しても、私の意見では DateAdd メソッドは、パフォーマンスと明快さの最高の組み合わせであるため、勝利します。マジックナンバーを必要とする答えは 0.50000004 を必要とする答えは、いつか誰かに噛み付かれるでしょうし（0が5つとか6つとか）、理解するのも難しいです。

追加メモ

時間があれば、私は 0.50000004 を '12:00:00.003' に変換して、その様子をご覧ください。変換されるのは、同じ datetime の値に変換され、私はこの方がずっと覚えやすいと思います。

興味のある方のために、上記のテストは@@Versionが以下を返すサーバで実行されました。

Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) Jul 9 2008 14:43:34 Copyright (c) 1988-2008 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)