PHP mongo聚合项目到日期字段 - php mongo aggregate project to date field

php mongo aggregate project to date field

本文关键字：日期字段项目 mongo PHP | 更新日期: 2023-09-27

在php中我有以下mongo聚合函数:

array(
    array(
        '$match' => array(
            "i" =>  array('$in' => $valRefId),
            "date" => array('$gt'=> $mongotimestart , '$lte'=> $mongotimeend),
        ),
    ),
    array(
        '$project' => array(
            "i" => '$i',
            "v" => '$v',
            "datumpje" => array('$add'=>array('$date',$timezone_date)),
            "year" => array('$year' =>array('$add'=>array('$date',$timezone_date))),
            "month" => array('$month'=>array('$add'=>array('$date',$timezone_date))),
            "day" => array('$dayOfMonth'=>array('$add'=>array('$date',$timezone_date))),
            "hour" => array('$hour'=> array('$add'=>array('$date',$timezone_date))),
        )
    ),
    array(
        '$group' => array(
            '_id' => array(
                "valRefId" => '$i',
                "year" => '$year',
                "month" => '$month',
                "day" => '$day',
                "hour" => '$hour',
            ),
            "MaxValue" => array('$max' => '$v'),
            "MinValue" => array('$min' => '$v'),
            "Gemiddelde" => array('$avg' => '$v'),
            "Aantal" => array('$sum' => 1),
        )
    ),
    array(
        '$sort' => array(
            "_id.valRefId" => 1,
            "_id.year" => 1,
            "_id.month" => 1,
            "_id.day" => 1,
            "_id.hour" => 1,
        )
    ),
    array(
        '$project' => array(
            "date" => array(
                '$concat' => array(array('$substr' => array('$_id.year',0,4)),
                    "-",
                    array('$substr' => array('$_id.month',0,2)),
                    "-",
                    array('$substr' => array('$_id.day',0,2)),
                    " ",
                    array('$substr' => array('$_id.hour',0,2)),
                    ":00:00",
                ),
            ),
            "MaxValue" => '$MaxValue',
            "MinValue" => '$MinValue',
            "Gemiddelde" => '$Gemiddelde',
            "Aantal" => '$Aantal',
        )
    ),
);

该函数的意义是获取 $i 在给定数组( $valRefId )中的maxValue, MinValue, Average和记录数，然后按 $i ， $year ， $month ， $day 和 $hour 分组。

作为结果，我现在有"_id"。"日期"、"执行"、"minValue"、"Gemiddelde"、"Aantal"。

我的问题是现在，是否有可能返回"日期"字段不是字符串类型，而是作为日期类型(或时间戳类型)?

这是可能的。您可以使用 $first 累加器返回 $group 管道中的日期字段，如下所示:

$pipeline = array(
    array(
        '$match' => array(
            "i" =>  array('$in' => $valRefId),
            "date" => array('$gt'=> $mongotimestart , '$lte'=> $mongotimeend),
        ),
    ),
    array(
        '$project' => array(
            "i" => 1,
            "v" => 1,
            "datumpje" => array('$add'=>array('$date',$timezone_date)),
            "year" => array('$year' =>array('$add'=>array('$date',$timezone_date))),
            "month" => array('$month'=>array('$add'=>array('$date',$timezone_date))),
            "day" => array('$dayOfMonth'=>array('$add'=>array('$date',$timezone_date))),
            "hour" => array('$hour'=> array('$add'=>array('$date',$timezone_date))),
        )
    ),
    array(
        '$group' => array(
            '_id' => array(
                "valRefId" => '$i',
                "year" => '$year',
                "month" => '$month',
                "day" => '$day',
                "hour" => '$hour',
            ),
            "date" => array('$first' => '$datumpje'),
            "MaxValue" => array('$max' => '$v'),
            "MaxValue" => array('$max' => '$v'),
            "MinValue" => array('$min' => '$v'),
            "Gemiddelde" => array('$avg' => '$v'),
            "Aantal" => array('$sum' => 1),
        )
    ),
    array(
        '$sort' => array(
            "_id.valRefId" => 1,
            "_id.year" => 1,
            "_id.month" => 1,
            "_id.day" => 1,
            "_id.hour" => 1,
        )
    )
);