CODEWITHSUNDEEP
pythonmysqlsqlmysql-pythonmysql-connector-python
Shounak Das
Shounak Das

Reputation: 565

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)

Upvotes: 1

Views: 135

Answers (3)

forpas
forpas

Reputation: 164064

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;

Upvotes: 2

scsanty
scsanty

Reputation: 356

You can declare the column as datatype TEXT if you want to retain a specific format.

Create table dates ( Date TEXT, name TEXT )

strftime is anyway converting to a string, anyway.

Or DATE, if you just want to get rid of the time stamp. Create table dates ( Date DATE, name TEXT )

in this case you don't need to convert the datetime object to string. It will cast into the target datatype by itself.

Upvotes: 0

Vikash Singh
Vikash Singh

Reputation: 14001

This problem has nothing to do with python, once you insert a date in a datetime field in mysql it will append time and seconds as 0 automatically.

You want to select date field like so:

SELECT DATE(Date), name FROM dates;

Upvotes: 2

Related Questions