what is the proper way to convert between mysql datetime and python timestamp?

Question

according to http://dev.mysql.com/doc/refman/5.0/en/datetime.html. i got to find a way to convert the string value 'YYYY-MM-DD HH:MM:SS' to a timestamp int.

i looked up in python's doc.

i tried:

print(time.strptime('2013-01-12 15:27:43', '%Y-%m-%d %H:%M:%S'))   

python give me a result like this.

time.struct_time(tm_year=2013, tm_mon=1, tm_mday=12, tm_hour=15, tm_min=27, tm_sec=43, tm_wday=5, tm_yday=12, tm_isdst=-1)

i tried this to convert timestamp to YYYY-MM-DD HH:MM:SS format

print(time.strftime('%Y-%m-%d %H:%M:%S',time.time()))

python give me a type error.

i only use timestamp to calculate time and date, i hope there's already a way in python, simple and efficient , and don't have to create temp data.

according to the answer i write two methods. hope it would be helpful

import time

def convertTimestampToSQLDateTime(value):
    return time.strftime('%Y-%m-%d %H:%M:%S',time.localtime(value))

def convertSQLDateTimeToTimestamp(value):
    return time.mktime(time.strptime(value, '%Y-%m-%d %H:%M:%S'))

Answer 1

Happy to update this if I'm not properly understanding, but here are a few examples which may help. Note that this uses the datetime module instead of time.

>>> import datetime

Here we set up an example timestamp ts and a format f:

>>> ts = '2013-01-12 15:27:43'
>>> f = '%Y-%m-%d %H:%M:%S'

Similar to what you did above, we use the strptime function (from datetime.datetime) to convert our string into a datetime object based on the formatting parameter:

>>> datetime.datetime.strptime(ts, f)
datetime.datetime(2013, 1, 12, 15, 27, 43)

Now in reverse - here we use datetime.datetime.now() to get the current time as a datetime object:

>>> now = datetime.datetime.now()
>>> now
datetime.datetime(2013, 1, 12, 0, 46, 54, 490219)

In the datetime case, the strftime method is actually called on the datetime object itself, with the formatting parameter as an argument:

>>> now.strftime(f)   
'2013-01-12 00:46:54'

In your situation, the reason you were getting an error is because time.time() returns a float:

>>> time.time()
1357980846.290231

But time.strftime needs a time tuple, similar to what you had above. Without getting into the maddening spiral that is time, a function such as time.localtime() will return the aforementioned time tuple and will return as you expect:

>>> now = time.localtime()
>>> now
time.struct_time(tm_year=2013, tm_mon=1, tm_mday=12, tm_hour=0, tm_min=55, tm_sec=55, tm_wday=5, tm_yday=12, tm_isdst=0)
>>> f = '%Y-%m-%d %H:%M:%S'
>>> time.strftime(f, now)
'2013-01-12 00:55:55'