MySQL中的日期操作
获取当前时间
now()
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2018-07-26 15:58:46 |
+---------------------+
1 row in set (0.00 sec)
sysdate()
mysql> select sysdate()
+---------------------+
| sysdate() |
+---------------------+
| 2018-07-26 15:59:19 |
+---------------------+
1 row in set (0.00 sec)
两者之间的区别在于：sysdate()是实时获取的。
mysql> select now(), sleep(3), now();
+---------------------+----------+---------------------+
| now() | sleep(3) | now() |
+---------------------+----------+---------------------+
| 2018-07-26 16:00:14 | 0 | 2018-07-26 16:00:14 |
+---------------------+----------+---------------------+
1 row in set (3.00 sec)
mysql> select sysdate(), sleep(3), sysdate();
+---------------------+----------+---------------------+
| sysdate() | sleep(3) | sysdate() |
+---------------------+----------+---------------------+
| 2018-07-26 16:00:30 | 0 | 2018-07-26 16:00:33 |
+---------------------+----------+---------------------+
1 row in set (3.00 sec)
current_timestamp,current_timestamp()
mysql> select current_timestamp, current_timestamp();
+---------------------+---------------------+
| current_timestamp | current_timestamp() |
+---------------------+---------------------+
| 2018-07-26 16:04:01 | 2018-07-26 16:04:01 |
+---------------------+---------------------+
1 row in set (0.00 sec)
日期、时间转换
感觉时间与日期这块有挺多操作的，包括与字符串的相互转换等。
日期/时间转字符串
date_format()/time_format()将日期/时间转换成字符串
mysql> select date_format('2018-7-26 16:05:11', '%Y%m%d%H%i%s')
+---------------------------------------------------+
| date_format('2018-7-26 16:05:11', '%Y%m%d%H%i%s') |
+---------------------------------------------------+
| 20180726160511 |
+---------------------------------------------------+
1 row in set (0.00 sec)
mysql> select time_format('16:05:11', '%H%i%s');
+-----------------------------------+
| time_format('16:05:11', '%H%i%s') |
+-----------------------------------+
| 160511 |
+-----------------------------------+
1 row in set (0.00 sec)
str_to_date()将字符串转成日期类型。
mysql> select str_to_date('07/26/2018', '%m/%d/%Y');
+---------------------------------------+
| str_to_date('07/26/2018', '%m/%d/%Y') |
+---------------------------------------+
| 2018-07-26 |
+---------------------------------------+
1 row in set (0.00 sec)
mysql> select str_to_date('2018/7/26 16:23:33','%Y/%m/%d %H:%i:%s');
+-------------------------------------------------------+
| str_to_date('2018/7/26 16:23:33','%Y/%m/%d %H:%i:%s') |
+-------------------------------------------------------+
| 2018-07-26 16:23:33 |
+-------------------------------------------------------+
1 row in set (0.00 sec)
日期时间计算函数
为日期增加一个时间间隔：date_add()
set @dt = now();
select date_add(@dt, interval 1 day); -- add 1 day
select date_add(@dt, interval 1 hour); -- add 1 hour
select date_add(@dt, interval 1 minute); -- ...
select date_add(@dt, interval 1 second);
select date_add(@dt, interval 1 microsecond);
select date_add(@dt, interval 1 week);
select date_add(@dt, interval 1 month);
select date_add(@dt, interval 1 quarter);
select date_add(@dt, interval 1 year);
select date_add(@dt, interval -1 day); -- sub 1 day
为日期减去一个时间间隔：date_sub()日期、时间相减函数：datediff(date1,date2), timediff(time1,time2)
