I'm writing a query on a mongoDB database that monitors some AWS Batch activity, but unfortunately the types of the field updated_at are not consistent. ​

If the Field status is submitted, the type is datetime.datetime (ex : 2022-01-17T15:13:34.929 00:00), if the status is succeeded the type is time.time (ex : 1642434736, number of seconds since 1st January 1970).

As I cannot convert an int number to a date directly (I would need a double or another type) [source], I need to convert the Date to a number, here's how I did it :

db.topic_scorings.aggregate([   
    ​{
       ​$addFields: {
           ​timeSeconds: {
               ​$convert: {
                   ​input: "updated_at",
                   ​to: "double",
                   ​onNull: "$updated_at",
                   ​onError: "$updated_at"
               ​}
           ​}
       ​}
   ​}
])

But the issue is that the documents with type int keep the same value (number of seconds from epoch, just converted to type double), and the documents with type datetime.datetime are converted to type double, as the number of milliseconds since the 1st January 1970.
So my question is :

• How can I multiply the updated_at Field based on the value of the status Field ?

CodePudding user response：

how about this? it converts the number to date the multiple 1000 was to convert seconds to milliseconds

db.topic_scorings.aggregate(
   [
      {
         $project:
           {
             status: 1,
             newDateField:
               {
                 //$cond: [ { "$eq": [ "$status", "submitted" ] }, "$updated_at", { $convert : { input: "$updated_at", to: "long" } } ]
                   //$cond: [ { "$eq": [ "$status", "submitted" ] }, "$updated_at", { $toDate : { $convert : { input: "$updated_at", to: "long" } } }]
                   $cond: [ 
                        { "$eq": [ "$status", "submitted" ] }, 
                        "$updated_at", 
                        { $toDate : { $multiply : [ {$convert : { input: "$updated_at", to: "long" } }, 1000 ] } }
                   ]
               }
           }
      }
   ]
)