Pymongo convert timestamp to date as a new field

I have many entities in my collection and I have to create a new date field in collection to use for future queries.

    {'_id': ObjectId('5afea920d326051990a7f337'), 'created_at': 'Fri May 18 10:21:07 +0000 2018', 'timestamp_ms': '1526638867739'}
    {'_id': ObjectId('5afea920d326051990a7f339'), 'created_at': 'Fri May 18 10:21:08 +0000 2018', 'timestamp_ms': '1526638868310'}
    {'_id': ObjectId('5afea972d326051c5c05bc11'), 'created_at': 'Fri May 18 10:22:30 +0000 2018', 'timestamp_ms': '1526638950799'}
    {'_id': ObjectId('5afea974d326051c5c05bc16'), 'created_at': 'Fri May 18 10:22:32 +0000 2018', 'timestamp_ms': '1526638952160'}
    {'_id': ObjectId('5afea974d326051c5c05bc17'), 'created_at': 'Fri May 18 10:22:32 +0000 2018', 'timestamp_ms': '1526638952841'}



I have many entities in my collection and I have to create a new date field in collection to use for future queries.

{'_id': ObjectId('5afea920d326051990a7f337'), 'created_at': 'Fri May 18 10:21:07 +0000 2018', 'timestamp_ms': '1526638867739'}
{'_id': ObjectId('5afea920d326051990a7f339'), 'created_at': 'Fri May 18 10:21:08 +0000 2018', 'timestamp_ms': '1526638868310'}
{'_id': ObjectId('5afea972d326051c5c05bc11'), 'created_at': 'Fri May 18 10:22:30 +0000 2018', 'timestamp_ms': '1526638950799'}
{'_id': ObjectId('5afea974d326051c5c05bc16'), 'created_at': 'Fri May 18 10:22:32 +0000 2018', 'timestamp_ms': '1526638952160'}
{'_id': ObjectId('5afea974d326051c5c05bc17'), 'created_at': 'Fri May 18 10:22:32 +0000 2018', 'timestamp_ms': '1526638952841'}

I need to convert timestamp_ms into date format like this:

{'_id': ObjectId('5afea920d326051990a7f337'), 'created_at': 'Fri May 18 10:21:07 +0000 2018', 'timestamp_ms': '1526638867739’, 'NewDate': '2018 05 18 10:21:07'}
{'_id': ObjectId('5afea920d326051990a7f339'), 'created_at': 'Fri May 18 10:21:08 +0000 2018', 'timestamp_ms': '1526638868310’, 'NewDate': '2018 05 18 10:21:08'}
{'_id': ObjectId('5afea972d326051c5c05bc11'), 'created_at': 'Fri May 18 10:22:30 +0000 2018', 'timestamp_ms': '1526638950799’, 'NewDate': '2018 05 18 10:22:30'}
{'_id': ObjectId('5afea974d326051c5c05bc16'), 'created_at': 'Fri May 18 10:22:32 +0000 2018', 'timestamp_ms': '1526638952160’, 'NewDate': '2018 05 18 10:22:32'}
{'_id': ObjectId('5afea974d326051c5c05bc17'), 'created_at': 'Fri May 18 10:22:32 +0000 2018', 'timestamp_ms': '1526638952841’, 'NewDate': '2018 05 18 10:22:32'}


I used this code (Python 3.6, pymongo 3.8, mongodb 4.0):

    pipeline = [
        {
            '$addFields': {
                'newDate': {
                    '$toDate': '$timestamp_ms'
                }
            }
        }
    ]
    cursor = collection.aggregate(pipeline)

But gives this error message: pymongo.errors.OperationFailure: Error parsing date string '1526638867739'; 12: Unexpected character '9'

I am not sure that aggregate is the right method for this task. datetime.strptime() can be better for 'created_at' but I haven't figured out how to implement it into db.Mycollection_update_many().

Hi @enes_75322,

First of all you need to remove the quotes around the value of timestamp_ms field so that it is not considered as String.
You can refer to the following screenshot for the timestamp_ms field value:

Then you can use following query to update all the timestamp_ms field values:

db.newCollection2.aggregate([{$project: {yearMonthDayUTC: { $convert: { to: “NewDate”, input: “$timestamp_ms” } }}}])

I hope it helps!

Let me know, if you have any questions.

Thanks,
Sonali

Hi Sonali,
I tired to run your script both on shell and on python. Both of them gave the error codes. I’ll put them so you can have some ideas.

on shell:
[js] Error: command failed: ('ok': 0, 'errmsg': 'Unknown type name: NewDate', 'code': 2, 'codename': 'BadValue') : aggregate failed

on Python:
SyntaxError: invalid syntax

In python I have to define objects or I have to use them inside the apostrophe. If I used like this:
db.newCollection2.aggregate([{'$project':{'yearMonthDayUTC':{'$convert':{'to':'NewDate',input:'$timestamp_ms'}}}}])

it gives this error:
bson.errors.InvalidDocument:documents must have only string keys, key was (built-in function ınput)
Thanks for your reply