如何获得MongoDB中的子登记之间的日期差异？

发布于 2025-02-12 15:56:06 字数 2340 浏览 0 评论 0原文

以下是数组，

 {
    "CDF": {
        "UTILITYTYPE": {
            "D1": {
                "G1": "12387835",
                "G22": {
                    "NAME": "L1"
                }
            },
            "D5": {
                "EVENT": [
                    {
                        "CODE": "13",
                        "TIME": "29-05-2022 13:26:00",
                        "STATUS": "0"
                    },
                    {
                        "CODE": "13",
                        "TIME": "29-05-2022 14:41:00",
                        "STATUS": "1"
                    },
                    {
                        "CODE": "13",
                        "TIME": "31-05-2022 10:13:00",
                        "STATUS": "0"
                    },
                    {
                        "CODE": "13",
                        "TIME": "31-05-2022 10:18:00",
                        "STATUS": "1"
                    }
                ]
            }
        }
    }
},
{
    "CDF": {
        "UTILITYTYPE": {
            "D1": {
                "G1": "12388215",
                "G22": {
                    "NAME": "L2"
                }
            },
            "D5": {
                "EVENT": [
                    {
                        "CODE": "7",
                        "TIME": "16-05-2022 04:28:21",
                        "STATUS": "0"
                    },
                    {
                        "CODE": "7",
                        "TIME": "16-05-2022 06:30:30",
                        "STATUS": "1"
                    },
                    {
                        "CODE": "7",
                        "TIME": "16-05-2022 07:36:53",
                        "STATUS": "0"
                    },
                    {
                        "CODE": "7",
                        "TIME": "16-05-2022 19:39:28",
                        "STATUS": "1"
                    }
                ]
            }
        }
    }
}

如何比较以下时间以使用MongoDB中的聚合获得差异？比较应在第一个和第二个和第四和第四及第四及第二个之间。我也想以适当的可比日期格式转换日期。预期的输出是

我想获得2个事件之间的时间差的记录超过1小时

， G1：12387835在Out Put Array中的记录

{
    "CODE": "13",
    "TIME": "29-05-2022 13:26:00",
    "STATUS": "0"
},
{
    "CODE": "13",
    "TIME": "29-05-2022 14:41:00",
    "STATUS": "1"
}

原文

Below is the array

 {
    "CDF": {
        "UTILITYTYPE": {
            "D1": {
                "G1": "12387835",
                "G22": {
                    "NAME": "L1"
                }
            },
            "D5": {
                "EVENT": [
                    {
                        "CODE": "13",
                        "TIME": "29-05-2022 13:26:00",
                        "STATUS": "0"
                    },
                    {
                        "CODE": "13",
                        "TIME": "29-05-2022 14:41:00",
                        "STATUS": "1"
                    },
                    {
                        "CODE": "13",
                        "TIME": "31-05-2022 10:13:00",
                        "STATUS": "0"
                    },
                    {
                        "CODE": "13",
                        "TIME": "31-05-2022 10:18:00",
                        "STATUS": "1"
                    }
                ]
            }
        }
    }
},
{
    "CDF": {
        "UTILITYTYPE": {
            "D1": {
                "G1": "12388215",
                "G22": {
                    "NAME": "L2"
                }
            },
            "D5": {
                "EVENT": [
                    {
                        "CODE": "7",
                        "TIME": "16-05-2022 04:28:21",
                        "STATUS": "0"
                    },
                    {
                        "CODE": "7",
                        "TIME": "16-05-2022 06:30:30",
                        "STATUS": "1"
                    },
                    {
                        "CODE": "7",
                        "TIME": "16-05-2022 07:36:53",
                        "STATUS": "0"
                    },
                    {
                        "CODE": "7",
                        "TIME": "16-05-2022 19:39:28",
                        "STATUS": "1"
                    }
                ]
            }
        }
    }
}

How do I compare below time to get difference using aggregation in mongodb?
The comparison should be between the first and the second then third and fourth and further.
Also I want to convert the dates in proper comparable date format.
Expected output is

i want to get the record whos TIME difference between 2 events is more than 1 hours

Eg: Suppose date difference between these 2 subdocuments in first record has difference of more than 1 hour then i should only get
G1:12387835 RECORD IN THE OUT PUT ARRAY

{
    "CODE": "13",
    "TIME": "29-05-2022 13:26:00",
    "STATUS": "0"
},
{
    "CODE": "13",
    "TIME": "29-05-2022 14:41:00",
    "STATUS": "1"
}

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

梦途 2025-02-19 15:56:06

一种选项是：

将事件分为阵列的时间格式化，
根据状态
将它们合并为夫妻。
仅留出时间差的夫妻大于1小时，

db.collection.aggregate([
  {
    $project: {
      EVENT: {
        $map: {
          input: "$CDF.UTILITYTYPE.D5.EVENT",
          as: "i",
          in: {
            CODE: "$i.CODE",
            STATUS: "$i.STATUS",
            TIME: {
              $dateFromString: {
                "dateString": "$i.TIME",
                "format": "%d-%m-%Y %H:%M:%S"
              }
            }
          }
        }
      }
    }
  },
  {
    $project: {
      firstEvent: {
        $filter: {input: "$EVENT", cond: {$eq: ["$this.STATUS", "0"]}}
      },
      secondEvent: {
        $filter: {input: "$EVENT", cond: {$eq: ["$this.STATUS", "1"]}}
      }
    }
  },
  {$project: {couples: {$zip: {inputs: ["$firstEvent", "$secondEvent"]}}}},
  {$project: {
      couples: {
        $filter: {
          input: "$couples",
          cond: {
            $gt: [
              {$dateDiff: {
                  startDate: {$first: "$this.TIME"},
                  endDate: {$last: "$this.TIME"},
                  unit: "hour"
                }
              }, hourDiffParameter]
          }
        }
      }
    }
  }
])

请参见其在

One option is:

Format the time
Divide the events into to arrays according to the status
Merge them back as couples.
Keep only couples with time difference greater than 1 hour

db.collection.aggregate([
  {
    $project: {
      EVENT: {
        $map: {
          input: "$CDF.UTILITYTYPE.D5.EVENT",
          as: "i",
          in: {
            CODE: "$i.CODE",
            STATUS: "$i.STATUS",
            TIME: {
              $dateFromString: {
                "dateString": "$i.TIME",
                "format": "%d-%m-%Y %H:%M:%S"
              }
            }
          }
        }
      }
    }
  },
  {
    $project: {
      firstEvent: {
        $filter: {input: "$EVENT", cond: {$eq: ["$this.STATUS", "0"]}}
      },
      secondEvent: {
        $filter: {input: "$EVENT", cond: {$eq: ["$this.STATUS", "1"]}}
      }
    }
  },
  {$project: {couples: {$zip: {inputs: ["$firstEvent", "$secondEvent"]}}}},
  {$project: {
      couples: {
        $filter: {
          input: "$couples",
          cond: {
            $gt: [
              {$dateDiff: {
                  startDate: {$first: "$this.TIME"},
                  endDate: {$last: "$this.TIME"},
                  unit: "hour"
                }
              }, hourDiffParameter]
          }
        }
      }
    }
  }
])

See how it works on the playground example

回复收藏 0 原文

~没有更多了~