Convert DD/MM/YYYY HH:MM:SS into MySQL TIMESTAMP

Question

I would like a simple way to find and reformat text of the format 'DD/MM/YYYY' into 'YYYY/MM/DD' to be compatible with MySQL TIMESTAMPs, in a list of text items that may or may not contain a date atall, under python. (I'm thinking RegEx?)

Basically i am looking for a way to inspect a list of items and correct any timestamp formats found.

Great thing about standards is that there are so many to choose from....

Answer 1

If you're using the MySQLdb (also known as "mysql-python") module, for any datetime or timestamp field you can provide a datetime type instead of a string. This is the type that is returned, also and is the preferred way to provide the value.

For Python 2.5 and above, you can do:

from datetime import datetime

value = datetime.strptime(somestring, "%d/%m/%Y")

For older versions of python, it's a bit more verbose, but not really a big issue.

import time
from datetime import datetime

timetuple = time.strptime(somestring, "%d/%m/%Y")
value = datetime(*timetuple[:6])

The various format-strings are taken directly from what's accepted by your C library. Look up man strptime on unix to find other acceptable format values. Not all of the time formats are portable, but most of the basic ones are.

Note datetime values can contain timezones. I do not believe MySQL knows exactly what to do with these, though. The datetimes I make above are usually considered as "naive" datetimes. If timezones are important, consider something like the pytz library.

Answer 2

You can read the string into a datetime object and then output it back as a string using a different format. For e.g.

>>> from datetime import datetime
>>> datetime.strptime("31/12/2009", "%d/%m/%Y").strftime("%Y/%m/%d")
'2009/12/31'

Basically i am looking for a way to inspect a list of items and correct any timestamp formats found.

If the input format is inconsistent, can vary, then you are better off with dateutil.

>>> from dateutil.parser import parse
>>> parse("31/12/2009").strftime("%Y/%m/%d")
'2009/12/31'

Dateutil can handle a lot of input formats automatically. To operate on a list you can map the a wrapper over the parse function over the list and convert the values appropriately.