考勤月度统计mysql_mysql中跨月统计考勤天数-问答-阿里云开发者社区-阿里云

如果是PostgreSQL，你可以这样来实现：

首先需要创建一个剔除周末的函数：create or replace function minus_weekend(timestamp, timestamp) returns interval as

$$

declare

s timestamp := $1;

e timestamp := $2;

sd date;

ed date;

i interval := interval '0';

x int;

x1 interval;

x2 interval;

begin

if e < s then

s := $2;

e := $1;

end if;

select case when extract(isodow from s) not in (6,7) then date(s+interval '1 day')-s else interval '0' end,

case when extract(isodow from e) not in (6,7) then e-date(e) else interval '0' end

into x1, x2;

if date(e)-date(s) = 0 then

if extract(isodow from s) not in (6,7) then

return e-s;

else

return interval '0';

end if;

elsif date(e)-date(s) = 1 then

return x1 + x2;

end if;

sd := date(s)+1;

ed := date(e);

for x in 0..(ed-sd-1) loop

if extract(isodow from sd+x) not in (6,7) then

i := i + interval '1 day';

end if;

end loop;

return i+x1+x2;

end;

$$

language plpgsql strict;

接下来就可以使用这个函数满足你的需求postgres=> create table tbl(username name, begin_time timestamp, end_time timestamp);

CREATE TABLE

postgres=> insert into tbl values ('a','2012-10-28 08:30','2012-11-05 17:30');

INSERT 0 1

postgres=> insert into tbl values ('b','2012-11-02 08:30', '2012-11-07 13:30');

INSERT 0 1

postgres=> insert into tbl values ('a','2012-11-08 13:30', '2012-11-09 17:30');

INSERT 0 1

postgres=> select sum(minus_weekend(begin_time,end_time)) from tbl where username='a' ;

sum

-----------------

5 days 45:30:00

(1 row)