MongoDB 格式化时间

语法db.COLLECTION_NAME.aggregate(AGGREGATE_OPERATION)实例集合中的数据如下// 1{"_id": ObjectId("60f0d9913e2e8152fb6c3aa0"),"identificationType": NumberInt("1"),"byThermometryUnit": NumberInt("0"),"byIsAbnomalTempe

Yweir

3953人浏览 · 2021-08-09 09:46:42

Yweir · 2021-08-09 09:46:42 发布

语法

db.COLLECTION_NAME.aggregate(AGGREGATE_OPERATION)

实例

集合中的数据如下

// 1
{
    "_id": ObjectId("60f0d9913e2e8152fb6c3aa0"),
    "identificationType": NumberInt("1"),
    "byThermometryUnit": NumberInt("0"),
    "byIsAbnomalTemperature": NumberInt("0"),
    "struTime": NumberInt("1626397071")
}

// 2
{
    "_id": ObjectId("60f0d9923e2e8152fb6c3aa1"),
    "identificationType": NumberInt("1"),
    "byThermometryUnit": NumberInt("0"),
    "byIsAbnomalTemperature": NumberInt("0"),
    "struTime": NumberInt("1626397072")
}

// 3
{
    "_id": ObjectId("60f0db043e2e8152fb6c3aa2"),
    "identificationType": NumberInt("1"),
    "byThermometryUnit": NumberInt("0"),
    "byIsAbnomalTemperature": NumberInt("0"),
    "struTime": NumberInt("1626397442")
}

// 4
{
    "_id": ObjectId("60f0dd2d3e2e8152fb6c3aa3"),
    "identificationType": NumberInt("1"),
    "byThermometryUnit": NumberInt("0"),
    "byIsAbnomalTemperature": NumberInt("0"),
    "struTime": NumberInt("1626397995")
}

// 5
{
    "_id": ObjectId("60f0ddbc3e2e8152fb6c3aa4"),
    "identificationType": NumberInt("1"),
    "byThermometryUnit": NumberInt("0"),
    "byIsAbnomalTemperature": NumberInt("0"),
    "struTime": NumberInt("1626398138")
}

// 6
{
    "_id": ObjectId("60f0e1073e2e8152fb6c3aa5"),
    "identificationType": NumberInt("1"),
    "byThermometryUnit": NumberInt("0"),
    "byIsAbnomalTemperature": NumberInt("0"),
    "struTime": NumberInt("1626398981")
}

// 7
{
    "_id": ObjectId("60f0e6403e2e8152fb6c3aa6"),
    "identificationType": NumberInt("1"),
    "byThermometryUnit": NumberInt("0"),
    "byIsAbnomalTemperature": NumberInt("0"),
    "struTime": NumberInt("1626400318")
}

// 8
{
    "_id": ObjectId("60f0f5cd3e2e8152fb6c3aa7"),
    "identificationType": NumberInt("1"),
    "byThermometryUnit": NumberInt("0"),
    "byIsAbnomalTemperature": NumberInt("0"),
    "struTime": NumberInt("1626404300")
}

// 9
{
    "_id": ObjectId("60f0f8e93e2e8152fb6c3aa8"),
    "identificationType": NumberInt("1"),
    "byThermometryUnit": NumberInt("0"),
    "byIsAbnomalTemperature": NumberInt("0"),
    "struTime": NumberInt("1626405095")
}

// 10
{
    "_id": ObjectId("60f102c73e2e8152fb6c3aa9"),
    "identificationType": NumberInt("1"),
    "byThermometryUnit": NumberInt("0"),
    "byIsAbnomalTemperature": NumberInt("0"),
    "struTime": NumberInt("1626407621")
}

// 11
{
    "_id": ObjectId("60f103c33e2e8152fb6c3aaa"),
    "identificationType": NumberInt("1"),
    "byThermometryUnit": NumberInt("0"),
    "byIsAbnomalTemperature": NumberInt("0"),
    "struTime": NumberInt("1626407873")
}

// 12
{
    "_id": ObjectId("60f115273e2e8152fb6c3aab"),
    "identificationType": NumberInt("1"),
    "byThermometryUnit": NumberInt("0"),
    "byIsAbnomalTemperature": NumberInt("0"),
    "struTime": NumberInt("1626412325")
}

// 13
{
    "_id": ObjectId("60f11b163e2e8152fb6c3aac"),
    "identificationType": NumberInt("1"),
    "byThermometryUnit": NumberInt("0"),
    "byIsAbnomalTemperature": NumberInt("0"),
    "struTime": NumberInt("1626413844")
}

// 14
{
    "_id": ObjectId("60f11eaa3e2e8152fb6c3aad"),
    "identificationType": NumberInt("1"),
    "byThermometryUnit": NumberInt("0"),
    "byIsAbnomalTemperature": NumberInt("0"),
    "struTime": NumberInt("1626414760")
}

操作语句

	因为存储的时间单位（秒），所以时间需要乘 1000 ，时区 +8

db.demo_log_20210809.aggregate(
    [
        {
            $project: {
                'datetime': {
                    $dateToString: {
                        format: "%Y-%m-%d %H:%M:%S",
                        date: {
                            
                            $add: [{
                                $add: [ISODate("1970-01-01T00:00:00Z"), {
                                    $multiply: ["$struTime", 1000]
                                }]
                            }, 28800000]
                        }
                    }
                },
				struTime:1,
                identificationType: 1,
                byThermometryUnit: 1,
                byIsAbnomalTemperature: 1,
                struTime: 1
            }
        }
    ]
)

查询结果

// 1
{
    "_id": ObjectId("60f0d9913e2e8152fb6c3aa0"),
    "identificationType": NumberInt("1"),
    "byThermometryUnit": NumberInt("0"),
    "byIsAbnomalTemperature": NumberInt("0"),
    "struTime": NumberInt("1626397071"),
    "datetime": "2021-07-16 08:57:51"
}

// 2
{
    "_id": ObjectId("60f0d9923e2e8152fb6c3aa1"),
    "identificationType": NumberInt("1"),
    "byThermometryUnit": NumberInt("0"),
    "byIsAbnomalTemperature": NumberInt("0"),
    "struTime": NumberInt("1626397072"),
    "datetime": "2021-07-16 08:57:52"
}

// 3
{
    "_id": ObjectId("60f0db043e2e8152fb6c3aa2"),
    "identificationType": NumberInt("1"),
    "byThermometryUnit": NumberInt("0"),
    "byIsAbnomalTemperature": NumberInt("0"),
    "struTime": NumberInt("1626397442"),
    "datetime": "2021-07-16 09:04:02"
}

// 4
{
    "_id": ObjectId("60f0dd2d3e2e8152fb6c3aa3"),
    "identificationType": NumberInt("1"),
    "byThermometryUnit": NumberInt("0"),
    "byIsAbnomalTemperature": NumberInt("0"),
    "struTime": NumberInt("1626397995"),
    "datetime": "2021-07-16 09:13:15"
}

// 5
{
    "_id": ObjectId("60f0ddbc3e2e8152fb6c3aa4"),
    "identificationType": NumberInt("1"),
    "byThermometryUnit": NumberInt("0"),
    "byIsAbnomalTemperature": NumberInt("0"),
    "struTime": NumberInt("1626398138"),
    "datetime": "2021-07-16 09:15:38"
}

// 6
{
    "_id": ObjectId("60f0e1073e2e8152fb6c3aa5"),
    "identificationType": NumberInt("1"),
    "byThermometryUnit": NumberInt("0"),
    "byIsAbnomalTemperature": NumberInt("0"),
    "struTime": NumberInt("1626398981"),
    "datetime": "2021-07-16 09:29:41"
}

// 7
{
    "_id": ObjectId("60f0e6403e2e8152fb6c3aa6"),
    "identificationType": NumberInt("1"),
    "byThermometryUnit": NumberInt("0"),
    "byIsAbnomalTemperature": NumberInt("0"),
    "struTime": NumberInt("1626400318"),
    "datetime": "2021-07-16 09:51:58"
}

// 8
{
    "_id": ObjectId("60f0f5cd3e2e8152fb6c3aa7"),
    "identificationType": NumberInt("1"),
    "byThermometryUnit": NumberInt("0"),
    "byIsAbnomalTemperature": NumberInt("0"),
    "struTime": NumberInt("1626404300"),
    "datetime": "2021-07-16 10:58:20"
}

// 9
{
    "_id": ObjectId("60f0f8e93e2e8152fb6c3aa8"),
    "identificationType": NumberInt("1"),
    "byThermometryUnit": NumberInt("0"),
    "byIsAbnomalTemperature": NumberInt("0"),
    "struTime": NumberInt("1626405095"),
    "datetime": "2021-07-16 11:11:35"
}

// 10
{
    "_id": ObjectId("60f102c73e2e8152fb6c3aa9"),
    "identificationType": NumberInt("1"),
    "byThermometryUnit": NumberInt("0"),
    "byIsAbnomalTemperature": NumberInt("0"),
    "struTime": NumberInt("1626407621"),
    "datetime": "2021-07-16 11:53:41"
}

// 11
{
    "_id": ObjectId("60f103c33e2e8152fb6c3aaa"),
    "identificationType": NumberInt("1"),
    "byThermometryUnit": NumberInt("0"),
    "byIsAbnomalTemperature": NumberInt("0"),
    "struTime": NumberInt("1626407873"),
    "datetime": "2021-07-16 11:57:53"
}

// 12
{
    "_id": ObjectId("60f115273e2e8152fb6c3aab"),
    "identificationType": NumberInt("1"),
    "byThermometryUnit": NumberInt("0"),
    "byIsAbnomalTemperature": NumberInt("0"),
    "struTime": NumberInt("1626412325"),
    "datetime": "2021-07-16 13:12:05"
}

// 13
{
    "_id": ObjectId("60f11b163e2e8152fb6c3aac"),
    "identificationType": NumberInt("1"),
    "byThermometryUnit": NumberInt("0"),
    "byIsAbnomalTemperature": NumberInt("0"),
    "struTime": NumberInt("1626413844"),
    "datetime": "2021-07-16 13:37:24"
}

// 14
{
    "_id": ObjectId("60f11eaa3e2e8152fb6c3aad"),
    "identificationType": NumberInt("1"),
    "byThermometryUnit": NumberInt("0"),
    "byIsAbnomalTemperature": NumberInt("0"),
    "struTime": NumberInt("1626414760"),
    "datetime": "2021-07-16 13:52:40"
}