ITPub博客

首页 > 数据库 > MySQL > mysql日期和时间函数学习

mysql日期和时间函数学习

原创 MySQL 作者:to_be_dba 时间:2015-10-19 20:07:27 0 删除 编辑

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


基本函数的使用

如:

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<'20

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<'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)

 

mysql> select * from test_date where v_date>='2015-10-09' and v_date<'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)


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

请登录后发表评论 登录
全部评论

注册时间:2011-11-23

  • 博文量
    158
  • 访问量
    422578