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

本文是小编为大家收集整理的关于计算时间戳之间的营业时间的处理方法,想解了计算时间戳之间的营业时间的问题怎么解决?计算时间戳之间的营业时间问题的解决办法?那么可以参考本文帮助大家快速定位并解决问题。

问题描述

我使用的是 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;它不适用于 8.3 版本,因为 8.3 不支持公用表表达式或 generate_series(timestamp, timestamp).这是避免这些问题的 Erwin 查询的一个版本.这不是一个完全忠实的翻译;计算相差一个小时.这可能是我的一个栅栏错误,但我现在没有时间深入研究细节.

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 以及存储过程和函数都将正常工作.

本文地址:https://www.itbaoku.cn/post/1763972.html