MariaDB中WHERE子句筛选每月首尾日期的查询问题

我们尝试通过JOIN查询获取每月第一天和最后一天的所有记录，使用环境为PHPMyAdmin下的MariaDB 10.4，以下是各次查询及结果：

查询1 #

SELECT storing_eigenschappen.date, storing_eigenschappen.traplift_id, storing_warnings.raw_warning, storing_errors.raw_error
FROM storing_eigenschappen 
LEFT OUTER JOIN storing_warnings ON storing_eigenschappen.traplift_id = storing_warnings.traplift_id AND storing_eigenschappen.date = storing_warnings.date 
LEFT OUTER JOIN storing_errors ON storing_eigenschappen.traplift_id = storing_errors.traplift_id AND storing_eigenschappen.date = storing_errors.date 
WHERE storing_eigenschappen.traplift_id LIKE "UP-%" 
    AND DAY(storing_eigenschappen.date) = 1 
    AND DAY(storing_eigenschappen.date) = LAST_DAY(storing_eigenschappen.date)

结果1：未找到任何记录

查询2 #

SELECT storing_eigenschappen.date, storing_eigenschappen.traplift_id, storing_warnings.raw_warning, storing_errors.raw_error 
FROM storing_eigenschappen 
LEFT OUTER JOIN storing_warnings ON storing_eigenschappen.traplift_id = storing_warnings.traplift_id AND storing_eigenschappen.date = storing_warnings.date 
LEFT OUTER JOIN storing_errors ON storing_eigenschappen.traplift_id = storing_errors.traplift_id AND storing_eigenschappen.date = storing_errors.date 
WHERE storing_eigenschappen.traplift_id LIKE "TL-%" 
    AND (
        DAY(storing_eigenschappen.date) = 1 OR 
        DAY(storing_eigenschappen.date) = LAST_DAY(storing_eigenschappen.date)
    )

结果2：
Array ( [date] => 2024-06-01 14:08:25.000000 [traplift_id] => TL-123456 [raw_warning] => [raw_error] => )

查询3 #

SELECT storing_eigenschappen.date, storing_eigenschappen.traplift_id, storing_warnings.raw_warning, storing_errors.raw_error 
FROM storing_eigenschappen 
LEFT OUTER JOIN storing_warnings ON storing_eigenschappen.traplift_id = storing_warnings.traplift_id AND storing_eigenschappen.date = storing_warnings.date 
LEFT OUTER JOIN storing_errors ON storing_eigenschappen.traplift_id = storing_errors.traplift_id AND storing_eigenschappen.date = storing_errors.date 
WHERE storing_eigenschappen.traplift_id LIKE "UP-%" 
     AND (
        DAY(storing_eigenschappen.date) = 1 OR 
        DAY(storing_eigenschappen.date) = 31 OR 
        DAY(storing_eigenschappen.date) = 30 OR 
        DAY(storing_eigenschappen.date) = 29
    )

结果3：
Array ( [date] => 2024-06-01 14:08:25.000000 [traplift_id] => TL-123456 [raw_warning] => [raw_error] => ) Array ( [date] => 2024-06-30 14:08:52.000000 [traplift_id] => TL-123456 [raw_warning] => [raw_error] => ) Array ( [date] => 2024-06-29 14:41:22.000000 [traplift_id] => TL-123456 [raw_warning] => [raw_error] => )

查询4 #

尝试添加以下语句获取每月最后一天：

DATE_SUB(DATE_ADD(DATE_ADD(storing_eigenschappen.date, INTERVAL 1 MONTH), INTERVAL -DAY(DATE_ADD(storing_eigenschappen.date, INTERVAL 1 MONTH)) DAY), INTERVAL 1 DAY)

结果4：MariaDB提示语法错误

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