DATEDIFF() not working in sql queries

Anand Saran shared this problem 5 years ago
Not a Problem

I am trying to execute the following query:

mb.runSQLQuery(`


SELECT "locationEdgeList.fromLocation.locationName","locationEdgeList.fromLocation.locationName", (DATEDIFF(Hour,"locationEdgeList.etd","locationEdgeList.eta") FROM vesselSchedule

group by "locationEdgeList.fromLocation.locationName","locationEdgeList.fromLocation.locationName"


`)

and its showing the following exception.

{

"message" : "Unrecognized expression '$DATEDIFF'",

"ok" : 0,

"code" : 168,

"codeName" : "InvalidPipelineOperator",

"name" : "MongoError"

}

Can you let me know how to make this work.

Best Answer
photo

The field "locationEdgeList" is an array, to run SQLQuery against the collection, you have to unwind this field first.

In version 5.0, runSQLQuery can run as a pipeline stage so that you can write

db.vesselSchedule.aggregate()
   .unwind("$locationEdgeList")
   .runSQLQuery(`
         SELECT AVG(("locationEdgeList.eta"- "locationEdgeList.etd")/(60*60*1000)) as avgHour FROM vesselSchedule group by "locationEdgeList.fromLocation.locationName" as locFrom, "locationEdgeList.toLocation.locationName" as locTo
   `)

But version 4.0 has no such feature, and you have to write it using an aggregate statement.

db.vesselSchedule.aggregate([{
    $unwind: "$locationEdgeList"
}, {
    $group: {
        _id: {
            locFrom: "$locationEdgeList.fromLocation.locationName",
            locTo: "$locationEdgeList.toLocation.locationName"
        },
        avgHour: {
            $avg: {
                $divide: [{
                    $subtract: ["$locationEdgeList.eta", "$locationEdgeList.etd"]
                }, {
                    $multiply: [{
                        $multiply: [60, 60]
                    }, 1000]
                }]
            }
        }
    }
}])

Replies (1)

photo
2

NoSQLBooster only supports MongoDB date operator as SQL Function, does not support DATEDIFF function.


The $subtracts operator subtracts two numbers to return the difference, or two dates to return the difference in milliseconds, or a date and a number in milliseconds to return the resulting date. NoSQLBooster will map "-" operation as $subtract.

You can write:

SELECT (locationEdgeList.etd-locationEdgeList.eta)/(60*60*1000) as diffHours FROM vesselSchedule

photo
1

I have tried using query but the following is returned:

{

"message" : "The $subtract accumulator is a unary operator",

"ok" : 0,

"code" : 40237,

"codeName" : "Location40237",

"name" : "MongoError"

}

photo
1

I think the field "(DATEDIFF(Hour,"locationEdgeList.etd","locationEdgeList.eta")" is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

And, Can you describe your needs in detail, sample data and what kind of results do you want to get ?

photo
1

The sample object is as follows:

"locationEdgeList" : [

{

"fromLocation" : {

"locationCode" : "ECGYE",

"locationName" : "Guayaquil"

},

"etd" : ISODate("2018-11-21T05:30:00.000+05:30"),

"toLocation" : {

"locationCode" : "COBUN",

"locationName" : "Buenaventura"

},

"eta" : ISODate("2018-12-21T05:30:00.000+05:30")

}

],


I need to find the average duration for each location pair based on the arrivals and departures.

For a pair of locations, there could any number of entries with different arrival and departure dates

If we had to do this in SQL, the following should work:

mb.runSQLQuery(`


SELECT "locationEdgeList.fromLocation.locationName", "locationEdgeList.toLocation.locationName", AVG(DATEDIFF(HOUR,"locationEdgeList.eta", "locationEdgeList.etd")) FROM vesselSchedule group by "locationEdgeList.fromLocation.locationName", "locationEdgeList.toLocation.locationName"


`)

Could you please suggest an alternative approach?

photo
1

The field "locationEdgeList" is an array, to run SQLQuery against the collection, you have to unwind this field first.

In version 5.0, runSQLQuery can run as a pipeline stage so that you can write

db.vesselSchedule.aggregate()
   .unwind("$locationEdgeList")
   .runSQLQuery(`
         SELECT AVG(("locationEdgeList.eta"- "locationEdgeList.etd")/(60*60*1000)) as avgHour FROM vesselSchedule group by "locationEdgeList.fromLocation.locationName" as locFrom, "locationEdgeList.toLocation.locationName" as locTo
   `)

But version 4.0 has no such feature, and you have to write it using an aggregate statement.

db.vesselSchedule.aggregate([{
    $unwind: "$locationEdgeList"
}, {
    $group: {
        _id: {
            locFrom: "$locationEdgeList.fromLocation.locationName",
            locTo: "$locationEdgeList.toLocation.locationName"
        },
        avgHour: {
            $avg: {
                $divide: [{
                    $subtract: ["$locationEdgeList.eta", "$locationEdgeList.etd"]
                }, {
                    $multiply: [{
                        $multiply: [60, 60]
                    }, 1000]
                }]
            }
        }
    }
}])

photo
1

Thanks a lot, this worked.

photo
Leave a Comment
 
Attach a file