Laravel操作MongoDB时whereDate/whereTime筛选日期时间返回空问题

问题场景 #

通过composer安装依赖包jenssegers/mongodb操作MongoDB，集合中日期字段存储为ISODate类型，示例：
ISODate("2020-05-28T06:34:23.000Z")
需要同时按日期范围、当日时间范围筛选文档，以下三种常规写法在MySQL环境可正常返回结果，在MongoDB环境返回空：
写法一：

/** @var $db Jenssegers\Mongodb\Query\Builder */ $db = DB::connection('mgdb')->table('test');

$db
->whereDate('dateShot', '>=', Carbon::parse('2020-05-28'))
->whereDate('dateShot', '<=', Carbon::parse('2020-05-29'))
->whereTime('dateShot', '>=', Carbon::parse('06:00'))
->whereTime('dateShot', '<=', Carbon::parse('08:00'))

写法二：

$db
->whereDate('dateShot', '>=', '2020-05-28')
->whereDate('dateShot', '<=', '2020-05-29')
->whereTime('dateShot', '>=', '06:00')
->whereTime('dateShot', '<=', '08:00')

写法三：

$db
->where('dateShot', '>=', '2020-05-28 00:00:00')
->where('dateShot', '<=', '2020-05-29 23:59:59')
->whereTime('dateShot', '>=', '06:00')
->whereTime('dateShot', '<=', '08:00')

失败原因 #

• 3.8以下版本的jenssegers/mongodb查询构造器未适配whereDate、whereTime方法，两个方法默认生成MySQL专属的DATE()、TIME()函数语法，MongoDB无法解析
• 直接传入字符串格式时间时，MongoDB不会自动做类型转换，和BSON日期类型判定为不相等，匹配不到结果
• MongoDB存储的ISODate为UTC时区，直接传入本地时区（如东八区）的Carbon对象未做时区转换，时间范围会偏移对应时差，导致筛选范围错位
• 用23:59:59作为日期结束边界，会漏掉当日23:59:59到次日0点之间毫秒级的时间数据

注意：东八区场景下如果不做UTC转换，时间范围会整体偏移8小时，比如要查本地时间6点到8点的数据，实际查询的是UTC时间6点到8点，对应本地时间14点到16点，自然返回空结果

正确实现方案 #

方案1：原生运算符实现（兼容所有版本，性能最优） #

不依赖框架封装的日期查询方法，直接通过MongoDB原生聚合表达式实现筛选，代码可直接复用：

/** @var $db Jenssegers\Mongodb\Query\Builder */
$db = DB::connection('mgdb')->table('test');

// 统一将查询边界转为UTC时区
$dateStart = Carbon::parse('2020-05-28')->tz('UTC');
$dateEnd = Carbon::parse('2020-05-29')->endOfDay()->tz('UTC');
// 计算当日时间的毫秒偏移（UTC时间基准）
$timeStartMs = Carbon::parse('06:00')->tz('UTC')->secondsSinceMidnight() * 1000;
$timeEndMs = Carbon::parse('08:00')->tz('UTC')->secondsSinceMidnight() * 1000;

$result = $db->whereRaw([
    '$expr' => [
        '$and' => [
            // 日期范围匹配
            ['$gte' => ['$dateShot', new MongoDB\BSON\UTCDateTime($dateStart)]],
            ['$lte' => ['$dateShot', new MongoDB\BSON\UTCDateTime($dateEnd)]],
            // 当日时间匹配：取日期时间戳模一天的总毫秒数，得到当日毫秒偏移
            ['$gte' => [
                ['$mod' => [['$toLong' => '$dateShot'], 86400 * 1000]],
                $timeStartMs
            ]],
            ['$lte' => [
                ['$mod' => [['$toLong' => '$dateShot'], 86400 * 1000]],
                $timeEndMs
            ]]
        ]
    ]
])->get();

如果当前版本的包已经支持Carbon自动转换为UTCDateTime类型，日期范围部分可以简化写法：

$db->where('dateShot', '>=', $dateStart)
   ->where('dateShot', '<=', $dateEnd)

方案2：高版本包适配写法 #

如果使用jenssegers/mongodb 3.8及以上版本，框架已经对whereDate、whereTime做了MongoDB适配，只需要确保传入的时间值统一转为UTC时区即可：

$db
->whereDate('dateShot', '>=', Carbon::parse('2020-05-28')->tz('UTC'))
->whereDate('dateShot', '<=', Carbon::parse('2020-05-29')->tz('UTC'))
->whereTime('dateShot', '>=', Carbon::parse('06:00')->tz('UTC'))
->whereTime('dateShot', '<=', Carbon::parse('08:00')->tz('UTC'))
->get();

避坑提示 #

• 所有和MongoDB ISODate字段做对比的时间值，必须统一转为UTC时区，避免时区偏移导致结果错误
• 不要直接传格式化后的时间字符串和ISODate字段对比，类型不匹配会导致查询失效
• 日期范围结束边界优先用endOfDay()获取当日最后一毫秒，或者用次日0点配合<运算符做范围判断，避免漏数据

内容的提问来源于stack exchange，提问作者Dương Hải