计算时间戳之间的营业时间[重复]

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

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

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

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

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

在完美的世界中，我也会每天减去一个小时的午餐时间，最终我还想包括公司假期，但我只想先解决这个工作时间部分。

关于如何处理这个问题有什么建议吗？我对 SQL 还很陌生。我也愿意使用 SQLalchemy，但我也是一个初学者，并且对直接 SQL 感觉更舒服。

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

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'

现在您的查询可以计算分钟数，这非常简单。

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

您可以决定如何处理零碎时间。

按分钟计算和按小时计算之间可能存在很大差异，具体取决于您如何对待开始时间等。基于小时的计算可能不会计算超出停止时间的一小时内的很多分钟。是否重要取决于应用程序。

你可以使用generate_series()动态生成这样的虚拟表，但是这样的基表只需要大约400万行就可以覆盖30年，而且这种计算速度非常快。

之后 。 。 。

我看到Erwin Brandstetter 介绍了generate_series() 在现代 PostgreSQL 中的使用 https://stackoverflow.com/questions/1839319/calculate-working-hours-between-2-dates-in-postgresql;它在 8.3 版本中不起作用，因为 8.3 不支持公用表表达式或generate_series(timestamp, timestamp)。这是埃尔文查询的一个版本，可以避免这些问题。这并不是一个完全忠实的翻译；计算结果相差一个小时。这可能是我的一个栅栏错误，但我现在没有时间深入研究细节。

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

基于表格的解决方案的优点是可以轻松应对管理的突发奇想。 “嘿！我们的狗生了七只小狗！今天半天！”它的扩展性也很好，并且无需修改即可在几乎所有平台上运行。

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