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)