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可以写成其他值,这样更利于理解。