How to ignore fractional seconds with MySQL 5.6?

Question

MySQL 5.6.4 and up supports fractional seconds for temporal column type. http://dev.mysql.com/doc/refman/5.6/en/fractional-seconds.html

But I want to ignore fractional seconds part for compatibility.

mysql> CREATE TABLE `t1` (`created` datetime(0) NOT NULL);
Query OK, 0 rows affected (0.41 sec)

mysql> INSERT INTO t1 set created = '2013-08-27 05:13:21.999';
mysql> INSERT INTO t1 set created = '2013-08-27 23:59:59.999';


mysql> select * from t1;
+---------------------+
| created             |
+---------------------+
| 2013-08-27 05:13:22 |
| 2013-08-28 00:00:00 |
+---------------------+
2 row in set (0.00 sec)

Expected result is '2013-08-27 05:13:21' and '2013-08-27 23:59:59'.

How do I save same result with previous version of MySQL?

Answer 1

Try to create the table as below:

CREATE TABLE t1 (dt DATETIME(6) NOT NULL);

SQL Fiddle