计算PostgreSQL中两个日期之间的工作时间
我正在使用Postgres(PL / pgSQL)开发一个算法,我需要计算2个时间戳之间的工作时数,考虑到周末不工作,其余时间只能从上午8点到下午15点进行.
例子: >从12月3日14时至12月4日上午9时应计2小时: 3rd = 1,4th = 1 >从12月3日下午15点到12月7日上午8点应该算8小时: 3rd = 0,4th = 8,5th = 0,6th = 0,7th = 0 考虑小时数也是很好的.
根据你的问题,工作时间是:Mo-Fr,08:00-15:00.
圆圆结果 只有两个给定的时间戳 以1小时为单位运行.分数被忽略,因此不精确但简单: SELECT count(*) AS work_hours FROM generate_series (timestamp '2013-06-24 13:30',timestamp '2013-06-24 15:29' - interval '1h',interval '1h') h WHERE EXTRACT(ISODOW FROM h) < 6 AND h::time >= '08:00' AND h::time <= '14:00'; >如果结束大于开始,则每个完整给定间隔(1小时)的函数 整个桌子 CREATE TEMP TABLE t (t_id int PRIMARY KEY,t_start timestamp,t_end timestamp); INSERT INTO t VALUES (1,'2009-12-03 14:00','2009-12-04 09:00'),(2,'2009-12-03 15:00','2009-12-07 08:00') -- examples in question,(3,'2013-06-24 07:00','2013-06-24 12:00'),(4,'2013-06-24 12:00','2013-06-24 23:00'),(5,'2013-06-23 13:00','2013-06-25 11:00'),(6,'2013-06-23 14:01','2013-06-24 08:59'); -- max. fractions at begin and end 查询: SELECT t_id,count(*) AS work_hours FROM ( SELECT t_id,generate_series (t_start,t_end - interval '1h',interval '1h') AS h FROM t ) sub WHERE EXTRACT(ISODOW FROM h) < 6 AND h::time >= '08:00' AND h::time <= '14:00' GROUP BY 1 ORDER BY 1; SQL Fiddle. 更精准 为了获得更高的精度,您可以使用较小的时间单位.例如:5分钟的切片 SELECT t_id,count(*) * interval '5 min' AS work_interval FROM ( SELECT t_id,t_end - interval '5 min',interval '5 min') AS h FROM t ) sub WHERE EXTRACT(ISODOW FROM h) < 6 AND h::time >= '08:00' AND h::time <= '14:55' -- 15.00 - interval '5 min' GROUP BY 1 ORDER BY 1; 单位越小成本越高. 在Postgres中使用LATERAL清洁9.3 结合Postgres 9.3中的新 1小时精度: SELECT t.t_id,h.work_hours FROM t LEFT JOIN LATERAL ( SELECT count(*) AS work_hours FROM generate_series (t.t_start,t.t_end - interval '1h',interval '1h') h WHERE EXTRACT(ISODOW FROM h) < 6 AND h::time >= '08:00' AND h::time <= '14:00' ) h ON TRUE ORDER BY 1; 5分钟精度: SELECT t.t_id,h.work_interval FROM t LEFT JOIN LATERAL ( SELECT count(*) * interval '5 min' AS work_interval FROM generate_series (t.t_start,t.t_end - interval '5 min',interval '5 min') h WHERE EXTRACT(ISODOW FROM h) < 6 AND h::time >= '08:00' AND h::time <= '14:55' ) h ON TRUE ORDER BY 1; 这另外的优点是,如上述版本所示,不会从结果中排除包含零工作时间的间隔. 更多关于LATERAL: > Find most common elements in array with a group by 确切的结果 Postgres 8.4 或者您分别处理时间段的开始和结束,以获得精确的结果到微秒.使查询更复杂,但更便宜和更精确: WITH var AS (SELECT '08:00'::time AS v_start,'15:00'::time AS v_end) SELECT t_id,COALESCE(h.h,'0') -- add / subtract fractions - CASE WHEN EXTRACT(ISODOW FROM t_start) < 6 AND t_start::time > v_start AND t_start::time < v_end THEN t_start - date_trunc('hour',t_start) ELSE '0'::interval END + CASE WHEN EXTRACT(ISODOW FROM t_end) < 6 AND t_end::time > v_start AND t_end::time < v_end THEN t_end - date_trunc('hour',t_end) ELSE '0'::interval END AS work_interval FROM t CROSS JOIN var LEFT JOIN ( -- count full hours,similar to above solutions SELECT t_id,count(*)::int * interval '1h' AS h FROM ( SELECT t_id,v_start,v_end,generate_series (date_trunc('hour',t_start),date_trunc('hour',t_end) - interval '1h',interval '1h') AS h FROM t,var ) sub WHERE EXTRACT(ISODOW FROM h) < 6 AND h::time >= v_start AND h::time <= v_end - interval '1h' GROUP BY 1 ) h USING (t_id) ORDER BY 1; SQL Fiddle. Postgres 9.2与 新系列类型提供了更加优雅的解决方案,结合intersection operator 时间范围仅限一天的简单功能: CREATE OR REPLACE FUNCTION f_worktime_1day(_start timestamp,_end timestamp) RETURNS interval AS $func$ -- _start & _end within one calendar day! - you may want to check ... SELECT CASE WHEN extract(ISODOW from _start) < 6 THEN ( SELECT COALESCE(upper(h) - lower(h),'0') FROM ( SELECT tsrange '[2000-1-1 08:00,2000-1-1 15:00)' -- hours hard coded * tsrange( '2000-1-1'::date + _start::time,'2000-1-1'::date + _end::time ) AS h ) sub ) ELSE '0' END $func$ LANGUAGE sql IMMUTABLE; 如果您的范围从不跨越多天,那就是您需要的. CREATE OR REPLACE FUNCTION f_worktime(_start timestamp,_end timestamp,OUT work_time interval) AS $func$ BEGIN CASE _end::date - _start::date -- spanning how many days? WHEN 0 THEN -- all in one calendar day work_time := f_worktime_1day(_start,_end); WHEN 1 THEN -- wrap around midnight once work_time := f_worktime_1day(_start,NULL) + f_worktime_1day(_end::date,_end); ELSE -- multiple days work_time := f_worktime_1day(_start,_end) + (SELECT count(*) * interval '7:00' -- workday hard coded! FROM generate_series(_start::date + 1,_end::date - 1,'1 day') AS t WHERE extract(ISODOW from t) < 6); END CASE; END $func$ LANGUAGE plpgsql IMMUTABLE; 呼叫: SELECT t_id,f_worktime(t_start,t_end) AS worktime FROM t ORDER BY 1; SQL Fiddle. (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |