Search code examples
pythonmysqlunix-timestamp

str timestamp to mysql timestamp in python

I am trying to store unix timestamp in mysql with mysql timestamp but when I try to do that it shows 0000-00-00 00:00:00 in mysql

the way I have tried are as follows

table structure in mysql id name timestamp whare id is auto name is varchar and timestamp is timestamp

sql query is "INSERT INTO stocks (ticker, timestamp) VALUES ("harry", "1607485826")"

I have tried another one with datetime as follows from datetime import datetime // imported once only

tmpstp = datetime.fromtimestamp(1607485826)
f'INSERT INTO stocks (ticker, timestamp) VALUES ("harry", {tmpstp})'
Solution

  • Use

    INSERT INTO stocks (ticker, `timestamp`) 
VALUES (@ticker, FROM_UNIXTIME(@timestamp))

    where @ticker and @timestamp are parameters placeholders.

    For shown parameters values the query will be

    INSERT INTO stocks (ticker, `timestamp`) 
VALUES ('harry', FROM_UNIXTIME(1607485826))

    PS. I recommend you to rename the column timestamp strongly - this is reserved word in MySQL.