db2 timeslice timestamp sql 15 minutes 30 minutes hourly

Db2 timeslice timestamp sql 15 minutes 30 minutes hourly code#

) ENGINE =MyISAM AUTO_INCREMENT = 16 DEFAULT CHARSET =latin1 `name ` varchar( 255) NOT NULL DEFAULT ' ', `hash ` varchar( 32) NOT NULL DEFAULT ' ', `id ` mediumint( 8) unsigned NOT NULL AUTO_INCREMENT, *!40101 SET character_set_client = utf8 */ *!40101 SET SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */ ĬREATE DATABASE /*!32312 IF NOT EXISTS */ `cacti ` /*!40100 DEFAULT CHARACTER SET latin1 */

Db2 timeslice timestamp sql 15 minutes 30 minutes hourly code#

You can modify following code to meet your need.- MySQL dump 10.13 Distrib 5.5.43, for debian-linux-gnu (x86_64) You probably want this in another table with some eventid to keep tack of which one is related to which operation. Shift_date | Hour | Status | Duration (min) WHEN h > DATEPART(hour, Start_timestamp)AND h < DATEPART(hour, End_timestamp) THEN 60 WHEN h = DATEPART(hour, End_timestamp) THEN DATEPART(minute, End_timestamp) create table register(Shift_date datetime, Status varchar(20), Start_timestamp datetime, End_Timestamp datetime) Basically it calculates minutes that fits each hour and finally, it calculates the total time of every group. I'd suggest to use an hours table to help with this job. ║ Shift_date ║ hour ║ Status ║ Duration ║ , dateadd(hour, 1 + n.n + datediff(hour, 0, t.Start_timestamp), 0) end_from_numbersįROM numbers n WHERE DATEDIFF(HOUR, t.Start_timestamp, t.End_Timestamp) >= n.n

SELECT dateadd(hour, n.n + datediff(hour, 0, t.Start_timestamp), 0) start_from_numbers , CASE WHEN t.End_timestamp > n.end_from_numbers THEN n.end_from_numbers ELSE t.End_timestamp END end_final , CASE WHEN t.Start_timestamp > n.start_from_numbers THEN t.Start_timestamp ELSE n.start_from_numbers END start_final , DATEDIFF(MINUTE, start_final, end_final) Duration

One approach which should work on 2008: - may need more rows here depending on how long the events can be From there, you just need to account for all of the different cases to figure out how many minutes for the status are relevant for each hour bucket. I'm doing this by joining to a numbers table. The basic idea here is to add rows for each row in the status table depending on how many hours it covers. Shift_date Hour Status Duration (minutes) Shift_date Status Start_timestamp End_Timestamp If the status was 'Operating' from 15:20 to 17:10, I want to see that it was 40 minutes on Operating for the 16th hour of the day, 60 minutes operating on the 17th hour and 10 minutes operating on the 18th hour of the day. But now I want to know for every hour what the status is. I have a table that registers the status for equipment.