February 1, 2012 at 5:13 pm
Hello all,
I've got a bit of a weird situation with the statistics generated by the software of one of our vendors (stored in mysql). The way that they aggregate minute statistics into hourly statistics is that
1:00:01 AM to 2:00:00 AM is rolled into the hour 2:00:00 AM. I need to be able to group their minute stats into hourly stats the way that they do, except in SQL server, there is no pre-existing Datetime ceiling function.
I never was able to find one that I really liked using Google, so I wrote my own using the datetime truncate functions I've found as a baseline.
I've come up with the following solution in a deterministic inline table valued function , which I think works well, but I'd like to ask the experts to pick at it to see if there's something I'm doing that irks them in some way, or possible a case where this function fails to produce the correct result.
--Included so you can see what I started with.
CREATE FUNCTION [dbo].[ufn_DateTimeFloor]
(
@Input datetime
)
RETURNS TABLE WITH SCHEMABINDING
AS
RETURN
(
-- Add the SELECT statement with parameter references here
SELECT (DATEADD(day, DATEDIFF(day, StaticDate, @Input), StaticDate)) AS Day_Floor
,(DATEADD(hour, DATEDIFF(hour, StaticDate, @Input), StaticDate)) AS Hour_Floor
,(DATEADD(minute, DATEDIFF(minute, StaticDate, @Input), StaticDate)) AS Minute_Floor
,(DATEADD(second, DATEDIFF(second, StaticDate, @Input), StaticDate)) AS Second_Floor
FROM (SELECT CONVERT([datetime], '1975-01-01T00:00:00.000', (126)) AS StaticDate) SubVars
)
CREATE FUNCTION [dbo].[ufn_DateTimeCeiling]
(
@Input datetime
)
RETURNS TABLE WITH SCHEMABINDING
AS
RETURN
(
-- Add the SELECT statement with parameter references here
SELECT (DATEADD(day, DATEDIFF(day, StaticDate, Adjusted_Day), StaticDate)) AS Day_Ceiling
,(DATEADD(hour, DATEDIFF(hour, StaticDate, Adjusted_Hour), StaticDate)) AS Hour_Ceiling
,(DATEADD(minute, DATEDIFF(minute, StaticDate, Adjusted_Minute), StaticDate)) AS Minute_Ceiling
,(DATEADD(second, DATEDIFF(second, StaticDate, Adjusted_Second), StaticDate)) AS Second_Ceiling
FROM (SELECT CONVERT([datetime], '1975-01-01T00:00:00.000', (126)) AS StaticDate
--datetime data type is accurate to 3.33 milliseconds, Per BOL.
,DATEADD(millisecond, -3.33, DATEADD(day, 1, @Input)) AS Adjusted_Day
,DATEADD(millisecond, -3.33, DATEADD(hour, 1, @Input)) AS Adjusted_Hour
,DATEADD(millisecond, -3.33, DATEADD(minute, 1, @Input)) AS Adjusted_Minute
,DATEADD(millisecond, -3.33, DATEADD(second, 1, @Input)) AS Adjusted_Second
) SubVars
)
iTVFs don't allow you to declare variables to be used inside the function, so I put the "variables" I'd like to use in the table FROM clause. This is another thing I'd like to have the experts pick at. Is this something I should avoid doing?
As far as performance goes, applying this TVF seems to have little if any affect on the performance of large queries (2.5 million rows). Aggregation using Day_Ceiling on same sample data was able to summarize my results in less than 2 seconds after a DBCC dropcleanbuffers and DBCC freeproccache.
Opinions? Suggestions? Questions?
February 2, 2012 at 4:59 am
You can cast the datetime to float and apply ceiling or floor to it.
select GETDATE(), CAST(getdate() as float), CAST(ceiling(cast(getdate() as float)) as datetime)
select GETDATE(), CAST(getdate() as float), CAST(floor(cast(getdate() as float)) as datetime)
February 2, 2012 at 7:23 am
There's a couple reasons why I don't go that route.
#1: It only works on rounding up or down to the nearest day. My method will work on Seconds, Minutes, Hours (And can be adapted to Weeks/Months/Years)
#2: It relies on the current internal storage method Microsoft uses for datetime types, which they say they won't guarantee to stay the same for future releases. CAST to float may work for all current versions of SQL, but that may change.
February 2, 2012 at 9:24 am
You solution is not bad but:
1. My method can be adapted as well, think about it.
2. Any implementation of datetime rely on internaly using a kind of float (and it ill not change)
3. Your method rely on the precision of the datetime and its not guarante, in fact what about others types like smalldatetime?
4. Its more easy to read.
declare @now0 datetime, @now1 datetime, @now2 datetime, @now3 datetime, @now4 datetime;
set @now0 = getdate();
set @now1 = CAST(floor(cast(@now0 as float)) as datetime);
set @now2 = DATEADD(HOUR, (datepart(hour,@now0)), @now1);
set @now3 = DATEADD(MINUTE, (datepart(MINUTE,@now0)), @now2);
set @now4 = DATEADD(SECOND, (datepart(SECOND,@now0)), @now3);
select @now0
select @now1, @now2, @now3, @now4;
select dateadd(day,1,@now1), dateadd(hour,1,@now2), dateadd(minute,1,@now3), dateadd(second,1,@now4)
5. If you dont need millisecond precision you can do it: (-4/3 to 4/3 milliseconds imprecise)
select CAST(floor(cast(GETDATE() as float)*(24*60))/(24*60) as datetime)--exemple for minutes
February 2, 2012 at 11:06 am
This is in a function. I specify that it is a datetime datatype. If someone passes a smalldatetime value to it, it gets converted to a datetime.
I'm not able to find the reference that stated that Microsoft cannot guarantee that the internal format datetime won't change.
I've put your method into a function, and tried it out. There seems to be a problem with the hour ceiling. (Not sure if Minute ceiling is affected.) Every three hours, instead of getting an hour value, I get 3.33 milliseconds less than the hour value. For example, instead of 2012-01-30 01:00:00, I get 2012-01-30 00:59:59.997
My code for your method below:
ALTER FUNCTION [dbo].[ufn_TimeCeiling_Cast]
(
@Input datetime
)
RETURNS TABLE WITH SCHEMABINDING
AS
RETURN
(
SELECT CAST(CEILING(CAST(@Input as float)) as datetime) AS Day_Ceiling
,CAST(CEILING(CAST(@Input as float)*(24))/(24) as datetime) AS Hour_Ceiling
,CAST(CEILING(CAST(@Input as float)*(24*60))/(24*60) as datetime) AS Minute_Ceiling
)
Did I mess something up there?
Also interesting to note: My method takes about 3% less CPU time in my testing. Certainly not a significant difference.
February 2, 2012 at 11:28 am
The cast to float trick is good for get rid of the hour:minute:second.millisecond part.
But as I said its imprecise if used direclty to hour/minute/second by multiply and division.
That's why I used datepart & dateadd to achieve it in the example.
That's due the integer part of the float represents the day( and months and year) and the mantissa holds the milliseconds (seconds, minutes and hour).
since multiply and divide the mantissa ill sometimes get imprecise as is imprecise the result.
I recalculated it, the imprecision range is from -10/3 to 0 milliseconds.
Its mean sometimes milliseconds ill be .997 instead of .000
You parameter is datetime, but when tested it for smalldatetime:
select * from dbo.ufn_DateTimeFloor(cast(GETDATE() as smalldatetime))
select * from dbo.ufn_DateTimeCeiling(cast(GETDATE() as smalldatetime))
It's not working propertly to minutes and seconds.
Try put the code bellow in a function:
declare @now0 datetime, @now1 datetime, @now2 datetime, @now3 datetime, @now4 datetime;
set @now0 = getdate();
set @now1 = CAST(floor(cast(@now0 as float)) as datetime);
set @now2 = DATEADD(HOUR, (datepart(hour,@now0)), @now1);
set @now3 = DATEADD(MINUTE, (datepart(MINUTE,@now0)), @now2);
set @now4 = DATEADD(SECOND, (datepart(SECOND,@now0)), @now3);
select @now0
select @now1, @now2, @now3, @now4;
select dateadd(day,1,@now1), dateadd(hour,1,@now2), dateadd(minute,1,@now3), dateadd(second,1,@now4)
February 2, 2012 at 12:36 pm
My testing shows no problems for smalldatetime in my function. Casting a datetime to a smalldatetime rounds the datetime to the nearest minute.
Datetime: 2012-02-02 13:31:52.413
CAST to smalldatetime: 2012-02-02 13:32:00
CEILING to Seconds from smalldatetime: 2012-02-02 13:32:00
CEILING to Minutes from smalldatetime: 2012-02-02 13:32:00
CEILING to Hours from smalldatetime: 2012-02-02 14:00:00.000
Datetime: 2012-02-02 13:32:02.413
CAST to smalldatetime:2012-02-02 13:32:00
CEILING to Seconds from smalldatetime: 2012-02-02 13:32:00
CEILING to Minutes from smalldatetime: 2012-02-02 13:32:00
CEILING to Hours from smalldatetime: 2012-02-02 14:00:00.000
Also, for performance reasons, I'm using an inline TVF. As a result, I cannot use any variables other than the input parameter. I've converted your method to a multi-statement UDF, but performance takes a definite hit.
CREATE FUNCTION ufn_TimeCeiling3
(
@Input datetime
)
RETURNS
@Table_Var TABLE
(
-- Add the column definitions for the TABLE variable here
Day_Ceiling smalldatetime,
Hour_Ceiling smalldatetime,
Minute_Ceiling smalldatetime,
Second_Ceiling datetime
)
AS
BEGIN
-- Fill the table variable with the rows for your result set
declare @now0 datetime, @now1 datetime, @now2 datetime, @now3 datetime, @now4 datetime;
set @now0 = @Input;
set @now1 = CAST(floor(cast(@now0 as float)) as datetime);
set @now2 = DATEADD(HOUR, (datepart(hour,@now0)), @now1);
set @now3 = DATEADD(MINUTE, (datepart(MINUTE,@now0)), @now2);
set @now4 = DATEADD(SECOND, (datepart(SECOND,@now0)), @now3);
INSERT INTO @Table_Var (Day_Ceiling, Hour_Ceiling, Minute_Ceiling, Second_Ceiling)
select dateadd(day,1,@now1) AS Day_Ceiling
, dateadd(hour,1,@now2) AS Hour_Ceiling
, dateadd(minute,1,@now3) AS Minute_Ceiling
, dateadd(second,1,@now4) AS Second_Ceiling
RETURN
END
The code I posted above has an issue though. If you enter a datetime value that is should not be changed by the CEILING function, it changes it anyway.
As an example: 2012-02-02 13:31:00.000
For seconds, it is changed to 2012-02-02 13:31:01.000
For minutes, it is changed to 2012-02-02 13:32:00.000
Both of these should return the original value of 2012-02-02 13:31:00.000
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply