python convert string time to sql datetime format

Question

I am importing a large csv file to ETL into a database, and the date format that was originally set in the csv file looks like 4/22/2016 1:00:00 PM. Each date is part of a larger list that might have non-date type items in it. for example:

v = ['4/29/2016 8:25:58 AM', '5/25/2016 2:22:22 PM', 'True', 'Foo', 1]

I would like to reformat every date (if present in the list) with the correct MySQL format of

%m-%d-%Y %I:%M:%S

How would i do this with a list comprehension? my code is not working for obvious reasons but i'm not sure where to go from here. I need to retain the index that the date is found in v.

from datetime import datetime, date, time

v = ['4/29/2016 8:25:58 AM', '5/25/2016 2:22:22 PM', 'True', 'Foo', 1]


def fixdate(_params):
        tstamp = datetime.strptime(_params, "%m/%d/%Y %I:%M:%S %p")
        newtstamp = date.strftime(tstamp, "%m-%d-%Y %I:%M:%S")
        replace = { _params: newtstamp }
        l = [replace.get(x, x) for x in _params]
        print l


fixdate(v)

Dinesh Pundkar · Accepted Answer · 2016-09-20 20:55:00Z

2

Please check this. Comments inline with code.

from datetime import datetime

v = ['4/29/2016 8:25:58 AM', '5/25/2016 2:22:22 PM', 'True', 'Foo', 1]

def fixdate(_params):

        print "Before changing format ..."
        print _params
        #First date in list
        tstamp = datetime.strptime(_params[0], "%m/%d/%Y %I:%M:%S %p")
        #Add %p after %S if AM or PM is required
        newtstamp = datetime.strftime(tstamp, "%m-%d-%Y %I:%M:%S")
        #Update the element in list
        _params[0] = newtstamp

        #Second date in list
        tstamp = datetime.strptime(_params[1], "%m/%d/%Y %I:%M:%S %p")
        newtstamp = datetime.strftime(tstamp, "%m-%d-%Y %I:%M:%S")
        #Update the element in list
        _params[1] = newtstamp

        print "After changing format..."
        print _params

fixdate(v)

Output:

C:\Users\dinesh_pundkar\Desktop>python c.py
Before changing format ...
['4/29/2016 8:25:58 AM', '5/25/2016 2:22:22 PM', 'True', 'Foo', 1]
After changing format...
['04-29-2016 08:25:58', '05-25-2016 02:22:22', 'True', 'Foo', 1]

C:\Users\dinesh_pundkar\Desktop>

Code with list comprehension:

from datetime import datetime

v = ['4/29/2016 8:25:58 AM', '5/25/2016 2:22:22 PM', 'True', 'Foo', 1]

def fixdate(_params):

        print "Before changing format ..."
        print _params

        _params = [i if ':' not in str(i) and '/' not in str(i) else datetime.strftime(datetime.strptime(i, "%m/%d/%Y %I:%M:%S %p"), "%m-%d-%Y %I:%M:%S") for i in _params]

        print "After changing format..."
        print _params

fixdate(v)

Output:

C:\Users\dinesh_pundkar\Desktop>python c.py
Before changing format ...
['4/29/2016 8:25:58 AM', '5/25/2016 2:22:22 PM', 'True', 'Foo', 1]
After changing format...
['04-29-2016 08:25:58', '05-25-2016 02:22:22', 'True', 'Foo', 1]

C:\Users\dinesh_pundkar\Desktop>

edited Sep 20, 2016 at 20:55

answered Sep 20, 2016 at 19:53

Dinesh Pundkar

4,1962 gold badges26 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Dinesh Pundkar Over a year ago

@dobbs - Please let me know if you want any other info.

dobbs Over a year ago

Thanks for your submission. my actual list i am working with is much bigger (17 elements), and i'd like to accomplish this with a list comprehension if possible rather than specifying each index which needs to be modified. I updated the question above to address this

Collectives™ on Stack Overflow

python convert string time to sql datetime format

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related