CODEWITHSUNDEEP
pythonmongodbpymongo
Hà Muôn
Hà Muôn

Reputation: 21

Data type of time in MongoDB?

I am writing a program to transfer data from MySQL to MongoDB in python. However, I have trouble transferring time data type to MongoDB: - MySQL side has a time data field in the example format: '18: 59: 32.521177 '("hours: minute: second: milisecond") I want to ask in MongoDB what is the corresponding data type of the above field? How can I use python to convert it?

Upvotes: 2

Views: 6224

Answers (2)

Buzz Moschetti
Buzz Moschetti

Reputation: 7578

There is no time type in mongodb, only datetime. If you truly only ever use the time and do not care about the date component at all, I recommend setting the date component to 1-jan-1 (year 1) as a marker. In your example, 521177 looks like it is in microseconds, not milliseconds, so that will go into a python datetime object straight away:

#  Get your time object from MySQL and extract hour, mins, secs, and microseconds.
#  Then, construct a full datetime with the marker date:
d7 = datetime.datetime(1, 1, 1, 18, 59, 32, 521177)
db.foo.insert({"myDate": d7});

db.foo.find();
{ "_id" : ObjectId("5ce45553283c3282ad1cec34"), "myDate" : ISODate("0001-01-01T18:59:32.521Z") }

Note that the time component of the MongoDB datetime is accurate only to millis. If you require capture of micros, you will have to store that in a peer field, e.g.

micros = 521177
d7 = datetime.datetime(1, 1, 1, 18, 59, 32, micros)
db.foo.insert({"myDate": d7, "micros": micros});

Or take advantage of rich types and make a little date+micros structure:

micros = 521177
d7 = datetime.datetime(1, 1, 1, 18, 59, 32, micros)
db.foo.insert({"myDate": {"d":d7, "micros": micros}});

This way, you can easily carry around both pieces of data:

db.foo.findOne({}, {"myDate":1} );
{
    "_id" : ObjectId("5ce457df283c3282c43a79b7"),
    "myDate" : {
        "micros" : 521177,
        "d" : ISODate("0001-01-01T18:59:32.521Z")
    }
}

In any event, you can use the timedelta features of python datetime directly on the date objects fetched from MongoDB. With the 1-Jan-1 convention, the date diff will always be zero, yielding you a diff in time -- albeit accurate only to millis:

micros = 521177
d7 = datetime.datetime(1, 1, 1, 18, 59, 32, micros)
db.foo.insert({"_id":0, "myDate": {"d":d7, "micros": micros}});
micros += 3780    # jump ahead in time a bit....
d7 = datetime.datetime(1, 1, 1, 18, 59, 32, micros)
db.foo.insert({"_id":1, "myDate": {"d":d7, "micros": micros}});

doc1 = db.foo.find_one({"_id":0});
doc2 = db.foo.find_one({"_id":1});

delta = doc2['myDate']['d'] - doc1['myDate']['d']
print "delta: ", delta

delta:  0:00:00.003000

You can of course get full fidelity back by adding in the micros modulo 1000 to get the micros that are not part of the millis already stored with the date:

adj1 = doc1['myDate']['d'] + datetime.timedelta(microseconds=doc1['myDate']['micros'] % 1000)
adj2 = doc2['myDate']['d'] + datetime.timedelta(microseconds=doc2['myDate']['micros'] % 1000)

delta = adj2 - adj1
print "delta: ", delta

delta:  0:00:00.003780

And of course if you settle on using the mini struct to carry datetime+micros, then a function is called for:

def mdb2py(minidatestruct):
    return minidatestruct['d'] + datetime.timedelta(microseconds=minidatestruct['micros'] % 1000)

adj1 = mdb2py(doc1['myDate'])
adj2 = mdb2py(doc2['myDate'])

delta = adj2 - adj1
print "delta: ", delta

delta:  0:00:00.003780

Upvotes: 1

Dmitrii Sidenko
Dmitrii Sidenko

Reputation: 658

You can to keep your data as string and make a conversation:

  • From string to date:
your_datatime = datetime.strptime(your_str_datatime, "%Y-%m-%dT%H:%M:%SZ")
  • From date to str:
your_str_datatime = your_datatime.strftime("%Y-%m-%dT%H:%M:%SZ")

Upvotes: 0

Related Questions