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

Question

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

Answer 1

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 the Python date and time to the UNIX timestamp:

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

Answer 2

You can use the MySQL FROM_UNIXTIME 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() 

Exit (if you saved the above epoch.py ​​value):

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

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

Answer 3

solvable.

I just did this:

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