面试题：MySQL日期和时间函数在复杂数据分析中的运用

Question

在一张员工考勤记录表attendance中，有签到时间（check_in_time，DATETIME类型）和签退时间（check_out_time，DATETIME类型）字段。要求统计出每个员工每天的实际工作时长，并且要考虑到可能存在签到或签退时间记录不完整（即其中一个字段为NULL）的情况，用MySQL日期和时间处理函数实现该需求，写出SQL查询语句及必要的逻辑说明。

Answer 1

1. SQL查询语句：

SELECT 
    employee_id, 
    DATE(check_in_time) AS work_date, 
    CASE 
        WHEN check_in_time IS NULL OR check_out_time IS NULL THEN NULL 
        ELSE TIMESTAMPDIFF(SECOND, check_in_time, check_out_time) 
    END AS work_duration_seconds
FROM 
    attendance;

2. 逻辑说明：
   • SELECT employee_id, DATE(check_in_time) AS work_date：从考勤记录表attendance中选择员工ID和签到时间的日期部分，作为工作日期。
   • CASE WHEN check_in_time IS NULL OR check_out_time IS NULL THEN NULL ELSE TIMESTAMPDIFF(SECOND, check_in_time, check_out_time) END AS work_duration_seconds：
     • 使用CASE语句来处理签到或签退时间可能为NULL的情况。
     • 如果签到时间check_in_time或签退时间check_out_time为NULL，则返回NULL，表示工作时长无法确定。
     • 否则，使用TIMESTAMPDIFF函数计算签退时间与签到时间之间的时间差，单位为秒，以此作为实际工作时长。这里以秒为单位方便统一计算，实际应用中可根据需求转换为小时、分钟等其他时间单位。