How to fix the date and time fields Pymongo

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?

MongoDB stores data as BSON. The BSON spec only has support for UTC datetimes which are represented as milliseconds since the Unix epoch.

When you do df['date4'].astype('datetime64[ns]') what you are basically doing is converting a date object into a datetime object with (0, 0) time offset. This is required, because again BSON only supports datetimes (and not dates). Consequently, when you read the data back you are also seeing the trailing 0s the represent the time offset in addition to the date.

Depending on what you are trying to do, there are many ways to remedy the situation. Since the datetime type is more-granular than the date type you are not losing any information during the type cast. You can simply call the date() method on the datetime objects returned by the server to get back the date. See datetime — Basic date and time types — Python 3.11.2 documentation

2 Likes

From your previous posts I see your parsing text files (like CSV) to upload them into MongoDB. I suggest you combine the ‘date4’ and ‘time4’ fields into a single datetime.datetime mongodb field ‘datetime4’ (or whatever name you like). As Prashant mentions above, the datetime type contains both a date and a time.