1 hour corresponds to the number of quarter hours

Standardization of time information in SQL

Everything in our projects revolves around time. Mostly it is a matter of comparing and analyzing the data on a daily, monthly or yearly level. Some time ago we had a slightly more specific requirement in a project that we would like to report on today.

At that time it was about the display of measurement data from a special application that checks the availability of software systems and servers within a company. This special application checks at least once within 15 minutes whether the respective system can be addressed. For example, it is checked whether a SAP login can be carried out successfully or whether a certain page is returned when a web application is called up.

We receive information on the unsuccessful measurements via an interface. In other words: whenever a system cannot be reached during the availability check, we receive a data record. As a time specification, we receive a field of the DateTime type, in which the point in time at which the availability check took place is exactly to the millisecond.

Now we have the data accurate to the millisecond, but we don't want that at all. In the end, the requirement was that we should count the number of time units of 15 minutes in which a system failure was measured. To make matters worse, the measurements are not made every 15 minutes, but only at least every 15 minutes. So it could also happen that more than one data set was delivered for a system within 15 minutes. Under no circumstances should this be counted twice, but must be booked as one measurement. For example, the following data could arise for a system that cannot be reached for an entire hour:

SystemIDOffline_TimeStamp
SAP_4711_081501.01.2014 13:04:08.963
SAP_4711_081501.01.2014 13:16:37.283
SAP_4711_081501.01.2014 13:29:06.349
SAP_4711_081501.01.2014 13:38:49.721
SAP_4711_081501.01.2014 13:51:03.392

 

In the example you can see that the second and third measurements are within the same 15 minutes - namely between 13:15 and 13:30. It is therefore clear that we have to find a way to first round up the measurement data to 15-minute steps and then group them by SystemID and time. This then gives you exactly one data record per quarter hour and system.

In order to be able to round time information with standard SQL tools, you first have to have something that you can round off. The common rounding functions ROUND (), FLOOR () and CEILING () only work with numeric values. So what could be more natural than a type conversion from DateTime to Float? But what do you have then?

The type conversion of a date value into a numeric value follows very simple rules in SQL:

  1. The integer portion of the numeric value corresponds to the number of days that have passed since 01/01/1900. For example, that would be the value 41,638 for 01/01/2014. This can be easily recalculated with the DateDiff () function.
  2. The portion after the decimal point corresponds to the portion of the current day measured from 00: 00: 00.000 to 23: 59: 59.999. For example, 0.5 corresponds to 12:00 noon. 0.25 corresponds to 6:00 a.m. A chain calculation can be used to calculate the hours, minutes and seconds. An example: Assuming we have given the following decimal places: 0.913224537
    • This number multiplied by the number of hours in a day gives:
      21.9173888888326 - so it's after 9 p.m.
    • If you take the decimal places again and multiply them by the number of minutes in an hour, you get:
      55.0433333299588 - so 55 minutes of the current hour have passed.
    • Again the decimal places of this number, multiplied by the number of seconds in a minute:
      2.59999979753047 - so: around 2.6 seconds.
    • This results in the time, which is represented by the decimal places given above: It is 21: 55: 02.600.

If you want to round up or down to the exact hour, the converted DateTime value must be multiplied by 24, then rounded and then divided by 24. To round up this looks like B. looks like this:

DECLARE @dt DATETIME, @f FLOAT, @rndUpHour FLOAT, @rndDwnHour FLOAT SET @dt = '2014-01-01 21: 55: 02.600' - set time SET @f = convert (FLOAT, @dt) - conversion in float -> 41638,913224537 - please round up ... SET @rndUpHour = CEILING (@f * 24) / 24 - rounding is also possible ... SET @rndDwnHour = FLOOR (@f * 24) / 24 - - Result: SELECT @dt as StartZeit, CONVERT (SMALLDATETIME, @rndDwnHour) as hour rounded down, CONVERT (SMALLDATETIME, @rndUpHour) as hour rounded up

Here is the result:

Figure 1: Result of rounding up or down to full hours

If we want to round up or down exactly to the minute, the numerical value that results from the type conversion of DateTime must be multiplied by 24 and then by 60, rounded off and then again divided by 24 and by 60.

For rounding up to full quarter-hours, you only have to multiply by 4 instead of 60, since the hour should only be divided into four equal parts. In general, one could also say that we specify a grid of 15 minutes and divide the 60 minutes of an hour by this grid. So we come back to 60/15 = 4. But with this a nice general approach can be realized, which could then look like this:

DECLARE @dt DATETIME, @f FLOAT, @pattern INT, @factor FLOAT, @rndUpPattern FLOAT, @rndDwnPattern FLOAT SET @dt = '2014-01-01 21: 55: 02.600' - set time SET @f = CONVERT ( FLOAT, @dt) - Conversion into float -> 41638,913224537 SET @pattern = 15 - Specification of the "target" time frame (minutes) to which - should be rounded. - But only works reliably for integer divisors of the number 60! So: 1,2,3,4,5,6,10, etc. SET @factor = 24 * 60 / @pattern - rounding up please ... SET @rndUpPattern = CEILING (@f * @factor) / @factor - rounding off is also possible ... SET @rndDwnPattern = FLOOR (@f * @ factor) / @factor - Result: SELECT @dt as StartZeit, CONVERT (SMALLDATETIME, @rndDwnPattern) as RasterAbundunden, CONVERT (SMALLDATETIME, @rndUpPattern) as RasterUrround

And here the result:

Figure 2: Result of rounding up / down to the nearest 1/4 hour

For the conversion back to a date value, the use of the SmallDateTime format has proven to be practical, as it is always rounded to full minutes and rounding errors can thus be eliminated.

Friday March 28, 2014