我正在使用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
想象一下你有一个工作时间表。（或者自己创建一个。这个没有经过测试，所以可能包含时区和栅木错误。）
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
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和存储过程和函数都将正常工作。
SELECT count(*)
FROM generate_series(0, extract(days from timestamp '2013-02-25 11:18:36'
- timestamp '2013-01-23 10:47:52')::int * 24) n
WHERE extract(ISODOW from timestamp '2013-01-23 10:47:52' + n * interval '1h') < 6
AND (timestamp '2013-01-23 10:47:52' + n * interval '1h')::time >= '09:00'::time
AND (timestamp '2013-01-23 10:47:52' + n * interval '1h')::time < '17:30'::time
timestamp '2013-01-23 10:47:52-05' is not doing what you seem to think. The time zone offset -05 is discarded because you cast the literal to timestamp [without timezone].
You probably wanted timestamptz '2013-01-23 10:47:52-05'.
However, working hours are usually bound to local time, so it could be argued that timestamp [without time zone] is a better fit to begin with. More in this related answer:
Ignoring timezones altogether in Rails and PostgreSQL
This form is much more efficient
timestamptz '2013-01-23 10:47:52-05' + n * interval '1h'
than this:
timestamptz '2013-01-23 10:47:52-05' + (n || ' hours')::interval
You can simply multiply any interval.
我进一步开发并将其封装为SQL函数。
虽然不是很精确，但它修复了系统错误，并且由于半小时单位而具有更小的舍入误差。
CREATE OR REPLACE FUNCTION f_worktime83(t_start timestamp
, t_end timestamp)
RETURNS interval AS
$func$
SELECT (count(*) - 1) * interval '30 min' -- fix off-by-one error
FROM (
SELECT $1 + generate_series(0, (extract(epoch FROM $2 - $1)/1800)::int)
* interval '30 min' AS t
) sub
WHERE extract(ISODOW from t) < 6
AND t::time >= '09:00'::time
AND t::time < '17:30'::time
$func$ LANGUAGE sql
电话：
SELECT f_worktime83('2013-06-26 10:47:52', '2013-06-26 11:10:51')
generate_series()添加值，简化代码。epoc（秒数）并将其除以1800（30分钟的秒数）来获取（四舍五入的）精确时间单位的数量。