Killing Time – or removing the time element from a datetime
Over the course of time, we all collect scripts and routines for helping us do our job. Recently, I ran across two routines that are removing the time element from a datetime data type that I hadn’t seen before. A quick initial test showed that these seem to perform pretty well… but how well do they perform compared to the other ways of doing this? It’s time to find out.
I have blogged before about working with dates, where I checked out how the DATETIMEFROMPARTS function performs compared to the DATEADD/DATEDIFF functions. Today, we’ll compare 3 other methods to the DATEADD/DATEDIFF method:
- Taking advantage of the fact that a datetime datatype is stored as a float, with the decimal being fractions of a day and the whole numbers being days, we will convert the datetime to float, taking the floor (just the whole numbers), and converting back to datetime.
- Using the DATEADD/DATEDIFF routine.
- Converting the datetime to DATE and back to datetime.
- Converting the datetime to varbinary (which returns just the time), and subtracting that from the datetime value.
Update: added tests for converting the datetime to integer / bigint and back to datetime after Eirikur’s comment below.
While there are other ways of stripping the time (DATETIMEFROMPARTS, string manipulation), those ways are already known as poorly performing. Let’s just concentrate on these four.
Four Six methods of removing the time element from a datetime
First off, we need to ensure that we have the calculations down correctly. This query uses the four methods to strip the time element from the datetime value:
|
1 2 3 4 5 6 7 8 9 |
DECLARE @Date DATETIME; SET @Date = GETDATE(); SELECT 'Original Date', @Date UNION ALL SELECT 'Floor of Float', CAST(FLOOR(CAST(@Date AS FLOAT)) AS DATETIME) AS [Floor of Float] UNION ALL SELECT 'Date functions', DATEADD(DAY, DATEDIFF(DAY, '2000-01-01', @Date), '2000-01-01') AS [Date Math] UNION ALL SELECT 'Convert to DATE', CONVERT(DATETIME, CONVERT(DATE, @Date)) AS [Convert to DATE] UNION ALL SELECT 'Varbinary Math', @Date - CONVERT(DATETIME, CONVERT(VARBINARY(4), @Date)) AS [Varbinary Math] UNION ALL SELECT 'Convert to INTEGER', CONVERT(DATETIME, CONVERT(INTEGER, @Date)) AS [Convert to INTEGER] UNION ALL SELECT 'Convert to BIGINT', CONVERT(DATETIME, CONVERT(BIGINT, @Date)) AS [Convert to BIGINT]; |
This returns the following result set, in which we can see that all the calculations are removing the time element:
The one million row performance test
Now, it’s time to test how these methods perform. As you may have gathered from my earlier tests, a true test requires a lot of data. In this test, I will create a one million row dataset of dates, and then strip the time from them. The results are sent into a temporary table so that the test is not affected by the display time of the millions of rows. I’ll store off the start/end time for processing that method. The test runs over 500 iterations for each method, and at the end I’ll get the MIN, MAX and AVG elapsed time (in microseconds) for each method.
The test that I’ll use is:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 |
IF OBJECT_ID('tempdb.dbo.#Timings') IS NOT NULL DROP TABLE #Timings; CREATE TABLE #Timings ( Method VARCHAR(25), StartTime DATETIME2(7), EndTime DATETIME2(7), ElapsedTime AS DATEDIFF(MICROSECOND, StartTime, EndTime) ); CREATE INDEX IX1 ON #Timings (Method, ElapsedTime); GO IF OBJECT_ID('tempdb.dbo.#bitbucket1') IS NOT NULL DROP TABLE #bitbucket1; IF OBJECT_ID('tempdb.dbo.#bitbucket2') IS NOT NULL DROP TABLE #bitbucket2; IF OBJECT_ID('tempdb.dbo.#bitbucket3') IS NOT NULL DROP TABLE #bitbucket3; IF OBJECT_ID('tempdb.dbo.#bitbucket4') IS NOT NULL DROP TABLE #bitbucket4; IF OBJECT_ID('tempdb.dbo.#bitbucket5') IS NOT NULL DROP TABLE #bitbucket5; IF OBJECT_ID('tempdb.dbo.#bitbucket6') IS NOT NULL DROP TABLE #bitbucket6; DECLARE @StartTime DATETIME2(7); SET @StartTime = SYSDATETIME(); WITH Tens (N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1), Hundreds(N) AS (SELECT 1 FROM Tens t1, Tens t2), Millions(N) AS (SELECT 1 FROM Hundreds t1, Hundreds t2, Hundreds t3), Tally (N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM Millions), SubQuery (ReleaseDate) AS (SELECT DATEADD(MINUTE, N, GETDATE()) FROM Tally) SELECT CAST(FLOOR(CAST(ReleaseDate AS FLOAT)) AS DATETIME) ReleaseDate INTO #bitbucket1 FROM SubQuery; INSERT INTO #Timings (Method, StartTime, EndTime) VALUES ('Floor of Float', @StartTime, SYSDATETIME() ); SET @StartTime = SYSDATETIME(); WITH Tens (N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1), Hundreds(N) AS (SELECT 1 FROM Tens t1, Tens t2), Millions(N) AS (SELECT 1 FROM Hundreds t1, Hundreds t2, Hundreds t3), Tally (N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM Millions), SubQuery (ReleaseDate) AS (SELECT DATEADD(MINUTE, N, GETDATE()) FROM Tally) SELECT DATEADD(DAY, DATEDIFF(DAY, '2000-01-01', ReleaseDate), '2000-01-01') AS ReleaseDate INTO #bitbucket2 FROM SubQuery; INSERT INTO #Timings (Method, StartTime, EndTime) VALUES ('Date Math', @StartTime, SYSDATETIME() ); SET @StartTime = SYSDATETIME(); WITH Tens (N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1), Hundreds(N) AS (SELECT 1 FROM Tens t1, Tens t2), Millions(N) AS (SELECT 1 FROM Hundreds t1, Hundreds t2, Hundreds t3), Tally (N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM Millions), SubQuery (ReleaseDate) AS (SELECT DATEADD(MINUTE, N, GETDATE()) FROM Tally) SELECT CONVERT(DATE, ReleaseDate) AS ReleaseDate INTO #bitbucket3 FROM SubQuery; INSERT INTO #Timings (Method, StartTime, EndTime) VALUES ('Convert to DATE', @StartTime, SYSDATETIME() ); SET @StartTime = SYSDATETIME(); WITH Tens (N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1), Hundreds(N) AS (SELECT 1 FROM Tens t1, Tens t2), Millions(N) AS (SELECT 1 FROM Hundreds t1, Hundreds t2, Hundreds t3), Tally (N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM Millions), SubQuery (ReleaseDate) AS (SELECT DATEADD(MINUTE, N, GETDATE()) FROM Tally) SELECT ReleaseDate - CONVERT(DATETIME, CONVERT(VARBINARY(4),ReleaseDate)) AS ReleaseDate INTO #bitbucket4 FROM SubQuery; INSERT INTO #Timings (Method, StartTime, EndTime) VALUES ('Varbinary Math', @StartTime, SYSDATETIME() ); SET @StartTime = SYSDATETIME(); WITH Tens (N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1), Hundreds(N) AS (SELECT 1 FROM Tens t1, Tens t2), Millions(N) AS (SELECT 1 FROM Hundreds t1, Hundreds t2, Hundreds t3), Tally (N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM Millions), SubQuery (ReleaseDate) AS (SELECT DATEADD(MINUTE, N, GETDATE()) FROM Tally) SELECT CONVERT(DATETIME, CONVERT(INTEGER,ReleaseDate)) AS ReleaseDate INTO #bitbucket5 FROM SubQuery; INSERT INTO #Timings (Method, StartTime, EndTime) VALUES ('Convert to INTEGER', @StartTime, SYSDATETIME() ); SET @StartTime = SYSDATETIME(); WITH Tens (N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1), Hundreds(N) AS (SELECT 1 FROM Tens t1, Tens t2), Millions(N) AS (SELECT 1 FROM Hundreds t1, Hundreds t2, Hundreds t3), Tally (N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM Millions), SubQuery (ReleaseDate) AS (SELECT DATEADD(MINUTE, N, GETDATE()) FROM Tally) SELECT CONVERT(DATETIME, CONVERT(BIGINT,ReleaseDate)) AS ReleaseDate INTO #bitbucket6 FROM SubQuery; INSERT INTO #Timings (Method, StartTime, EndTime) VALUES ('Convert to BIGINT', @StartTime, SYSDATETIME() ); GO 500 SELECT Method, COUNT(*) AS [Execution Count], MIN(ElapsedTime) AS [Elapsed Minimum], MAX(ElapsedTime) AS [Elapsed Maximum], AVG(ElapsedTime) AS [Elapsed Average] FROM #Timings GROUP BY Method ORDER BY [Elapsed Average]; |
Over 500 iterations, the results are:
This result is sorted by the average time. The first thing to note is that all four of these methods are very fast. There is only 63 milli-seconds difference between the best and worse methods (on average).
In Summary
SQL 2008 introduced the DATE data type. I have tried to not use the convert to DATE method because the DATE datatype wasn’t in all versions. It sucks to still be on SQL 2005. Since this method has the best times (on average), I think I’ll be using this in the future. But what was really surprising was that the two methods that I hadn’t seen before performed better than I expected, and better than the method that I had used.
Update
After Eirikur’s comment below, I added a test (and updated the code above) for converting to integer and to bigint, and reran the test. Updated results:
The conversion to DATE is still the fastest, but converting to bigint and back to datetime is now the fastest way in 2005.
