mysql timestamp 和 date比较

1.问题： 列名为create_time代表我新数据的生成时间，如果想提取某一天的数据，该如何比较
注意：create_time为timestamp类型，mysql允许自动生成这个时间，所以表中有这一列为了记录数据的产生时间。
需要提取某一天的数据，例如这一天为：2016-07-29，如何比较？

一种方案：timestamp转为date直接比较
“`
 DATE(expr)
Extracts the date part of the date or datetime expression *expr
*.
mysql> **SELECT DATE(‘2003-12-31 01:02:03’);
** -> ‘2003-12-31’

官网给出的解释如上：
测试：
 ```sql
MariaDB [prf]> create table  time_test(id int ,time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP);
Query OK, 0 rows affected (0.01 sec)
MariaDB [prf]> insert into time_test values(1,null);
Query OK, 1 row affected (0.00 sec)

MariaDB [prf]> select * from time_test;
+------+---------------------+
| id   | time                |
+------+---------------------+
|    1 | 2016-07-29 21:07:38 |
+------+---------------------+
1 row in set (0.00 sec)

MariaDB [prf]> select * from time_test where date(time) ='2016-07-29';
+------+---------------------+
| id   | time                |
+------+---------------------+
|    1 | 2016-07-29 21:07:38 |
+------+---------------------+
1 row in set (0.01 sec)

MariaDB [prf]> select * from time_test;
+------+---------------------+
| id   | time                |
+------+---------------------+
|    1 | 2016-07-29 21:07:38 |
|    2 | 2016-07-28 14:23:22 |
|    2 | 2016-07-27 14:23:22 |
|    2 | 2016-07-26 14:23:22 |
+------+---------------------+
4 rows in set (0.00 sec)

MariaDB [prf]> create index time_index on time_test(time);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [prf]> select * from time_test where date(time)='2016-07-28';
+------+---------------------+
| id   | time                |
+------+---------------------+
|    2 | 2016-07-28 14:23:22 |
+------+---------------------+
1 row in set (0.00 sec)

MariaDB [prf]> explain select * from time_test where date(time)='2016-07-28';
+------+-------------+-----------+------+---------------+------+---------+------+------+-------------+
| id   | select_type | table     | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+------+-------------+-----------+------+---------------+------+---------+------+------+-------------+
|    1 | SIMPLE      | time_test | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using where |
+------+-------------+-----------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

以上有一个问题，虽然能够查询，但是如果我对time这一列进行了索引，由于用了函数，所以这一列查询无法用到索引，依然是全表扫描如何解决这个问题呢？？？

MariaDB [prf]> select * from time_test where time between '2016-07-28' and '2016-07-29';
+------+---------------------+
| id   | time                |
+------+---------------------+
|    2 | 2016-07-28 14:23:22 |
+------+---------------------+
1 row in set (0.00 sec)

MariaDB [prf]> explain select * from time_test where time between '2016-07-28' and '2016-07-29';
+------+-------------+-----------+-------+---------------+------------+---------+------+------+-----------------------+
| id   | select_type | table     | type  | possible_keys | key        | key_len | ref  | rows | Extra                 |
+------+-------------+-----------+-------+---------------+------------+---------+------+------+-----------------------+
|    1 | SIMPLE      | time_test | range | time_index    | time_index | 4       | NULL |    1 | Using index condition |
+------+-------------+-----------+-------+---------------+------------+---------+------+------+-----------------------+
1 row in set (0.00 sec)

以上采用了between and 或者 >= <=都可以做到走索引，而且不用每一行time值都进行date转换，所以效率比较高