I tried this and it did not work as expected. I tried date_trunc which does not have the precision I need.Įx: between 15:30:00, 15:40:00 5 results. I have all timestamps in between a period, but I need to group it every 10 minutes and I have no idea how. WHERE ST_Within(earthnetworks_geometry_raios, poligono_geometry) I have this query below that returns me a result set: SELECT timestamp
Db2 timeslice timestamp sql 15 minutes 30 minutes hourly code#
You can modify following code to meet your need.I need to group by a period of time spliting up by 10 minutes. The hour must be less than or equal to 12 and cannot be zero except for 00:00 AM Db2 TIME type example. AM or PM can be in uppercase or lowercase and must be preceded by a single blank. You probably want this in another table with some eventid to keep tack of which one is related to which operation. The minute part can be omitted, for example, 1:00 PM is equivalent to 1 PM. 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.
0 kommentar(er)
