How to fix the date and time fields Pymongo

Question

When i import my data to mongodb i get this:

    _id:object("603678958a6eade21c0790b8")
    id1:3758
    date2:2010-01-01T00:00:00.000+00:00
    time3:1900-01-01T00:05:00.000+00:00
    date4 :2009-12-31T00:00:00.000+00:00
    time5:1900-01-01T19:05:00.000+00:00
    id6 :2
    id7:-79.09
    id8:35.97
    id9:5.5
    id10:0
    id11:-99999
    id12 :0
    id13 :-9999
    c14:"U"
    id15:0
    id16:99
    id17:0
    id18:-99
    id19:-9999
    id20:33
    id21:0
    id22:-99
    id23:0

The ideal would be something like this:

    _id:object("603678958a6eade21c0790b8")
    id1:3758
    date2:2010-01-01
    time3:00:05:00
    date4 :2009-12-31
    time5:19:05:00
    id6 :2
    id7:-79.09
    id8:35.97
    id9:5.5
    id10:0
    id11:-99999
    id12 :0
    id13 :-9999
    c14:"U"
    id15:0
    id16:99
    id17:0
    id18:-99
    id19:-9999
    id20:33
    id21:0
    id22:-99
    id23:0

The code i have used to convert column date2,date4,time3,time5 to date and time is this:

    df['date4'] = df['date4'].astype('datetime64[ns]') 
    df['date2'] = df['date2'].astype('datetime64[ns]') 

    
    df['time3'] = df['time3'].apply(lambda x:datetime.datetime.strptime(x[0]+x[1]+":"+x[2]+x[3], '%H:%M'))
    df['time5'] = df['time5'].apply( lambda x: datetime.datetime.strptime(x[0] + x[1] + ":" + x[2] + x[3], '%H:%M'))

I tried some other things like datetime.datetime but nothing seems to work Does anyone know how can i fix that?

My experience with Mongo is that they have one data format and it is ISO standard. That has date + time in it. I just have a timestamp object where I have multiple timestamps and you can use datetime(year, month, day, hour, min sec) to create that format for any date/hour you want. — Kyle Hurst, Feb 24 '21 at 18:04
If you want triggers or something like that, you can insert those as strings, instead? — Kyle Hurst, Feb 24 '21 at 18:05

score 1 · Accepted Answer · answered Feb 24 '21 at 18:29

strptime create a datetime object from a string.

strftime do the opposite by creating a string from a datetime

You will actually want to use both because you have a string, you will then create a datetime object and then parse it again in string, but with the desired format

If you have python 3.7 you can use datetime.datetime.fromisoformat to convert your string to a datetime object

You can read more on How to parse an isoformat to a datatime object

df = pd.DataFrame([{
    "_id": "603678958a6eade21c0790b8",
    "date2": "2010-01-01T00:00:00.000+00:00",
    "time3": "1900-01-01T00:05:00.000+00:00",
    "date4": "2009-12-31T00:00:00.000+00:00",
    "time5": "1900-01-01T19:05:00.000+00:00",
}])
df['date4'] = df['date4'].apply(lambda x: datetime.datetime.strftime(datetime.datetime.fromisoformat(x), '%Y-%M-%d'))
df['date2'] = df['date2'].apply(lambda x: datetime.datetime.strftime(datetime.datetime.fromisoformat(x), '%Y-%M-%d'))
df['time3'] = df['time3'].apply(lambda x: datetime.datetime.strftime(datetime.datetime.fromisoformat(x), '%H:%M:%S'))
df['time5'] = df['time5'].apply(lambda x: datetime.datetime.strftime(datetime.datetime.fromisoformat(x), '%H:%M:%S'))

Output :

date2                2010-00-01
date4                2009-00-31
time3                  00:05:00
time5                  19:05:00

For a more readable code you can import your package like that : from datetime import datetime

from datetime import datetime
df['date4'] = df['date4'].apply(lambda x: datetime.strftime(datetime.fromisoformat(x), '%Y-%M-%d'))
df['date2'] = df['date2'].apply(lambda x: datetime.strftime(datetime.fromisoformat(x), '%Y-%M-%d'))
df['time3'] = df['time3'].apply(lambda x: datetime.strftime(datetime.fromisoformat(x), '%H:%M:%S'))
df['time5'] = df['time5'].apply(lambda x: datetime.strftime(datetime.fromisoformat(x), '%H:%M:%S'))

I'm personally used to the arrow package to manipulate dates. Here is the code with this package :

import arrow
df['date2'] = df['date2'].apply(lambda x: arrow.get(x).format("YYYY-MM-DD"))
df['date4'] = df['date4'].apply(lambda x: arrow.get(x).format("YYYY-MM-DD"))
df['time3'] = df['time3'].apply(lambda x: arrow.get(x).format("HH:mm:ss"))
df['time5'] = df['time5'].apply(lambda x: arrow.get(x).format("HH:mm:ss"))

gives the same output

You're welcome. You can mark the answer as accepted, to "close" the question — AlexisG, Feb 25 '21 at 15:52
I want to keep the format which you gave me for the date and time ,but i dont want the type of both of them to be string,i want to be date for the date2,date4 and time for the time3,time5.Is that possible?Do you know how i can do that? — xaroulis gekas, Mar 02 '21 at 19:17

How to fix the date and time fields Pymongo

1 Answers1