MongoDB aggregation created date to timestamp and match against timestamp field afterwards-CodePudding

I am trying to parse the _created_at fields of my collection to timestamps or rather I am adding an additional field "timestamp" to the collection.

However, when I get a timestamp like for example one 3 hours ago and put that inside a match aggregation, I still get documents back older than several days. What am I doing wrong here?

[
  {
    '$addFields': {
      'timestamp': {
        '$toLong': '$_created_at'
      }
    }
  }, {
    '$match': {
      'timestamp': {
        '$gte': 1648365437
      }
    }
  }
]

Resulting documents contain _created_at values from 22nd of March instead only few hours ago.

{
  _id: "3pFlY2W5kKDDRR4Isi5AdX75"
  from_address: "0xb2c76826c8a48ed5c5a06b27911177d7cc368223"
  log_index: 215
  to_address: "0x6262998ced04146fa42253a5c0af90ca02dfd2a3"
  transaction_hash: "0xcf8ef8d4e403f66a7ac1131b5cabc7610453d0013dbd3a91f5c4172724d00ca3"
  _created_at: 2022 - 03 - 22 T20: 17: 31.903   00: 00
  _updated_at: 2022 - 03 - 22 T20: 19: 43.670   00: 00
  block_hash: "0xdc8111c1c8b4058a71b1ca98452f37ed7712c2e0ebcc8a60b4af6833e9d6b169"
  block_number: 14438210
  block_timestamp: 2022 - 03 - 22 T20: 16: 05.000   00: 00
  decimal: 1500.075101511569677281
  token_address: "0x7d1afa7b718fb893db30a3abc0cfc608aacfebb0"
  transaction_index: 132
  value: "1500075101511569677281"
  confirmed: true
  timestamp: 1647980251903
}

CodePudding user response：

From the official document of the $toLong function,

Converts the Date into the number of milliseconds since the epoch.

So you should multiply your filtering criteria by 1000 to convert them into milliseconds for filtering.

[
  {
    '$addFields': {
      'timestamp': {
        '$toLong': '$_created_at'
      }
    }
  }, {
    '$match': {
      'timestamp': {
        '$gte': 1648365437 * 1000
      }
    }
  }
]

A side note is that it is generally considered anti-pattern not to store datetime field in proper date object. Consider modifying the schema to store the datetime fields in date instead of string.