例子:
>从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_hoursFROM generate_serIEs (timestamp '2013-06-24 13:30',timestamp '2013-06-24 15:29' - interval '1h',interval '1h') hWHERE EXTRACT(ISodoW FROM h) < 6AND h::time >= '08:00'AND h::time <= '14:00';
>如果结束大于开始,则每个完整给定间隔(1小时)的函数generate_series()
生成一行.这个数字每小时输入一次.忽略分数小时,从最后减去1小时.而不要在14:00之前开始计时.
>使用字段模式ISodoW而不是DOW for EXTRACT()
来简化表达式.星期日返回7而不是0.
>一个简单的(非常便宜的)时间使得很容易识别符合条件的时间.
>忽略一小时的分数,即使间隔开始和结束的分数加起来一个小时或更长时间.
整个桌子
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_hoursFROM ( SELECT t_ID,generate_serIEs (t_start,t_end - interval '1h',interval '1h') AS h FROM t ) subWHERE EXTRACT(ISodoW FROM h) < 6AND h::time >= '08:00'AND h::time <= '14:00'GROUP BY 1ORDER BY 1;
SQL Fiddle.
更精准
为了获得更高的精度,您可以使用较小的时间单位.例如:5分钟的切片
SELECT t_ID,count(*) * interval '5 min' AS work_intervalFROM ( SELECT t_ID,t_end - interval '5 min',interval '5 min') AS h FROM t ) subWHERE EXTRACT(ISodoW FROM h) < 6AND h::time >= '08:00'AND h::time <= '14:55' -- 15.00 - interval '5 min'GROUP BY 1ORDER BY 1;
单位越小成本越高.
在Postgres中使用LAteraL清洁9.3
结合Postgres 9.3中的新LATERAL
功能,上述查询可以写成:
1小时精度:
SELECT t.t_ID,h.work_hoursFROM tleft 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 TRUEORDER BY 1;
5分钟精度:
SELECT t.t_ID,h.work_intervalFROM tleft 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 TRUEORDER BY 1;
这另外的优点是,如上述版本所示,不会从结果中排除包含零工作时间的间隔.
更多关于LAteraL:
> Find most common elements in array with a group by
> Insert multiple rows in one table based on number in another table
确切的结果
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_intervalFROM t CROSS JOIN varleft 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与tsrange
新系列类型提供了更加优雅的解决方案,结合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 worktimeFROM tORDER BY 1;
SQL Fiddle.
总结以上是内存溢出为你收集整理的计算PostgreSQL中两个日期之间的工作时间全部内容,希望文章能够帮你解决计算PostgreSQL中两个日期之间的工作时间所遇到的程序开发问题。
如果觉得内存溢出网站内容还不错,欢迎将内存溢出网站推荐给程序员好友。
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)