CODEWITHSUNDEEP
pythonmongodbpymongopython-dateutil
user1847830
user1847830

Reputation: 21

Convert All Strings In Column To ISODate Using Python and Copy Into MongoDB

I am new to MongoDB so please help me out with my question. I wish to convert all of my MongoDB string type date data in a column named match_date to ISODATE and paste them into the same column ( I am hoping to upload it to a different column in the start and rename it after completion of the iteration. )

But I want to make sure the new column is of the type ISODATE.

Currently, the string format is "3/31/2019 7:00:00 PM"

I am finding it hard to figure out how it can traverse through the entire collection and do this conversion and accomplish this task.

Your answers are much appreciated.

collection name: instats_tournament_matches_transition Column name: match_date

I tried executing this on the MongoDB shell but that won't work since I have to run this as a scheduled task.

I am hoping the results would look like 2019-04-25T15:31:35.000Z and do this task in Python

import datetime
import pymongo
import dateutil.parser

def getDatetimeFromISO(s):
    d = dateutil.parser.parse(s)
    return d

conn = pymongo.MongoClient()
db = conn.sampleDB
collection = db.test
post = {
    "user" : "test1",
    "date" : getDatetimeFromISO("2016-02-10T21:56:33.000Z")
}

collection.insert_one(post)
conn.close()

Upvotes: 1

Views: 282

Answers (1)

tfw
tfw

Reputation: 383

If your current data is all on the form 3/31/2019 7:00:00 PM as you describe, you can parse it with strptime() as mentioned.

from datetime import datetime

data = [
  '3/31/2019 7:00:00 PM',
  '3/30/2019 8:35:20 AM',
  '2/1/2019 11:00:00 PM',
]

for x in data:
  print(datetime.strptime(x, '%m/%d/%Y %I:%M:%S %p').isoformat())
  # 2019-03-31T19:00:00
  # 2019-03-30T08:35:20
  # 2019-02-01T23:00:00

I'm not really familiar with PyMongo and MongoDB, but I guess what you'd want is to loop through your collection and updating your documents with the new match_date(?) formatted like above.

A wild guess would be something like (I have not tested this)

docs = collection.find({})

for doc in docs:
    doc['match_date'] = datetime.strptime(doc['match_date'], '%m/%d/%Y %I:%M:%S %p').isoformat()
    collection.replace_one({'_id': doc['_id']}, doc)

But your mileage may vary.

Upvotes: 1

Related Questions