如何删除日期时间值的时间部分(SQL Server)?

2021-12-02 00:00:00 datetime sql-server date-conversion

这是我使用的:

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

我认为可能有更好更优雅的方式.

I'm thinking there may be a better and more elegant way.

要求:

  • 必须尽可能快(铸造越少越好).
  • 最终结果必须是 datetime 类型，而不是字符串.
  • It has to be as fast as possible (the less casting, the better).
  • The final result has to be a datetime type, not a string.

推荐答案

SQL Server 2008 及更高版本

在 SQL Server 2008 及更高版本中，当然最快的方法是 Convert(date, @date).如有必要，可以将其转换回 datetime 或 datetime2.

In SQL Server 2008 and up, of course the fastest way is Convert(date, @date). This can be cast back to a datetime or datetime2 if necessary.

SQL Server 2005 及更早版本中真正最好的是什么?

我看到关于在 SQL Server 中截断时间最快的说法不一致，有些人甚至说他们进行了测试，但我的经验有所不同.所以让我们做一些更严格的测试，让每个人都有脚本，这样如果我犯了任何错误，人们可以纠正我.

I've seen inconsistent claims about what's fastest for truncating the time from a date in SQL Server, and some people even said they did testing, but my experience has been different. So let's do some more stringent testing and let everyone have the script so if I make any mistakes people can correct me.

浮点数转换不准确

首先，我不会将 datetime 转换为 float，因为它不能正确转换.准确地执行时间删除操作可能会侥幸成功，但我认为使用它是个坏主意，因为它隐含地向开发人员传达了这是一个安全操作而它不是的信息.看一看:

First, I would stay away from converting datetime to float, because it does not convert correctly. You may get away with doing the time-removal thing accurately, but I think it's a bad idea to use it because it implicitly communicates to developers that this is a safe operation and it is not. Take a look:

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

这不是我们应该在我们的代码或在线示例中教给人们的东西.

This is not something we should be teaching people in our code or in our examples online.

而且，这甚至不是最快的方法！

Also, it is not even the fastest way!

证明——性能测试

如果您想自己执行一些测试以查看不同方法的实际叠加情况，那么您将需要此设置脚本来进一步运行测试:

If you want to perform some tests yourself to see how the different methods really do stack up, then you'll need this setup script to run the tests farther down:

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% 的增长，则需要的时间会比您先调整足够大的时间长.

Please note that this creates a 427.57 MB table in your database and will take something like 15-30 minutes to run. If your database is small and set to 10% growth it will take longer than if you size big enough first.

现在是实际的性能测试脚本.请注意，不将行返回给客户端是有目的的，因为这对于 2600 万行来说非常昂贵，并且会隐藏方法之间的性能差异.

Now for the actual performance testing script. Please note that it's purposeful to not return rows back to the client as this is crazy expensive on 26 million rows and would hide the performance differences between the methods.

绩效结果

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 子句中:

Some notes about this. First of all, if just performing a GROUP BY or a comparison, there's no need to convert back to datetime. So you can save some CPU by avoiding that, unless you need the final value for display purposes. You can even GROUP BY the unconverted value and put the conversion only in the SELECT clause:

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 转换几乎翻了一番?这显示了用于查询中日期计算的 CPU 部分.有部分 CPU 使用率不涉及日期计算，在上述查询中，这似乎接近 19875 毫秒.然后转换需要一些额外的金额，因此如果有两次转换，则该金额大约用完两次.

Also, see how the numeric conversions only take slightly more time to convert back to datetime, but the varchar conversion almost doubles? This reveals the portion of the CPU that is devoted to date calculation in the queries. There are parts of the CPU usage that don't involve date calculation, and this appears to be something close to 19875 ms in the above queries. Then the conversion takes some additional amount, so if there are two conversions, that amount is used up approximately twice.

更多的检查表明，与 Convert(, 112) 相比，Convert(, 101) 查询有一些额外的 CPU 开销(因为它使用了更长的 varchar?)，因为第二次转换回 date 并不像初始转换到 varchar 花费那么多，但是使用 Convert(,112) 它更接近相同的 20000 毫秒 CPU 基本成本.

More examination reveals that compared to Convert(, 112), the Convert(, 101) query has some additional CPU expense (since it uses a longer varchar?), because the second conversion back to date doesn't cost as much as the initial conversion to varchar, but with Convert(, 112) it is closer to the same 20000 ms CPU base cost.

以下是我用于上述分析的 CPU 时间计算:

Here are those calculations on the CPU time that I used for the above analysis:

     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

  • round 是返回到 datetime 的往返的 CPU 时间.

    • round is the CPU time for a round trip back to datetime.

      single 是单次转换为备用数据类型(具有删除时间部分的副作用的数据类型)的 CPU 时间.

      single is CPU time for a single conversion to the alternate data type (the one that has the side effect of removing the time portion).

      base 是从 single 中减去两次调用的差值的计算:single - (round - single).这是一个大概的数字，假设与该数据类型和 datetime 之间的转换在任一方向上大致相同.看来这个假设并不完美，但很接近，因为所有值都接近 20000 毫秒，只有一个例外.

      base is the calculation of subtracting from single the difference between the two invocations: single - (round - single). It's a ballpark figure that assumes the conversion to and from that data type and datetime is approximately the same in either direction. It appears this assumption is not perfect but is close because the values are all close to 20000 ms with only one exception.

      一个更有趣的事情是基本成本几乎等于单个 Convert(date) 方法(它必须几乎是 0 成本，因为服务器可以在内部提取整数天部分)datetime 数据类型的前四个字节).

      One more interesting thing is that the base cost is nearly equal to the single Convert(date) method (which has to be almost 0 cost, as the server can internally extract the integer day portion right out of the first four bytes of the datetime data type).

      结论

      那么看起来单向varchar转换方法大约需要1.8μs，而单向DateDiff方法大约需要0.18μs.在我对 18458 毫秒总计 25,920,000 行的测试中，我基于最保守的基本 CPU"时间，因此 23218 毫秒/25920000 = 0.18 微秒.明显的 10 倍改进似乎很多，但坦率地说，在您处理数十万行(617k 行 = 1 秒节省)之前，它的改进幅度很小.

      So what it looks like is that the single-direction varchar conversion method takes about 1.8 μs and the single-direction DateDiff method takes about 0.18 μs. I'm basing this on the most conservative "base CPU" time in my testing of 18458 ms total for 25,920,000 rows, so 23218 ms / 25920000 = 0.18 μs. The apparent 10x improvement seems like a lot, but it is frankly pretty small until you are dealing with hundreds of thousands of rows (617k rows = 1 second savings).

      即使考虑到这种微小的绝对改进，在我看来，DateAdd 方法还是胜出，因为它是性能和清晰度的最佳组合.需要 0.50000004 的幻数"的答案有一天会咬人(五个零或六个???)，而且更难理解.

      Even given this small absolute improvement, in my opinion, the DateAdd method wins because it is the best combination of performance and clarity. The answer that requires a "magic number" of 0.50000004 is going to bite someone some day (five zeroes or six???), plus it's harder to understand.

      附加说明

      等我有时间时，我会将 0.50000004 更改为 '12:00:00.003' 并看看效果如何.它被转换为相同的 datetime 值，我发现它更容易记住.

      When I get some time I'm going to change 0.50000004 to '12:00:00.003' and see how it does. It is converted to the same datetime value and I find it much easier to remember.

      对于那些感兴趣的人，上述测试是在@@Version 返回以下内容的服务器上运行的:

      For those interested, the above tests were run on a server where @@Version returns the following:

      Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) 2008 年 7 月 9 日 14:43:34 版权所有 (c) 1988-2008 Microsoft Corporation Standard Edition on Windows NT 5.2(Build 3790:Service Pack 2)

      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)

相关文章