MySQL中如何对比分开存储的日期时间与当前时间筛选预约记录

方案选型建议 #

优先选择SQL侧做条件过滤后返回数据，原因两点：

• 避免全量数据从数据库传输到PHP服务，减少网络IO开销
• 不需要在PHP侧加载全量数据占用内存，数据量越大性能优势越明显，MySQL本身的时间计算、排序效率远高于PHP侧遍历处理

原SQL失效原因 #

你编写的SQL中STR_TO_DATE的格式占位符和实际存储格式不匹配：

• 日期存储格式为日/月/年，不是你写的年-月-日格式
• 时间存储格式为12小时制带AM/PM后缀，不是你写的24小时制格式

修正后的MySQL查询语句 #

即将到来的预约 #

$sql = "SELECT * FROM p_appointment 
        WHERE UNIX_TIMESTAMP(STR_TO_DATE(CONCAT(date,' ',time), '%d/%m/%Y %h:%i %p')) >= UNIX_TIMESTAMP(NOW()) 
        ORDER BY STR_TO_DATE(CONCAT(date,' ',time), '%d/%m/%Y %h:%i %p') ASC";

注：排序使用合并后的完整时间，避免同日期下不同时间的顺序错误

历史预约 #

$sql = "SELECT * FROM p_appointment 
        WHERE UNIX_TIMESTAMP(STR_TO_DATE(CONCAT(date,' ',time), '%d/%m/%Y %h:%i %p')) < UNIX_TIMESTAMP(NOW()) 
        ORDER BY STR_TO_DATE(CONCAT(date,' ',time), '%d/%m/%Y %h:%i %p') DESC";

性能优化方案（可选） #

如果表数据量超过1万条，建议新增存储生成列存储合并后的完整时间，再建索引提升查询效率：

-- 新增生成列
ALTER TABLE p_appointment ADD COLUMN full_datetime DATETIME 
GENERATED ALWAYS AS (STR_TO_DATE(CONCAT(date,' ',time), '%d/%m/%Y %h:%i %p')) STORED;
-- 建索引
CREATE INDEX idx_full_datetime ON p_appointment(full_datetime);

后续查询可以简化为：

// 即将到来的预约
$sql = "SELECT * FROM p_appointment WHERE full_datetime >= NOW() ORDER BY full_datetime ASC";
// 历史预约
$sql = "SELECT * FROM p_appointment WHERE full_datetime < NOW() ORDER BY full_datetime DESC";

PHP侧实现方案（适用于必须全量拉取数据再拆分的场景） #

// 拉取全量预约数据
$allAppointments = $pdo->query("SELECT * FROM p_appointment")->fetchAll(PDO::FETCH_ASSOC);
$upcoming = [];
$history = [];
$now = time();

foreach ($allAppointments as $item) {
    $datetimeObj = DateTime::createFromFormat('d/m/Y h:i A', $item['date'].' '.$item['time']);
    $timestamp = $datetimeObj->getTimestamp();
    if ($timestamp >= $now) {
        $upcoming[] = $item;
    } else {
        $history[] = $item;
    }
}

// 对两个列表分别排序
usort($upcoming, function($a, $b) {
    $t1 = DateTime::createFromFormat('d/m/Y h:i A', $a['date'].' '.$a['time'])->getTimestamp();
    $t2 = DateTime::createFromFormat('d/m/Y h:i A', $b['date'].' '.$b['time'])->getTimestamp();
    return $t1 - $t2;
});

usort($history, function($a, $b) {
    $t1 = DateTime::createFromFormat('d/m/Y h:i A', $a['date'].' '.$a['time'])->getTimestamp();
    $t2 = DateTime::createFromFormat('d/m/Y h:i A', $b['date'].' '.$b['time'])->getTimestamp();
    return $t2 - $t1;
});

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