clickhouse 时间处理相关函数 WorkTimeBetween， WorkDaysBetween ，

--  DATE_MATCH
DROP FUNCTION DateMatch;

CREATE FUNCTION DateMatch AS(column, YEARS, QUARTERS, MONTHS, WEEKS, DAYS) -> if(isNotNull(YEARS), toYear(column, 'Asia/Shanghai') IN YEARS, true)
    AND if(isNotNull(QUARTERS), toQuarter(column, 'Asia/Shanghai') in QUARTERS, true) AND
    AND if(isNotNull(WEEKS), toWeek(column, 3) in WEEKS, true)
    AND if(isNotNull(DAYS), toDayOfMonth(column, 'Asia/Shanghai') in DAYS, true);

SELECT *
FROM CRM_TEST
WHERE DateMatch(`创建时间`, [2021], [3], NULL, [5], NULL)
LIMIT 500;

-- ROUND_DAY

DROP FUNCTION RoundDay;
CREATE FUNCTION RoundDay AS(date, tz) -> toStartOfDay(date, tz);

SELECT RoundDay(`创建时间`, 'Asia/Shanghai')
FROM CRM_TEST
LIMIT 10;

-- ROUND_WEEK
DROP FUNCTION RoundWeek;
CREATE FUNCTION RoundWeek AS(date, tz) -> toDateTime(toStartOfWeek(date, 3, tz));

SELECT RoundWeek(`创建时间`, 'Asia/Shanghai')
FROM CRM_TEST
LIMIT 10;

-- ROUND_QUARTER
DROP FUNCTION RoundQuarter;
CREATE FUNCTION RoundQuarter AS(date, tz) -> toDateTime(toStartOfQuarter(date, tz));

SELECT RoundQuarter(`创建时间`, 'Asia/Shanghai')
FROM CRM_TEST
LIMIT 10;

-- ADD WORK DAYS
DROP FUNCTION AddWorkDay;
CREATE FUNCTION AddWorkDay AS(start_date, num_of_days, drange) -> if(num_of_days >= 0,
    (arrayMap(x->toDate(start_date) + x + 1 NOT IN (SELECT timestamp FROM _chinese_holiday), range(if(isNotNull(drange), drange, 1000))) AS __map_days,
         arrayCumSum(__map_days) AS __cum__days, arrayFirstIndex(x -> x >= num_of_days, __cum__days) AS __add_index,
     addDays(start_date, __add_index)).4,
         (arrayMap(x->toDate(start_date) - x - 1 NOT IN (SELECT timestamp FROM _chinese_holiday), range(if(isNotNull(drange), drange, 1000))) AS __map_days_1,
         arrayCumSum(__map_days_1) AS __cum__days_1, arrayFirstIndex(x -> x >= abs(num_of_days), __cum__days_1) AS __add_index_1,
     subtractDays(start_date, __add_index_1)));

SELECT `创建时间`, AddWorkDay(`创建时间`, -5, 10)
FROM CRM_TEST
LIMIT 10;

- drop function WorkDaysBetween
create function WorkDaysBetween as (t1,t2) ->
(
select
        toDate(t2) - toDate(t1) + 1 -
        (
                select
                        count()
                from
                        `_chinese_holiday`
                where
                        `timestamp` >= toDate(t1)
                        and `timestamp` <= toDate(t2)
        )
);

select WorkDaysBetween('2023-09-28 10:00:00', '2023-10-08');

create function WorkTimeOfDay as (t1,t2, work_start,work_stop) ->
(
        select
                if(toDate(t1)!=toDate(t2) or t2<t1 or work_start > work_stop,0,
                        if((select count() from `_chinese_holiday` where `timestamp` = toDate(t1))>0,0,
                                dateDiff('second',greatest(t1,dateAdd(second,work_start,toDate(t1))),least(t2,dateAdd(second,work_stop,toDate(t2))))
                        )
                )
)

                select WorkTimeOfDay(toDateTime('2023-09-08 10:00:00'),toDateTime('2023-09-08 15:16:00'),8*60*60,18*60*60),5*3600+16*60 --working day

                select WorkTimeOfDay(toDateTime('2023-09-02 10:00:00'),toDateTime('2023-09-02 15:16:00'),0 --not working day

                select WorkTimeOfDay(toDateTime('2023-09-08 01:00:00'),toDateTime('2023-09-08 15:16:00'),8*60*60,18*60*60),7*3600+16*60 --working day,start at 8

                select WorkTimeOfDay(toDateTime('2023-09-08 10:00:00'),toDateTime('2023-09-08 19:16:00'),8*60*60,18*60*60),8*3600 --working day,start at 8

                select WorkTimeOfDay(toDateTime('2023-09-08 03:12:33'),toDateTime('2023-09-08 19:16:00'),8*60*60,18*60*60),10*3600 --working day,start at 8

                --drop function apqlWorkTimeBetween;

create function WorkTimeBetween as (t1,t2, work_start,work_stop) ->
(
        select
                if(t2<t1 or work_start > work_stop,0,
                        if(toDate(t1) = toDate(t2),apqlWorkTimeOfDay(t1,t2, work_start,work_stop),
                                apqlWorkTimeOfDay(t1,dateAdd(second,86399,toDate(t1)), work_start,work_stop)
                                +apqlWorkTimeOfDay(toDate(t2),t2, work_start,work_stop)
                                + if(toDate(t2) - toDate(t1)<2,0,(if(apqlWorkDaysBetween(t2,t1)-2>0,apqlWorkDaysBetween(t2,t1)-2,0))*(work_stop - work_start))
                        )
                )
)

                select WorkTimeBetween(toDateTime('2023-09-08 10:00:00'),toDateTime('2023-09-08 15:16:00'),8*60*60,18*60*60),5*3600+16*60 --working day

                select WorkTimeBetween(toDateTime('2023-09-02 10:00:00'),toDateTime('2023-09-02 15:16:00'),8*60*60,18*60*60),0 --not working day

                select WorkTimeBetween(toDateTime('2023-09-08 01:00:00'),toDateTime('2023-09-08 15:16:00'),8*60*60,18*60*60),7*3600+16*60 --working day,start at 8

                select WorkTimeBetween(toDateTime('2023-09-08 10:00:00'),toDateTime('2023-09-08 19:16:00'),8*60*60,18*60*60),8*3600 --working day,start at 8

                select WorkTimeBetween(toDateTime('2023-09-08 03:12:33'),toDateTime('2023-09-08 19:16:00'),8*60*60,18*60*60),10*3600 --working day,start at 8

                select WorkTimeBetween(toDateTime('2023-09-08 10:00:00'),toDateTime('2023-09-09 15:16:00'),8*60*60,18*60*60),(18-10)*3600

                select WorkTimeBetween(toDateTime('2023-09-08 10:00:00'),toDateTime('2023-09-11 15:16:00'),8*60*60,18*60*60),(18-10)*3600

                select WorkTimeBetween(toDateTime('2023-09-08 10:00:00'),toDateTime('2023-09-11 15:00:00'),8*60*60,18*60*60),(18-10 + 15-8)*3600

                select WorkTimeBetween(toDateTime('2023-09-08 03:00:00'),toDateTime('2023-09-11 19:00:00'),8*60*60,18*60*60),(18-8 + 18-8)*3600