如何将工作时间转换为布尔值

Question

如何转换工作时间08:00:00-11:59:00；13:00:00-16:59:00；转换为48位布尔格式，如

"000000000000000011111111001111111100000000000000" 

其中每个数字表示使用Oracle SQL Query的30分钟粒度？

Answer 1

假设您从一个字符串开始，该字符串总是有以分号分隔的从/到时间对，最后一对后面有分号；并且这些时间总是HH24:MI:SS，其中秒总是零，如图所示；然后您可以将字符串拆分为多个字符串对，表示每个from/to对：

select regexp_substr('08:00:00-11:59:00;13:00:00-16:59:00;', '(.*?)(;|-|$)', 1, (2 * level - 1), null, 1),
regexp_substr('08:00:00-11:59:00;13:00:00-16:59:00;', '(.*?)(;|-|$)', 1, 2 * level, null, 1)
from dual
connect by level <= regexp_count('08:00:00-11:59:00;13:00:00-16:59:00;', ';')

REGEXP_S REGEXP_S
-------- --------
08:00:00 11:59:00
13:00:00 16:59:00

你可以在一天内生成所有半小时的区块(选择一个不受夏令时切换的区块(：

select to_char(date '2000-01-01' + ((level - 1) / 48), 'HH24:MI":00"')
from dual
connect by level <= 48

TO_CHAR(
--------
00:00:00
00:30:00
01:00:00
01:30:00
02:00:00
...
23:00:00
23:30:00

然后使用字符串比较将它们连接在一起，看看哪里有重叠(这就是时间格式很重要的原因(；为了简单起见，使用CTE提供初始字符串，然后提供前两个查询的结果：

with t1 (working_hours) as (
select '08:00:00-11:59:00;13:00:00-16:59:00;' from dual
),
t2 (working_from, working_to) as (
select regexp_substr(working_hours, '(.*?)(;|-|$)', 1, (2 * level - 1), null, 1),
regexp_substr(working_hours, '(.*?)(;|-|$)', 1, 2 * level, null, 1)
from t1
connect by level <= regexp_count(working_hours, ';')
),
t3 (block_from) as (
select to_char(date '2000-01-01' + ((level - 1) / 48), 'HH24:MI":00"')
from dual
connect by level <= 48
)
select block_from,
case when t2.working_from is null then 0 else 1 end as flag
from t3
left join t2 on t2.working_from <= t3.block_from and t2.working_to >= t3.block_from

BLOCK_FROM  FLAG
----------  ----
00:00:00    0
00:30:00    0
...
07:30:00    0
08:00:00    1
08:30:00    1
...
11:00:00    1
11:30:00    1
12:00:00    0
12:30:00    0
13:00:00    1
13:30:00    1
...
16:00:00    1
16:30:00    1
17:00:00    0
...
23:00:00    0
23:30:00    0

然后最后将它们聚合到一个单独的结果字符串中：

with t1 (working_hours) as (
select '08:00:00-11:59:00;13:00:00-16:59:00;' from dual
),
t2 (working_from, working_to) as (
select regexp_substr(working_hours, '(.*?)(;|-|$)', 1, (2 * level - 1), null, 1),
regexp_substr(working_hours, '(.*?)(;|-|$)', 1, 2 * level, null, 1)
from t1
connect by level <= regexp_count(working_hours, ';')
),
t3 (block_from) as (
select to_char(date '2000-01-01' + ((level - 1) / 48), 'HH24:MI":00"')
from dual
connect by level <= 48
)
select listagg(case when t2.working_from is null then 0 else 1 end)
within group (order by t3.block_from) as result
from t3
left join t2 on t2.working_from <= t3.block_from and t2.working_to >= t3.block_from

RESULT
------------------------------------------------
000000000000000011111111001111111100000000000000

db<gt；小提琴

如果您的初始字符串实际上来自一个表，并且您需要同时对多行进行转换，那么通过拆分进行连接会有点复杂，递归CTE可能更适合该部分。

只是为了好玩，这里有一个例子。