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:
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:
- 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.
- 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.
- This number multiplied by the number of hours in a day gives:
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.
- What does Shafak Allah curse mean
- What does esterified form mean?
- What does self-righteous indignation mean
- Who were the sons of Freedom Apex
- What is rose hip tea
- What causes black spots on mirrors
- What is self-leveling power steering pump
- How attractive are you photo test
- What Eats Ragworms Scientific Names
- Donald Duck Dunn bass tone how to
- What kind of feeling is it, texts without students
- 30 Rathowen Lisnaskea Emmetts
- Mini camera md80 instructions on how to bind
- Which character do you have from the winning quiz?
- Alryk wait what pelto
- How to find the partition ID
- Entidad 3d descargar whatsapp
- Using the online source code visualizer
- Jason Westerfield, how the witchcraft works
- Erasable notepad for use
- Who Owns the Ford Calgary Advantage
- Demasiada carne descargar whatsapp
- Jacky Kok Chow hinges
- Free us ipad vpn what is it