New Topic

NoSQLBooster for MongoDB (formerly MongoBooster)Topic Problem

DATEDIFF() not working in sql queries

Anand Saran shared this problem 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.

The same problem 0

Best Answer

qinghai (support@nosqlbooster.com) ●

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(

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(

Replies (1)

qinghai (support@nosqlbooster.com) ●

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

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

You can write:

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

Reply URL

Anand Saran ●

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"

}

URL

qinghai (support@nosqlbooster.com) ●

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 ?

URL

Anand Saran ●

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?

URL

qinghai (support@nosqlbooster.com) ●

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(

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(

URL

Anand Saran ●

Thanks a lot, this worked.

URL