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/，如需轉載，請註明出處，否則將追究法律責任。