如何计算两个Unix时间戳间的业务秒数（含跨周末调整）

需求说明 #

给定转换后的日期时间戳：

Start - 1668105400.814 - Thursday, November 10, 2022 12:36 PM
End   - 1668444427.195 - Monday, November 14, 2022 10:47 AM

需要计算调整为「业务日」的秒数：暂不强制考虑节假日，但若时间跨周末，需从起止时间差中扣除172800秒（2天时长）。

数据集示例 #

type         created_at           emitted_at
assign       1668105400.814       1668105400.814
archive      1668105400.814       1668444427.195

当前原始时差计算逻辑 #

通过自定义列计算原始时间差，仅处理archive类型数据：

_Created_To_Archive_Handle_Time = IF(Front_Email_Stats[type]="archive", ROUND((Front_Email_Stats[emitted_at] - Front_Email_Stats[conversation.created_at]), 1), BLANK())

现有朴素实现方案 #

通过判断起止时间的星期数决定是否扣除周末时长：

_Created_To_Archive_Handle_Time = IF(Front_Email_Stats[type]="archive", IF(AND(WEEKDAY(Front_Email_Stats[_created_at_datetime_custom_column]) < 7, WEEKDAY(Front_Email_Stats[_emitted_at_datetime_custom_column]) > 1), ROUND((Front_Email_Stats[emitted_at] - Front_Email_Stats[conversation.created_at]), 1) - 172800, ROUND((Front_Email_Stats[emitted_at] - Front_Email_Stats[conversation.created_at]), 1)), BLANK())

优化后的DAX方案 #

_Created_To_Archive_Handle_Time = 
VAR IsArchiveType = Front_Email_Stats[type] = "archive"
VAR RawSeconds = ROUND(Front_Email_Stats[emitted_at] - Front_Email_Stats[conversation.created_at], 1)
VAR StartDate = Front_Email_Stats[_created_at_datetime_custom_column]
VAR EndDate = Front_Email_Stats[_emitted_at_datetime_custom_column]
VAR DaysDiff = DATEDIFF(StartDate, EndDate, DAY)
VAR CrossesWeekend = 
    AND(
        WEEKDAY(StartDate, 2) <= 5, // 周一=1至周五=5，判断开始时间在工作日内
        WEEKDAY(EndDate, 2) >= 1,
        DaysDiff >= 3 // 确保时间跨度至少覆盖一个周末
    )
VAR AdjustedSeconds = IF(CrossesWeekend, RawSeconds - 172800, RawSeconds)
RETURN
    IF(IsArchiveType, AdjustedSeconds, BLANK())

优化要点 #

• 用VAR拆分逻辑单元，公式结构更清晰，便于后续修改维护
• 采用WEEKDAY(,2)参数，将周一设为1、周日设为7，更贴合业务日判断习惯
• 结合DATEDIFF计算天数差，精准识别跨周末场景，避免误判短时间跨天但未到周末的情况

内容的提问来源于stack exchange，提问作者wildcat89