MYSQL Datetime, remove seconds

Question

I've been attempting to do this for quite sometime. I have a program which periodically writes rows to a table. (table1) ID Date Text Number The Date column is in format yyyy-mm-dd hh:mm:ss ("2013-08-03 06:26:27") The script which reads the data matches it to another set of data with the date in the same format except that the seconds are exactly 0. "2013-08-03 06:26:00" I need to change the Date data column in (Table 1) so that the seconds column is exactly zero. Currently it is just random values. I have changed it on script level so that it writes the data to the MYSQL table so that the seconds is 0. However I have a lot of existing data which I can not loose which does not have the seconds at 0.

Accepted Answer

This is just a matter of updating the corresponding column.

Depending on ... hum ... your mood (?) you might try:

```
update tbl set datetime_column = substr(datetime_column, 1, 16);
```

Or

```
update tbl set datetime_column = date_format(datetime_column, '%Y-%m-%d %H:%i:00');
```

Or

```
update tbl set datetime_column = datetime_column - second(datetime_column);
```

MYSQL Datetime, remove seconds

Solution

Problem