In Python, if I have a unix timestamp, how do I insert that into a MySQL datetime field?

Question

I am using Python MySQLDB, and I want to insert this into DATETIME field in Mysql . How do I do that with cursor.execute?

Answer 1

Solved.

I just did this:

datetime.datetime.now() ...insert that into the column.

Answer 2

You can use the FROM_UNIXTIME MySQL function:

#import MySQLdb as mysql
import mysql.connector as mysql

if __name__ == '__main__':
    cnx = mysql.connect(user='root')
    cur = cnx.cursor()

    cur.execute("SELECT FROM_UNIXTIME(%s)", (1268811665,))
    print cur.fetchall()
    cur.close()
    cnx.close()

The output (if you save the above to epoch.py):

$ python epoch.py 
[(datetime.datetime(2010, 3, 17, 8, 41, 5),)]

You can use the FROM_UNIXTIME in your INSERT or other DML SQL statements.

Answer 3

To convert from a UNIX timestamp to a Python datetime object, use datetime.fromtimestamp() (documentation).

>>> from datetime import datetime
>>> datetime.fromtimestamp(0)
datetime.datetime(1970, 1, 1, 1, 0)
>>> datetime.fromtimestamp(1268816500)
datetime.datetime(2010, 3, 17, 10, 1, 40)

From Python datetime to UNIX timestamp:

>>> import time
>>> time.mktime(datetime(2010, 3, 17, 10, 1, 40).timetuple())
1268816500.0