sql - 从 SQL 中的日期范围获取工作日数，用于没有扩展功能的 sqlite

Question

我有一个包含开始和结束列的表，其中包含日期范围的开始和结束日期。我想从每个时间范围内使用纯 sqlite3 功能选择工作日数(不包括周六和周日的天数)。有没有办法做到这一点？

我检查了这个问题的几个答案，但很多人似乎都使用像 DATEDIFF 这样的函数。在 sqlite 中不可用。

示例:开始日期“2015-09-19”和结束日期“2015-09-22”应为两个工作日。

Answer 1

如果你有这样一张 table ，你的工作会很容易。

-- I'm going to drop this table later.
create table calendar (
  cal_date date primary key
);

insert into calendar values ('2015-09-18');
insert into calendar values ('2015-09-19');
insert into calendar values ('2015-09-20');
insert into calendar values ('2015-09-21');
insert into calendar values ('2015-09-22');
insert into calendar values ('2015-09-23');

-- Weekdays
select cal_date, strftime('%w', cal_date) day_of_week
from calendar
where day_of_week between 1 and 5;

cal_date    day_of_week----------  -----------2015-09-18  5          2015-09-21  1          2015-09-22  2          2015-09-23  3          

-- Weekdays between two dates
select cal_date, strftime('%w', cal_date) day_of_week
from calendar
where day_of_week between 1 and 5
  and cal_date between '2015-09-19' and '2015-09-22';

cal_date    day_of_week----------  -----------2015-09-21  1          2015-09-22  2          

-- Count them
select count(*) num_days
from (select cal_date, strftime('%w', cal_date) day_of_week
      from calendar
      where day_of_week between 1 and 5
        and cal_date between '2015-09-19' and '2015-09-22') x;

num_days  ----------2         

So let's manufacture a table like that from start and end dates. To do that, we need a table of integers.

create table integers (
  n integer primary key
);

insert into integers values (0);
insert into integers values (1);
insert into integers values (2);
insert into integers values (3);
insert into integers values (4);
insert into integers values (5);

您插入的行数很重要。最低开始日期加上最大整数通常需要返回晚于最晚结束日期的结果。通常，您需要多于五行。你已经被警告过了。

create table test (start date, end date);
insert into test values('2015-09-19','2015-09-22');
insert into test values('2015-09-19','2015-09-19');

-- Calendar from date range. Look at your query plan.
-- There might be a better way to do this. I think you're 
-- going to have to use a cross join with SQLite, though.
select distinct date(start, '+' || n || ' days') as cal_date
from test, integers 
order by cal_date;

cal_date  ----------2015-09-192015-09-202015-09-212015-09-222015-09-232015-09-242015-09-25

Let's expand that a little to include the day of the week, and make it a view.

-- Calendar view from date range
drop table calendar;
create view calendar as 
select distinct date(start, '+' || n || ' days') as cal_date,
       case strftime('%w', date(start, '+' || n || ' days')) 
       when 0 then 'Sun'
       when 1 then 'Mon'
       when 2 then 'Tue'
       when 3 then 'Wed'
       when 4 then 'Thu'
       when 5 then 'Fri'
       when 6 then 'Sat'
       end as day_of_week
from test, integers;

现在我们可以使用连接和聚合函数来计算工作日数。

select start, end, count(cal_date) as num_weekdays
from test
left join calendar on cal_date between start and end 
             and day_of_week in ('Mon', 'Tue', 'Wed', 'Thu', 'Fri')
group by start, end
order by start, end;

开始结束 num_weekdays
---------- ---------- ------------
2015-09-19 2015-09-19 0
2015-09-19 2015-09-22 2