Search code examples
pythonmysqlsqlmysql-pythonmysql-connector-python

How to neglect timestamps when inserting date to MySQL database

I want to insert a date into MySQL table. But, even if I use strftime(), the timestamps are showing like 00:00:00. How can I get rid of it?

insert_stmt = (
    "insert into dates (Date)"
    "values (%s)"
)

date = datetime.date(2020, 4, 6)
formatted_date = date.strftime('%Y-%m-%d')

data = (formatted_date,)
mycursor.execute(insert_stmt, data)
mydb.commit()

Output:

mysql> select * from dates;
+---------------------+------+
| Date                | name |
+---------------------+------+
| 2020-03-23 00:00:00 | John |
| 2020-03-03 00:00:00 | NULL |
| 2020-04-06 00:00:00 | NULL |
+---------------------+------+
3 rows in set (0.06 sec)
Solution

  • Obviously the column Date has data type DATETIME or TIMESTAMP.
    If you don't need the time part of the column, you should change the data type to DATE:

    ALTER TABLE dates MODIFY Date DATE;