Background
我一直在研究一些報告視圖，這些視圖需要multi-day個工作班次，并且應該根據數據進行一些計算，但我有點被困在這里。一次典型的輪班是3個日歷日，通常是1half-day和兩個完整的工作日，或者是一周2half-days（結束和開始）和5個完整的工作日。
Specifications
我對一整天和half-day有以下規定。這些規則是以監管為基礎的，不能更改。
- 2 half-days != 1full-day，兩半更“有價值”
- 給定
started_atiso datetime和end_atiso datetime - 我想得到兩個數字，full_days和half_days
- 半天是指從12點或之后開始的范圍開始的一天，以及在19點之前結束的范圍結束的一天
- 全天是指范圍內的一天（傳統的24小時），范圍開始時為12點之前，范圍結束時為19點或19點之后
我認為，在視圖中，最好是每full-day和half-day一行，或者將half_days和full_days作為兩個獨立列的聚合行，將其與其他視圖連接起來。
Simplified model
我簡化了數據模型，省去了不必要的列。
create table if not exists [trip]
(
trip_id integer
constraint trip_pk
primary key,
started_at text default (datetime('now')),
end_at text default (datetime('now'))
);
我有點糾結于該如何設計這個查詢。一個簡單的時間差是行不通的。
SQLFiddle處理樣本數據和答案：http://sqlfiddle.com/#!5/de7551/2
您可以通過計算日跨度（輪班跨度天數）的CTE來解決這個問題。由于半天總是1, 2或0（只發生在結束和開始），我們實際上不需要每天單獨考慮。
您可以使用julianday以數字形式獲取日期，但是julian日期從中午開始，因此您需要減去0.5以獲得用于計算的“實際”日期。如果結束時間晚于每一天的開始時間，則確定結束日，以避免較長的跨度，并將結果匯總，將部分天數作為跨度日。
此時，我們可以通過檢查結束和開始來計算半天的天數。為了得到完整天數，我們只需從結果中減去半天。