我正在尝试计算自服务请求被记录以来已经过的时间(服务时间)，基于服务小时。
开始时间是票据被记录的时间(date_logged)，结束时间可以是打开票据的当前时间，也可以是关闭票据的date_closed时间。
服务时间:
- 周一至周四07:00 - 16:30
- 星期五07:00 - 13:00
它看起来应该像这样(减去"current datetime"列，这只是为了上下文):
| 票务编号 | date_logged | 服务时间[hh:mm] | 当前日期时间 | date_closed | 1234567 | 06.01.21 11:30:52 | 62:33 | 14.01.2021 12:03 |
|---|---|---|---|---|
| 8912345 | 13.01.21 09:14:16 | 12点 | 14.01.2021 12:03 | |
| 6789012 | 14.01.21 10:48:01 | 00:28 | 14.01.2021 12:03 | 14.01.21 11:40 |
| … | … | … | … | … |
您可以计算时间的数量(改编自我的答案在这里和这里):
SELECT ticket_nr,
date_logged,
current_datetime,
date_closed,
TO_CHAR( FLOOR( service_time_seconds / 60 / 60 ), 'FM9990' )
|| ':'
|| TO_CHAR( MOD( FLOOR( service_time_seconds / 60 ), 60 ), 'FM00' )
|| ':'
|| TO_CHAR( MOD( service_time_seconds, 60 ), 'FM00' )
AS "SERVICE_TIME HH:MM:SS"
FROM (
SELECT ticket_nr,
date_logged,
SYSDATE AS current_datetime,
date_closed,
ROUND(
(
-- Calculate the full weeks difference from the start of ISO weeks.
(
TRUNC( COALESCE( date_closed, SYSDATE ), 'IW' )
- TRUNC( date_logged, 'IW' )
) * (9.5*4+6)/(7*24)
-- Add the hours for the full days for the final week.
+ DECODE(
TRUNC( COALESCE( date_closed, SYSDATE ) )
- TRUNC( COALESCE( date_closed, SYSDATE ), 'IW' ),
0, 0.0,
1, 9.5,
2, 19.0,
3, 28.5,
4, 38.0,
44.0
) / 24
-- Subtract the hours for the full days from the days of the week
-- before the date logged.
- DECODE(
TRUNC( date_logged )
- TRUNC( date_logged, 'IW' ),
0, 0.0,
1, 9.5,
2, 19.0,
3, 28.5,
4, 38.0,
44.0
) / 24
-- Add the hours of the final day
+ LEAST(
GREATEST(
COALESCE( date_closed, SYSDATE )
- ( TRUNC( COALESCE( date_closed, SYSDATE ) )
+ INTERVAL '07:00' HOUR TO MINUTE
),
0
),
DECODE(
TRUNC( COALESCE( date_closed, SYSDATE ) )
- TRUNC( COALESCE( date_closed, SYSDATE ), 'IW' ),
0, 9.5,
1, 9.5,
2, 9.5,
3, 9.5,
4, 6.0,
0.0
) / 24
)
-- Subtract the hours of the day before the range starts.
- LEAST(
GREATEST(
date_logged
- ( TRUNC( date_logged ) + INTERVAL '07:00' HOUR TO MINUTE ),
0
),
DECODE(
TRUNC( date_logged )
- TRUNC( date_logged, 'IW' ),
0, 9.5,
1, 9.5,
2, 9.5,
3, 9.5,
4, 6.0,
0.0
) / 24
)
)
-- Multiply to give seconds rather than fractions of full days.
* 24 * 60 * 60
) AS service_time_seconds
FROM table_name
);
对于样本数据:
CREATE TABLE table_name ( Ticket_Nr, date_logged, date_closed ) AS
SELECT 1234567, DATE '2021-01-06' + INTERVAL '11:30:52' HOUR TO SECOND, NULL FROM DUAL UNION ALL
SELECT 8912345, DATE '2021-01-13' + INTERVAL '09:14:16' HOUR TO SECOND, NULL FROM DUAL UNION ALL
SELECT 6789012, DATE '2021-01-14' + INTERVAL '10:48:28' HOUR TO SECOND, DATE '2021-01-21' + INTERVAL '11:40:00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 1, DATE '2021-01-07' + INTERVAL '07:00:00' HOUR TO SECOND, DATE '2021-01-14' + INTERVAL '07:00:00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 2, DATE '2021-01-07' + INTERVAL '07:00:00' HOUR TO SECOND, DATE '2021-01-08' + INTERVAL '07:00:00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 3, DATE '2021-01-08' + INTERVAL '07:00:00' HOUR TO SECOND, DATE '2021-01-09' + INTERVAL '07:00:00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 4, DATE '2021-01-09' + INTERVAL '07:00:00' HOUR TO SECOND, DATE '2021-01-10' + INTERVAL '07:00:00' HOUR TO SECOND FROM DUAL
输出(其中NLS_DATE_FORMAT为YYYY-MM-DD HH24:MI:SS (DY)):
TICKET_NR | DATE_LOGGED | CURRENT_DATETIME | DATE_CLOSED | SERVICE_TIME HH:MM:SS--------: | :------------------------ | :------------------------ | :------------------------ | :--------------------1234567 | 2021-01-06 11:30:52 (WED) | 2021-01-14 12:36:54 (THU) | null| 54:36:028912345 | 2021-01-13 09:14:16 (WED) | 2021-01-14 12:36:54 (THU) | null| 12:52:386789012 | 20121-01-14 10:48:28 (thu) | 20121-01-14 12:36:54 (thu) | 20121-01-21 11:40:00 (thu) | 44:51:321 | 2021-01-07 07:00:00 (thu) | 2021-01-14 12:36:54 (thu) | 2021-01-14 07:00:00 (thu) | 44:00:002 | 2021-01-07 07:00:00 (thu) | 2021-01-14 12:36:54 (thu) | 2021-01-08 07:00:00 (fri) | 9:30:003 | 2021-01-08 07:00:00(星期五)| 2021-01-14 12:36:54(星期四)| 2021-01-09 07:00:00(星期六)| 6:00:004 | 20121-01-09 07:00:00 (SAT) | 20121-01-14 12:36:54 (THU) | 20121-01-10 07:00:00 (SUN) | 0:00:00
db<此处小提琴>