Oracle, split a time duration row by one hour period
I have bulit a basic query, you can work around it and get what you want.
select greatest(Start_time, trunc(Start_time+(level-1)/24, 'hh24')),
least(End_time, trunc(Start_time+(level)/24, 'hh24'))
from log_table
connect by level <= floor((dt1-dt2)*24)+1;
Example at sqlfiddle:
http://sqlfiddle.com/#!4/82625/29
Oracle, split a time duration multiple rows by one hour period
You're really very close. Add a DISTINCT and an ORDER BY and I think you've got the results you were looking for:
SELECT DISTINCT AdmitDate,
PatientName,
Room,
greatest(start_time, trunc(start_time+(level-1)/24, 'hh24')) beginTime,
least(end_time, trunc(start_time+(level)/24, 'hh24')) endTime
from Utilization
connect by level <= floor((trunc(end_time, 'hh')-trunc(start_time, 'hh'))*24)+1
ORDER BY 1, 2, 3, 4, 5
This produces:
ADMITDATE PATIENTNAME ROOM BEGINTIME ENDTIME
2012-01-24 00:00:00 Patient1 RM1 2012-01-24 07:30:00 2012-01-24 08:00:00
2012-01-24 00:00:00 Patient1 RM1 2012-01-24 08:00:00 2012-01-24 08:32:00
2012-01-24 00:00:00 Patient2 RM1 2012-01-24 08:45:00 2012-01-24 09:00:00
2012-01-24 00:00:00 Patient2 RM1 2012-01-24 09:00:00 2012-01-24 09:13:00
SQLFiddle here
How do I split a duration of time into hourly intervals in Oracle SQL?
You can use a cte to recursively add an hour until the target is reached:
with cte(userid,date_capture,login_hour,logout_hour,target) as (
select userid,
date_capture,
login_hour,
login_hour + 1,
logout_hour
from table_name
union all
select userid,
date_capture,
logout_hour,
logout_hour + 1,
target
from cte
where logout_hour + 1 <= target
)
select userid,
date_capture,
login_hour,
logout_hour
from cte
order by userid,
date_capture,
login_hour;
How can i split a row of data at an hour level?
I'm not sure how happy 8i will be with this, but you can split the records up into hour (or partial hour) chunks with a hierarchical query like this:
select firstoccurrence, lastoccurrence, tally,
greatest(firstoccurrence,
trunc(firstoccurrence, 'HH24') + (level - 1) / 24) as new_start,
least(lastoccurrence,
trunc(firstoccurrence, 'HH24') + level / 24) as new_end
from t42
connect by firstoccurrence = prior firstoccurrence
and prior sys_guid() is not null
and trunc(firstoccurrence, 'HH24') + (level - 1) / 24
<= trunc(lastoccurrence, 'HH24');
... where t42 is a table populated as in your first image. That gives:
FIRSTOCCURRENCE LASTOCCURRENCE TALLY NEW_START NEW_END
------------------- ------------------- ----- ------------------- -------------------
2014-12-04 11:33:16 2014-12-04 11:33:36 1 2014-12-04 11:33:16 2014-12-04 11:33:36
2014-12-30 11:41:46 2014-12-30 16:23:08 7 2014-12-30 11:41:46 2014-12-30 12:00:00
2014-12-30 11:41:46 2014-12-30 16:23:08 7 2014-12-30 12:00:00 2014-12-30 13:00:00
2014-12-30 11:41:46 2014-12-30 16:23:08 7 2014-12-30 13:00:00 2014-12-30 14:00:00
2014-12-30 11:41:46 2014-12-30 16:23:08 7 2014-12-30 14:00:00 2014-12-30 15:00:00
2014-12-30 11:41:46 2014-12-30 16:23:08 7 2014-12-30 15:00:00 2014-12-30 16:00:00
2014-12-30 11:41:46 2014-12-30 16:23:08 7 2014-12-30 16:00:00 2014-12-30 16:23:08
You can then use that as a CTE, or as I don't think 8i knows about CTEs an inline view, and calculate the difference between each time and it's proportion against the original range, and divide the tally by that:
select firstoccurrence, lastoccurrence, tally, new_start, new_end,
to_number(to_char(new_start, 'HH24')) as new_hour,
tally / ((lastoccurrence - firstoccurrence) / (new_end - new_start)) as new_tally
from (
select firstoccurrence, lastoccurrence, tally,
greatest(firstoccurrence,
trunc(firstoccurrence, 'HH24') + (level - 1) / 24) as new_start,
least(lastoccurrence,
trunc(firstoccurrence, 'HH24') + level / 24) as new_end
from t42
connect by firstoccurrence = prior firstoccurrence
and prior sys_guid() is not null
and trunc(firstoccurrence, 'HH24') + (level - 1) / 24
<= trunc(lastoccurrence, 'HH24')
);
FIRSTOCCURRENCE LASTOCCURRENCE TALLY NEW_START NEW_END NEW_HOUR NEW_TALLY
------------------- ------------------- ----- ------------------- ------------------- -------- -----------
2014-12-04 11:33:16 2014-12-04 11:33:36 1 2014-12-04 11:33:16 2014-12-04 11:33:36 11 1.000000
2014-12-30 11:41:46 2014-12-30 16:23:08 7 2014-12-30 11:41:46 2014-12-30 12:00:00 11 .453619
2014-12-30 11:41:46 2014-12-30 16:23:08 7 2014-12-30 12:00:00 2014-12-30 13:00:00 12 1.492714
2014-12-30 11:41:46 2014-12-30 16:23:08 7 2014-12-30 13:00:00 2014-12-30 14:00:00 13 1.492714
2014-12-30 11:41:46 2014-12-30 16:23:08 7 2014-12-30 14:00:00 2014-12-30 15:00:00 14 1.492714
2014-12-30 11:41:46 2014-12-30 16:23:08 7 2014-12-30 15:00:00 2014-12-30 16:00:00 15 1.492714
2014-12-30 11:41:46 2014-12-30 16:23:08 7 2014-12-30 16:00:00 2014-12-30 16:23:08 16 .575524
SQL Fiddle demo; but that's 11gR2 so doesn't mean it'll work in 8i. I don't have such an ancient version to test on unfortunately.
If your table has an ID column then use that for the connect by prior clause - I've had to use firstoccurrence and hope it's unique. You may also need to use dbms_random.value instead of sys_guid() - an non-deterministic function will do though.
Split Time for each Hour in a day given start and end time
You can use generate series for calculating the interval in seconds,minutes,hours
CREATE TABLE userlogs
(
usernm varchar(300),
state varchar(300),
start_time timestamp,
end_time timestamp
);
INSERT INTO userlogs (usernm,state,start_time,end_time) VALUES ('user1', 'Work', '2022-08-15 11:00:38.000000 +00:00', '2022-08-15 14:11:03.000000 +00:00');
INSERT INTO userlogs (usernm,state,start_time,end_time) VALUES ('user1', 'Break', '2022-08-15 14:11:03.000000 +00:00', '2022-08-15 14:25:25.000000 +00:00');
INSERT INTO userlogs (usernm,state,start_time,end_time) VALUES ('user1', 'Work', '2022-08-15 14:25:25.000000 +00:00', '2022-08-15 15:09:10.000000 +00:00');
INSERT INTO userlogs (usernm,state,start_time,end_time) VALUES ('user1', 'Break', '2022-08-15 15:09:10.000000 +00:00', '2022-08-15 15:14:15.000000 +00:00');
INSERT INTO userlogs (usernm,state,start_time,end_time) VALUES ('user1', 'Work', '2022-08-15 15:14:15.000000 +00:00', '2022-08-15 18:07:50.000000 +00:00');
INSERT INTO userlogs (usernm,state,start_time,end_time) VALUES ('user1', 'Break', '2022-08-15 18:07:50.000000 +00:00', '2022-08-15 19:07:18.000000 +00:00');
INSERT INTO userlogs (usernm,state,start_time,end_time) VALUES ('user1', 'Work', '2022-08-15 19:07:18.000000 +00:00', '2022-08-15 19:25:31.000000 +00:00');
INSERT INTO userlogs (usernm,state,start_time,end_time) VALUES ('user1', 'Work', '2022-08-15 19:25:31.000000 +00:00', '2022-08-15 19:34:57.000000 +00:00');
INSERT INTO userlogs (usernm,state,start_time,end_time) VALUES ('user1', 'Work', '2022-08-15 19:34:57.000000 +00:00', '2022-08-15 20:10:57.000000 +00:00');
INSERT INTO userlogs (usernm,state,start_time,end_time) VALUES ('user1', 'Logged Out', '2022-08-15 20:10:57.000000 +00:00', null);
SELECT usernm, (count(*) * interval '1 min')::text AS work_interval
FROM (
SELECT usernm, generate_series (start_time, end_time - interval '1 min', interval '1 min') AS h
FROM userlogs where state='Work'
) sub
WHERE EXTRACT(ISODOW FROM h) < 6
AND h::time >= '20:00'
AND h::time <= '21:00'
GROUP BY 1
ORDER BY 1;
OUTPUT :
usernm work_interval
user1 00:10:00
Need Oracle SQL to split up date/time range by day
It is possible to do this in SQL. There are two tricks. The first is generating a series of numbers, which you can do with a CTE using connect.
The second is putting together the right logic to expand the dates, while keeping the right times for the beginning and end.
The following is an example:
with n as (
select level n
from dual connect by level <= 20
),
t as (
select 1 as id, to_date('01/01/2000 4', 'mm/dd/yyyy hh') as StartDate, to_date('01/03/2000 6', 'mm/dd/yyyy hh') as EndDate from dual union all
select 2 as id, to_date('01/04/2000 8', 'mm/dd/yyyy hh') as StartDate, to_date('01/04/2000 12', 'mm/dd/yyyy hh') as EndDate from dual union all
select 3 as id, to_date('01/05/2000', 'mm/dd/yyyy') as StartDate, to_date('01/06/2000', 'mm/dd/yyyy') as EndDate from dual
)
select t.id,
(case when n = 1 then StartDate
else trunc(StartDate + n - 1)
end) as StartDate,
(case when trunc(StartDate + n - 1) = trunc(enddate)
then enddate
else trunc(StartDate + n)
end)
from t join
n
on StartDate + n - 1 <= EndDate
order by id, StartDate
Here it is on SQLFiddle.
Oracle splitting date range into day and custom time intervals
You can use:
WITH SAMPLE (ID, startdate, enddate ) AS (
SELECT 1,
TO_DATE('2022-02-03 08:40', 'YYYY-MM-DD HH24:MI'),
TO_DATE('2022-02-04 10:07', 'YYYY-MM-DD HH24:MI')
FROM DUAL
UNION ALL
SELECT 2,
TO_DATE('2022-02-03 08:40', 'YYYY-MM-DD HH24:MI'),
TO_DATE('2022-02-04 02:07', 'YYYY-MM-DD HH24:MI')
FROM DUAL
UNION ALL
SELECT 3,
TO_DATE('2022-02-03 04:40', 'YYYY-MM-DD HH24:MI'),
TO_DATE('2022-02-04 02:07', 'YYYY-MM-DD HH24:MI')
FROM DUAL
)
SELECT ID,
o.type,
GREATEST(L.start_date + o.start_offset, s.startdate) AS startdate,
LEAST(L.start_date + o.end_offset, s.enddate) AS enddate
FROM SAMPLE s
CROSS JOIN LATERAL (
SELECT TRUNC(startdate - INTERVAL '6' HOUR)
+ INTERVAL '6' HOUR
+ LEVEL - 1 AS start_date
FROM DUAL
CONNECT BY
TRUNC(startdate - INTERVAL '6' HOUR)
+ INTERVAL '6' HOUR
+ LEVEL - 1
< ENDDATE
) L
CROSS JOIN (
SELECT 'DAY' AS type,
INTERVAL '0' HOUR AS start_offset,
INTERVAL '16' HOUR AS end_offset
FROM DUAL
UNION ALL
SELECT 'NIGHT' AS type,
INTERVAL '16' HOUR AS start_offset,
INTERVAL '24' HOUR AS end_offset
FROM DUAL
) o
WHERE L.start_date + o.start_offset < s.enddate
AND L.start_date + o.end_offset > s.startdate;
Which outputs:
ID TYPE STARTDATE ENDDATE 1 DAY 2022-02-03 08:40:00 2022-02-03 22:00:00 1 NIGHT 2022-02-03 22:00:00 2022-02-04 06:00:00 1 DAY 2022-02-04 06:00:00 2022-02-04 10:07:00 2 DAY 2022-02-03 08:40:00 2022-02-03 22:00:00 2 NIGHT 2022-02-03 22:00:00 2022-02-04 02:07:00 3 NIGHT 2022-02-03 04:40:00 2022-02-03 06:00:00 3 DAY 2022-02-03 06:00:00 2022-02-03 22:00:00 3 NIGHT 2022-02-03 22:00:00 2022-02-04 02:07:00 Calculate TIME Difference in ORACLE for same field in different row with output in HH:MM:SS format
Try below. Here
'1900-01-01'is dummy forTO_CHARto work correctly.WITH MAIN
AS (SELECT TO_DATE ('20200515 19:11:54', 'yyyymmdd hh24:mi:ss') DAT1,
TO_DATE ('20200515 18:11:54', 'yyyymmdd hh24:mi:ss') DAT2
FROM DUAL)
SELECT DAT1,
DAT2,
TO_CHAR (DATE '1900-01-01' + ABS (DAT1 - DAT2), 'HH24:MI:SS')
FROM MAINSame function with using
LAG.WITH MAIN
AS (SELECT TO_DATE ('20200515 19:11:54', 'yyyymmdd hh24:mi:ss') DAT1,
TO_DATE ('20200515 18:11:54', 'yyyymmdd hh24:mi:ss') DAT2
FROM DUAL
UNION ALL
SELECT TO_DATE ('20200514 19:12:54', 'yyyymmdd hh24:mi:ss') DAT1,
TO_DATE ('20200514 16:12:54', 'yyyymmdd hh24:mi:ss') DAT2
FROM DUAL
)
SELECT DAT1,
DAT2,
LAG(DAT1,1)OVER(ORDER BY DAT1) LAG,
TO_CHAR (DATE '1900-01-01' + ABS ( LAG(DAT1,1)OVER(ORDER BY DAT1) - DAT1), 'HH24:MI:SS')
FROM MAIN
Related Topics
How to Remove Part of the String in Oracle
Sqlite: How to Select "Most Recent Record for Each User" from Single Table with Composite Key
Converting Aggregate Operators from SQL to Relational Algebra
How to Call a User Defined Function to Use with Select, Group By, Order By
Continuing Inserts in Oracle When Exception Is Raised
Date Column Arithmetic in Postgresql Query
SQL Difference Between in and or in Where
SQL Server: Self-Reference Fk, Trigger Instead of on Delete Cascade
St_Hexagongrid Geom Vector to Find All Points
How to Find All Open/Active Connections in Db2 (8.X)
Sql: Count Distinct Values from One Column Based on Multiple Criteria in Other Columns
SQL Server Convert Datetime into Another Timezone
SQL Varchar(Max) VS Varchar(Fix)
Escaping Strings Containing Single Quotes in Powershell Ready for SQL Query
Conditions in Left Join (Outer Join) VS Inner Join
Access Query Counter Per Group
Insert Deleted Values into a Table Before Delete with a Delete Trigger