CODEWITHSUNDEEP
pythonmongodbpymongo
Seong Kim
Seong Kim

Reputation: 605

Update all MongoDB's datetime string to ISO datetime format in MongoDB

I have a little trouble querying my mongodb collection becasue its datetime are all stored as a String and the format is like this: timestamp:"Sat Jun 09 2018 11:58:03 GMT-0400 (EDT)"

the type of timestamp is String in the mongodb. I like to convert all its timestamp to be UTC ISO datetime. I am a newbie in Mongo.. I came from MSSQL.. I can't find a way to update all its timestamp which is in a weird format.

My goal is to run a query {timestamp: {$lte: some iso datetime.}}

Upvotes: 1

Views: 1496

Answers (2)

Belly Buster
Belly Buster

Reputation: 8844

Python method

Usually dateutil.parser.parse works well in this situation, but it doesn't like the convoluted format, so the process is to convert it to an ISO-look-alike string first and then parse the date:

import pymongo
import pytz
from datetime import datetime
from dateutil import parser

db = pymongo.MongoClient(tz_aware=True)['mydatabase']
db.mycollection.insert_one({"timestamp": "Sat Jun 09 2018 11:58:03 GMT-0400 (EDT)"})
records = db.mycollection.find()

for record in records:
    d_str = record.get('timestamp')
    if d_str is not None:
        naive_str = f'{d_str[0:24]}'
        offset = d_str[28:33]
        naive_d = datetime.strptime(naive_str, '%a %b %d %Y %H:%M:%S')
        iso_str = f'{naive_d.isoformat()}{offset}'
        aware_d = parser.parse(iso_str).astimezone(pytz.utc)
        db.mycollection.update_one({'_id': record['_id']}, {'$set': {'timestamp': aware_d}})

d = db.mycollection.find_one()
print (d['timestamp'], type(d['timestamp']))

gives:

2018-06-09 15:58:03+00:00 <class 'datetime.datetime'>

Upvotes: 0

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59602

Doing it purely in Mongo will be cumbersome, you would have to write quite a lot of code.

I think the easiest solution is to use the moment.js library because it can parse the RFC 2822 date-time format directly:

db.collection.find({}).forEach(function(doc) {
   var ts = moment(doc.timestamp).toDate();
   db.collection.updateOne(
      {_id: doc._id},
      {$set: { timestamp: ts} }
   );
})

You can run above command several times, i.e. when timestamp is already proper Date object then nothing breaks.

Upvotes: 1

Related Questions