mysql日期和时间函数学习

常用的日期和时间函数包括：

基本函数的使用

如：

mysql> select AddDate(now(),1) ;

+---------------------+

| AddDate(now(),1)    |

+---------------------+

| 2015-10-20 14:29:18 |

+---------------------+

1 row in set (0.06 sec)

 

mysql> select adddate(now(),1) ;

+---------------------+

| adddate(now(),1)    |

+---------------------+

| 2015-10-20 14:29:24 |

+---------------------+

1 row in set (0.00 sec)

 

mysql> select addtime(now(),1) ;

+---------------------+

| addtime(now(),1)    |

+---------------------+

| 2015-10-19 14:29:33 |

+---------------------+

1 row in set (0.00 sec)

 

mysql> select curdate() ;

+------------+

| curdate()  |

+------------+

| 2015-10-19 |

+------------+

1 row in set (0.00 sec)

 

 

创建时间戳格式的字段：

mysql> drop table test_date;

Query OK, 0 rows affected (0.28 sec)

 

mysql> create table test_Date(v_date timestamp);

Query OK, 0 rows affected (0.50 sec)

 

插入数据：

mysql> insert into test_Date select curtime();

Query OK, 1 row affected (0.14 sec)

Records: 1  Duplicates: 0  Warnings: 0

 

mysql> insert into test_Date select now();

Query OK, 1 row affected (0.14 sec)

Records: 1  Duplicates: 0  Warnings: 0

 

mysql> insert into test_Date select curdate();

Query OK, 1 row affected (0.26 sec)

Records: 1  Duplicates: 0  Warnings: 0

 

mysql> select * from test_date;

+---------------------+

| v_date              |

+---------------------+

| 2015-10-19 15:38:29 |

| 2015-10-19 15:38:33 |

| 2015-10-19 00:00:00 |

+---------------------+

3 rows in set (0.00 sec)

 

查询时间为当天时间的，时间不一致的不匹配，只有当天0时的时间匹配：

mysql> select * from test_Date where v_Date=curDate();

+---------------------+

| v_date              |

+---------------------+

| 2015-10-19 00:00:00 |

+---------------------+

1 row in set (0.00 sec)

 

查询某一天的数据，可以使用date函数对字段进行限制来实现

mysql> select * from test_Date where date(v_Date)=curDate();

+---------------------+

| v_date              |

+---------------------+

| 2015-10-19 15:38:29 |

| 2015-10-19 15:38:33 |

| 2015-10-19 00:00:00 |

+---------------------+

3 rows in set (0.00 sec)

 

创建索引：

mysql> create index idx_date on test_date(v_date);

Query OK, 0 rows affected (0.48 sec)

Records: 0  Duplicates: 0  Warnings: 0

 

mysql> explain select * from test_date where date(v_date)='2015-10-19' \G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: test_date

         type: index

possible_keys: NULL

          key: idx_date

      key_len: 4

          ref: NULL

         rows: 3

        Extra: Using where; Using index

1 row in set (0.05 sec)

 

mysql> explain select * from test_date where v_date>='2015-10-19' and v_date

15-10-20' \G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: test_date

         type: index

possible_keys: idx_date

          key: idx_date

      key_len: 4

          ref: NULL

         rows: 3

        Extra: Using where; Using index

1 row in set (0.00 sec)

 

 

在字段v_date上创建索引后，使用date(v_date)作为条件进行查询时无法使用到索引；范围查询时可以用到索引。

 

 

查询一段时间的数据：

 

mysql> insert into test_date select adddate(now(),-10);

Query OK, 1 row affected (0.09 sec)

Records: 1  Duplicates: 0  Warnings: 0

 

mysql> select * from test_date;

+---------------------+

| v_date              |

+---------------------+

| 2015-10-09 15:47:37 |

| 2015-10-19 00:00:00 |

| 2015-10-19 15:38:29 |

| 2015-10-19 15:38:33 |

+---------------------+

4 rows in set (0.00 sec)

 

mysql> select * from test_date where v_date between '2015-10-09' and '2015-10-19';

+---------------------+

| v_date              |

+---------------------+

| 2015-10-09 15:47:37 |

| 2015-10-19 00:00:00 |

+---------------------+

2 rows in set (0.00 sec)

 

（1）使用between and

mysql> select * from test_date where v_date between '2015-10-09' and '2015-10-20';

+---------------------+

| v_date              |

+---------------------+

| 2015-10-09 15:47:37 |

| 2015-10-19 00:00:00 |

| 2015-10-19 15:38:29 |

| 2015-10-19 15:38:33 |

+---------------------+

4 rows in set (0.00 sec)

 

（2）拆分日期后查询

mysql> select * from test_date where year(v_date)='2015' and month(v_date)='10'

and day(v_date)>=9 and day(v_date)<=19;

+---------------------+

| v_date              |

+---------------------+

| 2015-10-09 15:47:37 |

| 2015-10-19 00:00:00 |

| 2015-10-19 15:38:29 |

| 2015-10-19 15:38:33 |

+---------------------+

4 rows in set (0.01 sec)

 

（3）仅使用运算符

mysql> select * from test_date where v_date>='2015-10-09' and v_date

+---------------------+

| v_date              |

+---------------------+

| 2015-10-09 15:47:37 |

| 2015-10-19 00:00:00 |

| 2015-10-19 15:38:29 |

| 2015-10-19 15:38:33 |

+---------------------+

4 rows in set (0.00 sec)

 

mysql> select * from test_date where v_date>='2015-10-09' and v_date

+---------------------+

| v_date              |

+---------------------+

| 2015-10-09 15:47:37 |

| 2015-10-19 00:00:00 |

| 2015-10-19 15:38:29 |

| 2015-10-19 15:38:33 |

+---------------------+

4 rows in set (0.00 sec)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26451536/viewspace-1815029/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26451536/viewspace-1815029/