blocks|key|2508724|text|从周五上午8点到周一上午9点的示例开始：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2508725|with+dates+as+(
+Select+timestamp+'2019-05-31+08:00:00'+start_date
++++++,+timestamp+'2019-06-03+09:00:00'+end_date
+++from+dual
)|code-block|syntax|javascript|2508726|我们需要生成介于两者之间的天数。我们可以使用递归查询来做到这一点：|2508727|,+recur(start_date,+calc_date,+end_date)+as+(
++--+Anchor+Part
++select+start_date
+++++++,+trunc(start_date)
+++++++,+end_date
++++from+dates

++--+Recrusive+Part
++union+all
++select+start_date
+++++++,+calc_date%2B1
+++++++,+end_date
++++from+recur
+++where+calc_date%2B1+<+end_Date
)|2508728|由此，我们需要弄清楚一些事情，例如，calc_day是工作日还是周末，calc_day的开始和结束时间是什么，然后我们可以获取这些值，并使用一些日期算法来找到当天工作的小时数(从我们开始使用时间戳以来，以每天到秒的间隔返回)：|2508729|,+days+as+(
++select+calc_date
+++++++,+case+when+mod(to_number(to_char(calc_date,'d'))-1,6)+!=+0+then+1+end+isWeekDay
+++++++,+greatest(start_date,+calc_date+%2B+interval+'8'+hour)+start_time
+++++++,+least(end_date,+calc_date++++++%2B+interval+'16:30'+hour+to+minute)+end_time

+++++++,+least(+(+least(end_date,+calc_date++++++%2B+interval+'16:30'+hour+to+minute)+
++++++++++++++++-+greatest(start_date,+calc_date+%2B+interval+'8'+hour)
++++++++++++++++)+*+case+when+mod(to_number(to_char(calc_date,'d'))-1,6)+!=+0+then+1+end
++++++++++++++,+interval+'8'+hour
+++++++++)+daily_hrs
++++from+recur
+++where+start_date+<+(calc_date+%2B+interval+'16:30'+hour+to+minute)
+++++and+(calc_date+%2B+interval+'8'+hour)+<+end_date
)|2508730|请注意，在上面的步骤中，我们将每天的工作时间限制为每天8小时，where子句防止在营业时间之外的开始或结束日期。最后一步是计算小时数的总和。不幸的是，Oracle没有任何本机间隔聚合或分析函数，但我们仍然可以通过将间隔转换为秒，对它们求和，然后将它们转换回间隔来进行管理：|2508731|select+calc_date
+++++,+daily_hrs
+++++,+numtodsinterval(sum(+extract(hour+++from+daily_hrs)*60*60
++++++++++++++++++++++++++%2B+extract(minute+from+daily_hrs)*60
++++++++++++++++++++++++++%2B+extract(second+from+daily_hrs)
++++++++++++++++++++++++++)+over+(order+by+calc_date)
++++++++++++++++++++++,'second')+run_sum
++from+days;|2508732|我把上面的求和作为一个分析函数，这样我们就可以看到一些中间的数据，但是如果你只想要最终的输出，你可以把查询的最后一部分修改为：|2508733|select+numtodsinterval(sum(+extract(hour+from+daily_hrs)*60*60
++++++++++++++++++++++++++%2B+extract(minute+from+daily_hrs)*60
++++++++++++++++++++++++++%2B+extract(second+from+daily_hrs)
++++++++++++++++++++++++++)
++++++++++++++++++++++,'second')+run_sum|2508734|这是一个包含整个查询的。请注意，在小提琴中，我已经将数据库会话的NLS_TERRITORY设置更改为美国，以使查询工作，因为每周的第一天是特定于国家的。小提琴中的第二个查询替换了特定于地区的函数：|2508735|case+when+mod(to_number(to_char(calc_date,'d'))-1,6)+!=+0+then+1+end|2508736|使用与位置和语言无关的计算：|2508737|case+when+(mod(mod(calc_date+-+next_day(date+'2019-1-1',to_char(date+'2019-01-06','day')),7),6))+!=+0+then+1+end|2508738|entityMap^0|0|0|0|0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|16|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|17|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|18|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|19|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|1A|8|@]|9|@]|A|$]]|$1|M|3|N|5|D|7|1B|8|@]|9|@]|A|$E|F]]|$1|O|3|P|5|6|7|1C|8|@]|9|@]|A|$]]|$1|Q|3|R|5|D|7|1D|8|@]|9|@]|A|$E|F]]|$1|S|3|T|5|6|7|1E|8|@]|9|@]|A|$]]|$1|U|3|V|5|D|7|1F|8|@]|9|@]|A|$E|F]]|$1|W|3|X|5|6|7|1G|8|@]|9|@]|A|$]]|$1|Y|3|Z|5|D|7|1H|8|@]|9|@]|A|$E|F]]|$1|10|3|11|5|6|7|1I|8|@]|9|@]|A|$]]|$1|12|3|13|5|D|7|1J|8|@]|9|@]|A|$E|F]]|$1|14|3|-4|5|6|7|1K|8|@]|9|@]|A|$]]]|15|$]]

Starting with your example of 8AM Friday through 9AM Monday:

<pre><code>with dates as (
 Select timestamp '2019-05-31 08:00:00' start_date
 , timestamp '2019-06-03 09:00:00' end_date
 from dual
)
</code></pre>

We need to generate the days in between. We can do that with a recursive query:

<pre><code>, recur(start_date, calc_date, end_date) as (
 -- Anchor Part
 select start_date
 , trunc(start_date)
 , end_date
 from dates

 -- Recrusive Part
 union all
 select start_date
 , calc_date+1
 , end_date
 from recur
 where calc_date+1 &lt; end_Date
)
</code></pre>

From that we need to figure out a few things like, is the calc_day a weekday or a weekend, and what are the starting and ending times for the calc_day, we can then take those values and use a little date arithmetic to find the number of hours worked on that day (returned as day to second interval since we started with timestamps):

<pre><code>, days as (
 select calc_date
 , case when mod(to_number(to_char(calc_date,'d'))-1,6) != 0 then 1 end isWeekDay
 , greatest(start_date, calc_date + interval '8' hour) start_time
 , least(end_date, calc_date + interval '16:30' hour to minute) end_time

 , least( ( least(end_date, calc_date + interval '16:30' hour to minute) 
 - greatest(start_date, calc_date + interval '8' hour)
 ) * case when mod(to_number(to_char(calc_date,'d'))-1,6) != 0 then 1 end
 , interval '8' hour
 ) daily_hrs
 from recur
 where start_date &lt; (calc_date + interval '16:30' hour to minute)
 and (calc_date + interval '8' hour) &lt; end_date
)
</code></pre>

Note that in the above step, we've limited the daily hours to 8 hours a day, and the where clause guards against start or end days that are outside business hours. The final step is to sum the hours. Unfortunately Oracle doesn't have any native interval aggregate or analytic functions, but we can still manage by converting the intervals to seconds, summing them and then converting them back to an interval for output:

<pre><code>select calc_date
 , daily_hrs
 , numtodsinterval(sum( extract(hour from daily_hrs)*60*60
 + extract(minute from daily_hrs)*60
 + extract(second from daily_hrs)
 ) over (order by calc_date)
 ,'second') run_sum
 from days;
</code></pre>

I've done the sum above as an analytic function so we can see some of the intervening data, but if you just want the final output you can change the last part of the query to this:

<pre><code>select numtodsinterval(sum( extract(hour from daily_hrs)*60*60
 + extract(minute from daily_hrs)*60
 + extract(second from daily_hrs)
 )
 ,'second') run_sum
</code></pre>

Here's a <a href="https://dbfiddle.uk/?rdbms=oracle_18&amp;fiddle=1029401ba9c7a3f13fe5e93e99544af1" rel="nofollow noreferrer">db&lt;>fiddle</a> with the whole query in action. Note that in the fiddle, I've altered the DB session's NLS_TERRITORY setting to AMERICA to make the query work since the first day of the week is country specific. The second query in the fiddle replaces the territory specific function:

<pre><code>case when mod(to_number(to_char(calc_date,'d'))-1,6) != 0 then 1 end
</code></pre>

with a location and language agnostic calculation:

<pre><code>case when (mod(mod(calc_date - next_day(date '2019-1-1',to_char(date '2019-01-06','day')),7),6)) != 0 then 1 end
</code></pre>

I realize that this might be a somewhat redundant question BUT I have struggled to follow some of the examples that I did find and I thought I would ask again providing details on my specific scenario.

Here is why I am working with:

<ul>
<li>Oracle Database</li>
<li>The dates are in timestamp format</li>
<li>I cannot create any additional tables/views (due to permission issue)</li>
<li>I cannot create any custom functions (due to permission issue)</li>
<li>I have a 40 hour work week and business hours of 8 to 4:30 Monday through Friday. (I guess technically that leaves us with more than 40 hours to account for b/c I don't want to get SO FANCY TO worry about excluding lunch breaks)</li>
</ul>

I'm able to figure out to calculate hours but I don't know how to get in the business day component.

Calculate Business Hours Between Two Dates without Creating Function or View

Oracle数据库

数据库

我意识到这可能是一个有点多余的问题，但我一直在努力遵循我确实找到的一些例子，我想我应该再问一遍，提供关于我的特定场景的细节。这就是我为什么要使用：Oracle数据库日期格式为时间戳格式我无法创建任何其他表/视图(由于权限问题)我无法创建任何自定义函数(由于权限问题)我每周工作40小时，周一至周五的营业时间为8至4:30...

问在不创建函数或视图的情况下计算两个日期之间的营业时间
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在不创建函数或视图的情况下计算两个日期之间的营业时间EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在不创建函数或视图的情况下计算两个日期之间的营业时间
EN