Can't convert from BSON type string to Date

Drivers & ODMs
#1

Hey Everyone,

In my collection there is field called as time and the format of this field is as follows :
time: “2019/05/02 00:00:00”.

I need to extract hours from this field and group them based on hour. I tried many things including ISODate(), new Date() but getting different errors. Below is my project block followed by one of the errors. Any help in this regard will be appreciated.

command_2 = {"$project": {"_id": 0,
                              "station_id": 1,
                              "station_status": 1,
                              "hour": {"$hour": "$time"},
                              "available_bikes": 1
                              }
                 }
    my_query.append(command_2)

pymongo.errors.OperationFailure: can’t convert from BSON type string to Date, full error: {‘operationTime’: Timestamp(1616064173, 4), ‘ok’: 0.0, ‘errmsg’: “can’t convert from BSON type string to Date”, ‘code’: 16006, ‘codeName’: ‘Location16006’, ‘$clusterTime’: {‘clusterTime’: Timestamp(1616064173, 4), ‘signature’: {‘hash’: b’\xe0\xdd\x7f\xd6&\x1d\r\xb5\xdfv\x11\xc3\x88\xfc\xb1L\x93\x7f\xb8\xe1’, ‘keyId’: 6929970731853807619}}}

#2

Hi @Gunjan_Gautam,

As you’re storing the time field as a string you can’t use the $hour operator directly. You will first need to convert the time to a Date type, which can be done using the $dateFromString operator as such:

db.foo.drop();
db.foo.insert({ time: "2019/05/02 13:00:00" })
db.foo.aggregate([
{ $project: { 
    hour: { 
        $hour: { 
            $dateFromString: { dateString: "$time", format: "%Y/%m/%d %H:%M:%S" } 
        } 
    }
}}])

Try this out in the mongo shell then adapt to Python as needed.

#3

Hey @alexbevi

Its now running but not able to extract hour. Please have a look at the output. I want hours like 19, 21 etc.

— DOCUMENT 2492 INFO —

available_bikes : 0
hour : 2019-07-11 05:30:00
station_id : 522
station_status : In Service

Process finished with exit code 0

#4

@Gunjan_Gautam try it first using the mongo shell. I’m not sure what the output is you’re sharing but if you can adapt the following and share the results it might help:

// change COLLECTION to the name of your collection
db.COLLECTION.aggregate([
{ $limit: 1 },
{ $addFields: { 
    hour: { 
        $hour: { 
            $dateFromString: { dateString: "$time", format: "%Y/%m/%d %H:%M:%S" } 
        } 
    }
}}])
#5

Since your time field is a string. You can simply use $substr as in:

{
	"$project" : {
		"hour" : {
			"$substr" : [
				"$time",
				11,
				2
			]
		}
	}
}

to get the hour part. See https://docs.mongodb.com/manual/reference/operator/aggregation/substr.

1 Like
#6

If your time field only contains data in this format then what @steevej wrote is correct. My example expected the date string to be in a 2019/05/02 00:00:00 format, however your latest example uses a different delimiter (- compared to /).

1 Like
#8

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.