How to convert "new Date" filter to timestamp format

Working with Data
,
1

Hi there,

I need help to convert the below filter
{CREATED_ON:{$lt:new Date(“2023-07-01”)}}

To the below format to be able to use it in mongodump:

{“CREATED_ON”:{“$lt”: {“$timestamp”:{“t”: 1688158800, “i”: 1}}}}

I tried to use unix epoch online converter but the above value doesn’t fetch any output.

Please advise

2

Hello, @Abdullah_Madani !

To be able to query date fields with timestamps, you will need to convert fields value first. It can be done in the aggregation pipeline with $toLong operator:

db.aggregate([
  {
    // let's assume, you have this example document
    // in your solection
    $documents: [
      {
        name: 'Sashko',
        birthday: ISODate('2023-07-01'),
      }
    ]
  },
  // caluclate new field that would contain 
  // converted timestamp
  {
    $addFields: {
      birthdayTimestamp: {
        $toLong: '$birthday'
      }
    }
  },
  // then we can match documents by timestamp
  {
    $match: {
      birthdayTimestamp: 1688169600000
    }
  }
]);

Output:

[
  {
    name: 'Sashko',
    birthday: ISODate("2023-07-01T00:00:00.000Z"),
    birthdayTimestamp: Long("1688169600000")
  }
]

You can remove birthdayTimestamp field from output using $project stage.

3

Hi @Abdullah_Madani

Assuming {CREATED_ON:{$lt:new Date("2023-07-01")}} returns the documents you’re interested in the format for mongodump query, depending on whether it is canonical or relaxed format is:

{"CREATED_ON":  {"$lt": {"$date":{"$numberLong":"1688158800000"}}}}

or

{"CREATED_ON":  {"$lt": {"$date":"2023-07-01T00:00:00+03:00"}}}

From mongodump docs

The query must be in Extended JSON v2 format (either relaxed or canonical/strict mode), including enclosing the field names and operators in quotes. For example:

Here is the extended JSON v2 format for dates
https://www.mongodb.com/docs/manual/reference/mongodb-extended-json/#mongodb-bsontype-Date

3 Likes
4

Nice work. Can you recommend a 3rd stage to convert it to the official Timestamp format which looks like this?

birthdayTimestamp : {
$timestamp : { “t” : 1688169600000, “i” : 1 }
}

5

Timestamp should not be used in an application schema, this is for MongoDB internals, e.g. oplog.

For mongodump(per this topic) convert the date to unix epoch beforehand.