MySQL里面的一些时间函数

01

MySQL里面关于时间的几个函数

今天周天，下午看了一会儿书，主要还是在看电视剧，下午看官方文档的时候了解了几个不常用的时间函数，不过还是记在这里，算是一个积累吧，后续可能会用得着。

curdate()函数

这个函数是用来获取当前的时间的，其实它有两种写法：

mysql:yeyztest 18:55:59>>select curdate();
+------------+
| curdate()  |
+------------+
| 2019-08-11 |
+------------+
1 row in set (0.00 sec)

mysql:yeyztest 22:44:26>>select current_date();
+----------------+
| current_date() |
+----------------+
| 2019-08-11     |
+----------------+
1 row in set (0.00 sec)

timestampdiff函数

这个函数是用来计算指定的日志和当前日期之间的差距的，可以换算成年year，也可以换算成月month，还可以换算成日day，废话不多说，看例子，首先创建表，然后插入数据：

mysql:yeyztest 18:11:08>>create table test5 (id int,birth date);        
Query OK, 0 rows affected (0.01 sec)

mysql:yeyztest 18:12:22>>insert into test5 values (4,'1993-05-04');
Query OK, 1 row affected (0.00 sec)

mysql:yeyztest 18:12:29>>insert into test5 values (2,'1993-07-22');
Query OK, 1 row affected (0.00 sec)

然后我们来看这个函数的作用：

mysql:yeyztest 18:12:37>>select *,curdate(),timestampdiff(year,birth,curdate()) as age from test5 ;
+------+------------+------------+------+
| id   | birth      | curdate()  | age  |
+------+------------+------------+------+
|    4 | 1993-05-04 | 2019-08-11 |   26 |
|    2 | 1993-07-22 | 2019-08-11 |   26 |
+------+------------+------------+------+
2 rows in set (0.00 sec)

mysql:yeyztest 18:13:20>>select *,curdate(),timestampdiff(month,birth,curdate()) as age from test5;       
+------+------------+------------+------+
| id   | birth      | curdate()  | age  |
+------+------------+------------+------+
|    4 | 1993-05-04 | 2019-08-11 |  315 |
|    2 | 1993-07-22 | 2019-08-11 |  312 |
+------+------------+------------+------+
2 rows in set (0.00 sec)

mysql:yeyztest 18:13:34>>select *,curdate(),timestampdiff(day,birth,curdate()) as age from test5;     
+------+------------+------------+------+
| id   | birth      | curdate()  | age  |
+------+------------+------------+------+
|    4 | 1993-05-04 | 2019-08-11 | 9595 |
|    2 | 1993-07-22 | 2019-08-11 | 9516 |
+------+------------+------------+------+
2 rows in set (0.00 sec)

我们通过该函数timestampdiff函数，搭配year、month、day三个时间维度参数，可以得到指定日期到当前日志的年份差距、月份差距、和天数差距。

year、month、day、hour、minute、second函数

这6个函数，可以将以往的某个日期记录，只显示它的年份、月份、日期、小时、分钟、秒等信息。举例如下：

mysql:yeyztest 18:16:20>>select id,birth,year(birth) from test5;    
+------+------------+-------------+
| id   | birth      | year(birth) |
+------+------------+-------------+
|    4 | 1993-05-04 |        1993 |
|    2 | 1993-07-22 |        1993 |
+------+------------+-------------+
2 rows in set (0.00 sec)

mysql:yeyztest 18:15:58>>select id,birth,month(birth) from test5;
+------+------------+--------------+
| id   | birth      | month(birth) |
+------+------------+--------------+
|    4 | 1993-05-04 |            5 |
|    2 | 1993-07-22 |            7 |
+------+------------+--------------+
2 rows in set (0.00 sec)

mysql:yeyztest 18:16:11>>select id,birth,day(birth) from test5;       
+------+------------+------------+
| id   | birth      | day(birth) |
+------+------------+------------+
|    4 | 1993-05-04 |          4 |
|    2 | 1993-07-22 |         22 |
+------+------------+------------+
2 rows in set (0.00 sec)


mysql:yeyztest 22:49:44>>select * ,hour(birth) from test2;            
+------+---------------------+-------------+
| id   | birth               | hour(birth) |
+------+---------------------+-------------+
|    1 | 1993-05-04 11:12:13 |          11 |
+------+---------------------+-------------+
1 row in set (0.00 sec)

mysql:yeyztest 22:49:56>>select * ,minute(birth) from test2; 
+------+---------------------+---------------+
| id   | birth               | minute(birth) |
+------+---------------------+---------------+
|    1 | 1993-05-04 11:12:13 |            12 |
+------+---------------------+---------------+
1 row in set (0.00 sec)

mysql--dba_admin@127.0.0.1:yeyztest 22:49:59>>select * ,second(birth) from test2;      
+------+---------------------+---------------+
| id   | birth               | second(birth) |
+------+---------------------+---------------+
|    1 | 1993-05-04 11:12:13 |            13 |
+------+---------------------+---------------+
1 row in set (0.00 sec)

date_add函数

这个函数可以计算和当前时间差距若干个时间单位的时间，如下是查询当前时间的上一个月的时间：

mysql:yeyztest 18:20:47>>select id,birth from test5 where month(birth)=month(date_add(curdate(),interval -1 month));
+------+------------+
| id   | birth      |
+------+------------+
|    2 | 1993-07-22 |
+------+------------+
1 row in set (0.00 sec)

当然，这里的-1可以写成其他值，这样更利于理解。