SQL Server , split a time duration row by 24 hour period
Try this query:
WITH TAB1 (ID,FROMDATE,TODATE1,TODATE) AS
(SELECT ID,
FROMDATE,
DATEADD(SECOND, 24*60*60 - 1, CAST(CAST(FROMDATE AS DATE) AS DATETIME)) TODATE1,
TODATE
FROM TABLE1
UNION ALL
SELECT
ID,
DATEADD(HOUR, 24, CAST(CAST(TODATE1 AS DATE) AS DATETIME)) FROMDATE,
DATEADD(SECOND, 2*24*60*60-1, CAST(CAST(TODATE1 AS DATE) AS DATETIME)) TODATE1,
TODATE
FROM TAB1 WHERE CAST(TODATE1 AS DATE) < CAST(TODATE AS DATE)
),
TAB2 AS
(SELECT ID,FROMDATE,
CASE WHEN TODATE1 > TODATE THEN TODATE ELSE TODATE1 END AS TODATE
FROM TAB1)
SELECT TAB2.*,
DATEPART(hh, TODATE) - DATEPART(hh, FROMDATE) [DateDiff(HH)] FROM TAB2;
SQL Fiddle
Redshift: splitting a time duration row by 24 hour period
Redshift has one painful "feature". It is a pain to generate a derived table. Let me assume that you have a table big enough to generate a tally or numbers table.
Then, the rest is date arithmetic . . . but a bit complicated. I don't have Redshift on hand -- and it has a weird combination of date syntax from SQL Server and Postgres
with numbers as (
select row_number() over () - 1 as n
from t
)
select t.id,
greatest(date_trunc('day', t.fromdate) + n.n * interval '1 day', t.fromdate) as fromdate,
least(date_trunc('day', t.fromdate) + (n.n + 1) * interval '1 day', t.todate) as todate
datediff(hour,
greatest(date_trunc('day', t.fromdate) + n.n * interval '1 day', t.fromdate),
least(date_trunc('day', t.fromdate) + (n.n + 1) * interval '1 day', todate)
) as hours
from t join
numbers n
on todate > date_trunc('day', t.fromdate) + n.n * interval '1 day';
Here is a db<>fiddle using Postgres that illustrates the main ideas behind this.
SQL - Split total time by time intervals
Old school approach, but I would create a second table:
CREATE TABLE SplitTimes (
SplitStart time not null,
SplitEnd time not null,
primary key (SplitStart, SplitEnd)
)
And populate it:
INSERT INTO SplitTimes (SplitStart, SplitEnd) VALUES
('0:00', '0:30'),
('0:30', '1:00'),
('1:00', '1:30'),
('1:30', '2:00'),
('2:00', '2:30'),
('2:30', '3:00'),
('3:00', '3:30'),
('3:30', '4:00'),
('4:00', '4:30'),
('4:30', '5:00'),
('5:00', '5:30'),
('5:30', '6:00'),
('6:00', '6:30'),
('6:30', '7:00'),
('7:00', '7:30'),
('7:30', '8:00'),
('8:00', '8:30'),
('8:30', '9:00'),
('9:00', '9:30'),
('9:30', '10:00'),
('10:00', '10:30'),
('10:30', '11:00'),
('11:00', '11:30'),
('11:30', '12:00'),
('12:00', '12:30'),
('12:30', '13:00'),
('13:00', '13:30'),
('13:30', '14:00'),
('14:00', '14:30'),
('14:30', '15:00'),
('15:00', '15:30'),
('15:30', '16:00'),
('16:00', '16:30'),
('16:30', '17:00'),
('17:00', '17:30'),
('17:30', '18:00'),
('18:00', '18:30'),
('18:30', '19:00'),
('19:00', '19:30'),
('19:30', '20:00'),
('20:00', '20:30'),
('20:30', '21:00'),
('21:00', '21:30'),
('21:30', '22:00'),
('22:00', '22:30'),
('22:30', '23:00'),
('23:00', '23:30'),
('23:30', '23:59:59.9999999');
Now I can run this:
SELECT e.UserID
,case when e.StatusStart >= t.SplitStart then e.StatusStart else t.SplitStart end as SplitStatusStart
,case when e.StatusEnd <= t.SplitEnd then e.StatusEnd else t.SplitEnd end as SplitStatusEnd
,e.StatusKey
,datediff(second, case when e.StatusStart >= t.SplitStart then e.StatusStart else t.SplitStart end,
case when e.StatusEnd <= t.SplitEnd then e.StatusEnd else t.SplitEnd end) SplitStatusDuration
FROM EntryTable e
INNER JOIN SplitTimes t
ON e.StatusStart <= t.SplitEnd
AND e.StatusEnd >= t.SplitStart
It can be made to work with datetimes instead of times and it can be made to work across midnight. It's just extra fiddling to strip the time from the date and add the other one back on.
This has the advantage of not being a recursive CTE, which may perform better on a large table.
Snowflake server, split duration by hour?
For this type of project it helps to have separate date & interval tables.
here is the script to achieve what you need.
create a calendar table with whatever date range you need.
I start with '1970-01-01' the unix epoch, as I keep a calendar table handy for situations like this.
create or replace table calendar(calendar_date DATE)
insert into calendar(calendar_date)
select
dateadd(d,rid2,to_timestamp_ntz('1970-01-01')) as calendar_date
from
(
select 0 as rid2 union
select row_number() over (order by null) as rid2
from table (generator(rowcount => 100000))
) t
where dateadd(d,rid2,to_timestamp_ntz('1970-01-01')) < '2030-01-01'
order by 1 asc;
Next create a time / interval table.
The interval table. In your example your interval duration is 1 hour. So the following was created.
create or replace table interval(id integer, interval_start time);
insert into interval (id,interval_start)
select
id,
to_time(dateadd(hour,id,to_timestamp_ntz('1970-01-01')))
from
(
select 0 as id union
select row_number() over (order by null) as id
from table (generator(rowcount => 23))
) t;
Next i created a table with your sample data, as well as a couple other values so that the calculations can be validated across different scenarios.
create or replace table example1(id varchar(10), DURATION_START datetime, DURATION_END datetime);
-- drop table example1
truncate table example1;
--
insert into example1 values('0abc23','2019-06-29 00:08:00.000','2019-06-29 09:18:00.000');
insert into example1 values('0abc24','2019-06-28 11:07:45.000','2019-06-28 12:08:45.000');
insert into example1 values('0abc25','2019-06-28 01:00:00.000','2019-06-29 02:15:00.000');
insert into example1 values('0abc26','2019-06-28 00:08:00.000','2019-06-29 15:18:00.000');
Given that everything is setup, the query below will give you the result you need.
select
f.id
,f.DURATION_START
,f.DURATION_END
,f.start_time_HOUR_START
,f.end_time_HOUR_START
,q.CALENDAR_DATE
,q.HOUR_START
,q.HOUR_END
,case
-- when starts during interval and ends after interval
when f.DURATION_START >= q.HOUR_START and f.DURATION_END >= dateadd(hour, 1, q.HOUR_START)
then datediff(s, f.DURATION_START, dateadd(hour, 1, q.HOUR_START))
-- when starts during interval and ends during interval
when f.DURATION_START >= q.HOUR_START and f.DURATION_END <= dateadd(hour, 1, q.HOUR_START)
then datediff(s, f.DURATION_START, f.DURATION_END)
-- when starts before interval and ends during interval
when f.DURATION_START <= q.HOUR_START and f.DURATION_END <= dateadd(hour, 1, q.HOUR_START)
then datediff(s, q.HOUR_START, f.DURATION_END)
-- entire interval , starts before, and ends after
when
f.DURATION_START <= q.HOUR_START and f.DURATION_END >= dateadd(hour, 1, q.HOUR_START)
then datediff(s, q.HOUR_START, dateadd(hour, 1, q.HOUR_START))
else 0 end as seconds_elapsed
from (
select *
, to_timestamp(
dateadd(s, datediff(s, '1970-01-01', DURATION_START) - (datediff(s, '1970-01-01', DURATION_START) % 3600),
'1970-01-01')) as start_time_HOUR_START
, to_timestamp(
dateadd(s, datediff(s, '1970-01-01', DURATION_END) - (datediff(s, '1970-01-01', DURATION_END) % 3600),
'1970-01-01')) as end_time_HOUR_START
from example1
) f
inner join
(
select
distinct
q1.calendar_date
-- , t2.rid2
, dateadd(hour, t2.id, to_timestamp(q1.calendar_date)) as HOUR_START
, dateadd(hour, t2.id + 1, to_timestamp(q1.calendar_date)) as HOUR_END
from (
select calendar_date
from calendar
where calendar_date between (select to_date(min(DURATION_START)) from example1) and (select to_date(max(DURATION_END)) from example1)
) q1
cross join
interval as t2
-- order by HOUR_START
) q on q.HOUR_START between f.start_time_HOUR_START and f.end_time_HOUR_START
ORDER BY f.id
, f.DURATION_START
, f.DURATION_END
, q.CALENDAR_DATE
, q.HOUR_START
;
Sample output below. run the script to get the final result:
+--------+-------------------------+-------------------------+-------------------------+-------------------------+---------------+-------------------------+-------------------------+-----------------+
| ID | DURATION_START | DURATION_END | START_TIME_HOUR_START | END_TIME_HOUR_START | CALENDAR_DATE | HOUR_START | HOUR_END | SECONDS_ELAPSED |
|--------+-------------------------+-------------------------+-------------------------+-------------------------+---------------+-------------------------+-------------------------+-----------------|
| 0abc23 | 2019-06-29 00:08:00.000 | 2019-06-29 09:18:00.000 | 2019-06-29 00:00:00.000 | 2019-06-29 09:00:00.000 | 2019-06-29 | 2019-06-29 00:00:00.000 | 2019-06-29 01:00:00.000 | 3120 |
| 0abc23 | 2019-06-29 00:08:00.000 | 2019-06-29 09:18:00.000 | 2019-06-29 00:00:00.000 | 2019-06-29 09:00:00.000 | 2019-06-29 | 2019-06-29 01:00:00.000 | 2019-06-29 02:00:00.000 | 3600 |
| 0abc23 | 2019-06-29 00:08:00.000 | 2019-06-29 09:18:00.000 | 2019-06-29 00:00:00.000 | 2019-06-29 09:00:00.000 | 2019-06-29 | 2019-06-29 02:00:00.000 | 2019-06-29 03:00:00.000 | 3600 |
| 0abc23 | 2019-06-29 00:08:00.000 | 2019-06-29 09:18:00.000 | 2019-06-29 00:00:00.000 | 2019-06-29 09:00:00.000 | 2019-06-29 | 2019-06-29 03:00:00.000 | 2019-06-29 04:00:00.000 | 3600 |
| 0abc23 | 2019-06-29 00:08:00.000 | 2019-06-29 09:18:00.000 | 2019-06-29 00:00:00.000 | 2019-06-29 09:00:00.000 | 2019-06-29 | 2019-06-29 04:00:00.000 | 2019-06-29 05:00:00.000 | 3600 |
| 0abc23 | 2019-06-29 00:08:00.000 | 2019-06-29 09:18:00.000 | 2019-06-29 00:00:00.000 | 2019-06-29 09:00:00.000 | 2019-06-29 | 2019-06-29 05:00:00.000 | 2019-06-29 06:00:00.000 | 3600 |
| 0abc23 | 2019-06-29 00:08:00.000 | 2019-06-29 09:18:00.000 | 2019-06-29 00:00:00.000 | 2019-06-29 09:00:00.000 | 2019-06-29 | 2019-06-29 06:00:00.000 | 2019-06-29 07:00:00.000 | 3600 |
| 0abc23 | 2019-06-29 00:08:00.000 | 2019-06-29 09:18:00.000 | 2019-06-29 00:00:00.000 | 2019-06-29 09:00:00.000 | 2019-06-29 | 2019-06-29 07:00:00.000 | 2019-06-29 08:00:00.000 | 3600 |
| 0abc23 | 2019-06-29 00:08:00.000 | 2019-06-29 09:18:00.000 | 2019-06-29 00:00:00.000 | 2019-06-29 09:00:00.000 | 2019-06-29 | 2019-06-29 08:00:00.000 | 2019-06-29 09:00:00.000 | 3600 |
| 0abc23 | 2019-06-29 00:08:00.000 | 2019-06-29 09:18:00.000 | 2019-06-29 00:00:00.000 | 2019-06-29 09:00:00.000 | 2019-06-29 | 2019-06-29 09:00:00.000 | 2019-06-29 10:00:00.000 | 1080 |
| 0abc24 | 2019-06-28 11:07:45.000 | 2019-06-28 12:08:45.000 | 2019-06-28 11:00:00.000 | 2019-06-28 12:00:00.000 | 2019-06-28 | 2019-06-28 11:00:00.000 | 2019-06-28 12:00:00.000 | 3135 |
| 0abc24 | 2019-06-28 11:07:45.000 | 2019-06-28 12:08:45.000 | 2019-06-28 11:00:00.000 | 2019-06-28 12:00:00.000 | 2019-06-28 | 2019-06-28 12:00:00.000 | 2019-06-28 13:00:00.000 | 525 |
For a link to the code click
How to split start time and end time from different days into a daily table?
;WITH calcs AS (
-- 24 hours in seconds, minus 1 second for 23:59:59
SELECT CAST(24 * 60 * 60 - 1 AS INT) AS [t_const_1]
, CAST(2 * 24 * 60 * 60 - 1 AS INT) AS [t_const_2]
)
, timeCTE AS (
SELECT T.ID
, T.START_TIME AS [start_dt]
-- Find the "start" of the day (00:00:00) and add our constant
, DATEADD(SECOND, calcs.[t_const_1], CAST(CAST(T.START_TIME AS DATE) AS DATETIME)) AS [end_dt_1]
, T.END_TIME AS [end_dt]
FROM #_tmp AS T, calcs
UNION ALL
SELECT T1.ID
, DATEADD(HOUR, 24, CAST(CAST(T1.[end_dt_1] AS DATE) AS DATETIME)) AS [start_dt]
-- Find the "start" of the day (00:00:00) and add our constant times 2
, DATEADD(SECOND, calcs.[t_const_2], CAST(CAST(T1.[end_dt_1] AS DATE) AS DATETIME)) AS [end_dt_1]
, T1.[end_dt]
FROM timeCTE AS T1, calcs
WHERE CAST(T1.[end_dt_1] AS DATE) < CAST(T1.[end_dt] AS DATE)
)
, finalCTE AS (
SELECT T2.ID
, CONVERT(DATETIME, T2.[start_dt]) AS [start_dt]
, CASE
WHEN T2.[end_dt_1] > T2.[end_dt] THEN T2.[end_dt]
ELSE T2.[end_dt_1]
END AS [end_date]
FROM timeCTE AS T2
)
SELECT fin.[ID]
, CONVERT(DATETIME, fin.start_dt) AS [start_date]
, CONVERT(DATETIME, fin.end_date) AS [end_date]
, DATEPART(HOUR, fin.end_date) - DATEPART(HOUR, fin.[start_dt]) AS [net_daily_hours]
FROM finalCTE AS fin
Output:
ID start_dt end_date net_hours
01 2020-01-12 08:00:00.000 2020-01-12 23:59:59.000 15
02 2020-01-11 05:00:00.000 2020-01-11 23:59:59.000 18
02 2020-01-12 00:00:00.000 2020-01-12 23:59:59.000 23
02 2020-01-13 00:00:00.000 2020-01-13 02:00:00.000 2
01 2020-01-13 00:00:00.000 2020-01-13 23:59:59.000 23
01 2020-01-14 00:00:00.000 2020-01-14 08:00:00.000 8
Mostly drawn from this post.
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
T- SQL Split time in half hour intervals
The way I like to round datetime values to the nearest n-minute interval is to take advantage of SQL Server's integer math behavior. If you take the difference in minutes between midnight and the time in question, then divide by n and then multiply by n, it gets rid of any remainder. So to round right now down to the previous 30-minute interval:
DECLARE @now datetime = GETDATE();
DECLARE @today datetime = CONVERT(date, @now);
SELECT DATEADD
(
MINUTE,
DATEDIFF(MINUTE, @today, @now)/30*30,
@today
);
We can apply this to your query by taking your source table and using CROSS APPLY as Charlie suggested and apply that same calculation to your source values (you have to do a little more conversion inline because you don't have nice, static variables to use):
DECLARE @WindowSizeInMinutes smallint = 30;
SELECT x.interval, c.employee, call_count = COUNT(*)
FROM dbo.Calls AS c
CROSS APPLY
(
VALUES
(
DATEADD
(
MINUTE,
DATEDIFF
(
MINUTE,
CONVERT(datetime, CONVERT(date, call_pick_up_time)),
call_pick_up_time
) / @WindowSizeInMinutes * @WindowSizeInMinutes,
CONVERT(datetime, CONVERT(date, call_pick_up_time))
)
)
) AS x(interval)
-- WHERE c.something something
GROUP BY c.employee, x.interval;
If there is an index on call_pick_up_time you were hoping to use, that's out the window.
Another approach that could make use of an index is to pre-determine all the possible 30-minute windows in the range you're after, and then inner join to those:
DECLARE @WindowSizeInMinutes smallint = 30,
@min_date datetime = '20211001',
@max_date datetime = '20211014';
;WITH n(n) AS
(
SELECT 0 UNION ALL
SELECT n + 1
FROM n WHERE n <= 24*60/@WindowSizeInMinutes
),
days(d) AS
(
SELECT @min_date UNION ALL
SELECT DATEADD(DAY, 1, d)
FROM days WHERE d < @max_date
),
intervals AS
(
SELECT interval_start = DATEADD(MINUTE, n*@WindowSizeInMinutes, d),
interval_end = DATEADD(MINUTE, (n+1)*@WindowSizeInMinutes, d)
FROM n CROSS JOIN days
)
SELECT interval = i.interval_start,
c.employee,
call_count = COUNT(c.employee)
FROM intervals AS i
INNER JOIN dbo.Calls AS c
ON c.call_pick_up_time >= i.interval_start
AND c.call_pick_up_time < i.interval_end
GROUP BY c.employee, i.interval_start;
While more complicated, one nice thing about this approach is if you want to show slots for windows where no employees had calls, you could just change the join to an inner join, and if you wanted a slot for each employee, you could just add a CTE with the list of employees and cross join to that.
- Both examples on this db<>fiddle
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
Related Topics
How to Treat Max() of an Empty Table as 0 Instead of Null
How to Perform a Bitwise Group Function
#1146 - Table 'Phpmyadmin.Pma_Tracking' Doesn't Exist
Counting Rows for All Tables at Once
Recursive Query Used for Transitive Closure
SQL Selecting Rows Where One Column's Value Is Common Across Another Criteria Column
Delete Command Is Too Slow in a Table with Clustered Index
Date_Trunc 5 Minute Interval in Postgresql
What Column Should the Clustered Index Be Put On
Remove Trailing Empty Space in a Field Content
The Version of SQL Server in Use Does Not Support Datatype Datetime2
Anonymizing Customer Data for Development or Testing
Execute Query on SQL Server Analysis Services with Ironpython
Can There Be Constraints with the Same Name in a Db
How to Find the Average Time Difference Between Rows in a Table