计算时间戳之间的营业时间 [英] Counting business hours between timestamps

问题描述

我正在使用Postgres 8.3（目前版本中没有选择）。我的原始数据表如下：

I am using Postgres 8.3 (no choice in the version at this time). My raw data table is as follows:

ID  start_time               finish_time
01   2013-01-23 10:47:52-05  2013-02-25 11:18:36-05

我可以在两个时间戳之间进行计数：

I can count between the two timestamps:

--relevant line in view creation query:
date_part('epoch',(finish_time - start_time)::interval)/3600 as hours

我不想包括周末。另外，我只想计数09:00-17:30。

I do not want to include weekends. Also, I only want to count 09:00 - 17:30.

在一个理想的世界中，我每天也要减去一个小时的午餐时间，最终我也想加入公司假期，但我想解决这个问题小时部分优先。

In a perfect world, I would subtract an hour for lunch per day as well and eventually I would also like to include company holidays but I would just like to solve this working hours portion first.

关于如何处理此问题的任何建议？我对SQL很陌生。我也愿意使用SQLalchemy，但是我也是那里的初学者，并且对直接SQL感到更舒服。

Any advice on how to approach this? I am pretty new to SQL. I am open to using SQLalchemy as well, but am also a beginner there as well and feel more comfortable with straight SQL.

推荐答案

想象一下，您有一张工作表。 （或构建一个。这个未经测试，因此可能包含时区和围栏错误。）

Imagine that you have a table of work minutes. (Or build one. This one isn't tested, so it might contain timezone and fencepost errors.)

create table work_minutes (
  work_minute timestamp primary key
);

insert into work_minutes
select work_minute
from 
  (select generate_series(timestamp '2013-01-01 00:00:00', timestamp '2013-12-31 11:59:00', '1 minute') as work_minute) t
where extract(isodow from work_minute) < 6
  and cast(work_minute as time) between time '09:00' and time '17:30'

现在您的查询可以计算分钟了，简直太简单了。

Now your query can count minutes, and that's just dead simple.

select count(*)/60.0 as elapsed_hrs
from work_minutes
where work_minute between '2013-01-23 10:47:52' and '2013-02-25 11:18:36'

elapsed_hours
--
196.4

您可以决定小数小时的处理方式。

You can decide what to do with fractional hours.

根据分钟数和小时数来计算，分钟和小时之间可能会有很大的不同。基于小时的计算在一小时内超出停止时间的分钟可能不会占很多时间。

There can be a substantial difference between calculating by minutes and calculating by hours, depending on how you treat the start time and such. Calculations based on hours might not count a lot of minutes in an hour that extends beyond the stop time. Whether it matters is application-dependent.

您可以使用generate_series（）快速生成一个虚拟表，但是这样的基本表只需要约400万行可以覆盖30年，这种计算确实非常快。

You can generate a virtual table like this on the fly with generate_series(), but a base table like this only needs about 4 million rows to cover 30 years, and this kind of calculation on it is really fast.

稍后。 。 。

我看到 Erwin Brandstetter介绍了现代PostgreSQL对generate_series（）的使用；它在8.3版中不起作用，因为8.3不支持通用表表达式或generate_series（timestamp，timestamp）。这是Erwin查询的一种版本，可以避免这些问题。这不是完全忠实的翻译；计算相差一个小时。在我看来，这可能是一个篱笆墙错误，但是我现在没有时间去研究细节。

I see that Erwin Brandstetter covered the use of generate_series() for modern PostgreSQL; it won't work in version 8.3, because 8.3 doesn't support common table expressons or generate_series(timestamp, timestamp). Here's a version of Erwin's query that avoids those problems. This isn't a completely faithful translation; the calculations differ by an hour. That's probably a fencepost error on my part, but I don't have time to dig into the details right now.

select count(*) from 
(select timestamp '2013-01-23 10:47:52-05' + (n || ' hours')::interval
from generate_series(  0
                     , (extract(days from timestamp '2013-02-25 11:18:36-05' 
                                        - timestamp '2013-01-23 10:47:52-05')::integer * 24) ) n
where extract(isodow from (timestamp '2013-01-23 10:47:52-05' + (n || ' hours')::interval)) < 6
  and (timestamp '2013-01-23 10:47:52-05' + (n || ' hours')::interval)::time >= '09:00'::time
  and (timestamp '2013-01-23 10:47:52-05' + (n || ' hours')::interval)::time <  '17:30'::time
 ) t

基于表的解决方案具有易于处理管理异想天开的优点。 嘿！我们的狗有七只小狗！今天半天！

A table-based solution has the advantage of easily handling management whimsy. "Hey! Our dog had seven puppies! Half day today!" It also scales well, and it works on virtually every platform without modification.

如果使用generate_series（），则将其包装在视图中。这样，可以在一个地方管理对规则的任意更改。而且，如果规则变得太复杂而无法在视图中轻松维护，则可以用具有相同名称的表替换视图，所有应用程序代码，SQL以及存储过程和函数都将正常工作。

If you use generate_series(), wrap it in a view. That way, arbitrary changes to the rules can be managed in one place. And if the rules become too complicated to maintain easily within the view, you can replace the view with a table having the same name, and all the application code, SQL, and stored procedures and functions will just work.

这篇关于计算时间戳之间的营业时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！