やりたいこと
2023-01-01 7:00 ～ 2023-01-03 23:00 のように、日を跨いだ期間を日付ごとに分割したい。
つまり、次ようなテーブルを得たい。
| Start | End |
|---|---|
| 2023-01-01 07:00 | 2023-01-02 00:00 |
| 2023-01-02 00:00 | 2023-01-03 00:00 |
| 2023-01-03 00:00 | 2023-01-03 23:00 |
コード
(start as nullable any, end as nullable any, optional culture as nullable text) as table =>
let
startDt = DateTime.From(start, culture),
endDt = DateTime.From(end, culture),
separators = List.Generate(
() => Date.StartOfDay(Date.AddDays(startDt, 1)),
each startDt < _ and _ < endDt,
each Date.AddDays(_, 1)
),
starts = {startDt} & separators,
ends = separators & {endDt},
durations = List.Zip({starts, ends}),
schema = type table [Start = datetime, End = datetime]
in
if List.MatchesAll({startDt, endDt}, each _ = null) then
#table(schema, {})
else if List.Contains({startDt, endDt}, null) then
error "Cannot calculate the duration as either the start or end is null."
else if startDt > endDt then
error "start must be before end."
else
#table(schema, durations)