0

I am working in python environment. I have a date string which is like this - 26 Apr 2022 17:23:17 GMT I want to insert this date into mysql where the date column datatype is timestamp. How can I achieve this?

My variables are like this (id=1, name="Jack", date="26 Apr 2022 17:23:17 GMT")
mycurser = mydb.cursor()
sql = "INSERT INTO sample_table (id,name,date) VALUES (%s, %s, %s)"
val = (id, name, date)
mycurser.execute(sql,val)

Here date is string and mysql datatype is timestamp.

DCVOID
  • 1
  • 1
  • Since it is unclear if you are asking for a solution in Python or in SQL for me, does [Converting string into datetime](https://stackoverflow.com/a/466376/6771046) in Python answer your question? – U880D Apr 27 '22 at 06:00
  • I have updated the question, I want solution in python, so that the date I am inserting in mysql goes in timestamp format – DCVOID Apr 27 '22 at 06:19

1 Answers1

2

You could use STR_TO_DATE:

SELECT STR_TO_DATE('26 Apr 2022 17:23:17 GMT', '%d %b %Y %H:%i:%s');
-- 2022-04-26 17:23:17

Using your Python script:

sql = """INSERT INTO sample_table (id, name, date)
         SELECT %s, %s, STR_TO_DATE(%s, '%d %b %Y %H:%i:%s')"""
val = (id, name, date)
mycurser.execute(sql, val)
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • If I write this in place of %s, it doesn't work – DCVOID Apr 27 '22 at 06:20
  • After executing the script, still not able to insert data in table, if I only insert id and name using this syntax then it works but with STR_TO_DATE it doesn't – DCVOID Apr 27 '22 at 07:48