一、获取当前日期、时间函数
- now()
- current_timestamp()
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2019-05-15 13:49:03 |
+---------------------+
1 row in set
mysql> select current_timestamp();
+---------------------+
| current_timestamp() |
+---------------------+
| 2019-05-15 14:32:06 |
+---------------------+
1 row in set
二、日期格式与时间戳互相转换
- unix_timestamp() 将日期、时间转换为时间戳
- from_unixtime() 将unix时间戳转换为 日期、时间格式
mysql> select unix_timestamp();
+------------------+
| unix_timestamp() |
+------------------+
| 1557902324 |
+------------------+
1 row in set
mysql> select unix_timestamp('2019-05-01 12:20:20');
+---------------------------------------+
| unix_timestamp('2019-05-01 12:20:20') |
+---------------------------------------+
| 1556684420 |
+---------------------------------------+
1 row in set
mysql> select from_unixtime(1556684420);
+---------------------------+
| from_unixtime(1556684420) |
+---------------------------+
| 2019-05-01 12:20:20 |
+---------------------------+
1 row in set
三、日期时间计算函数
- date_add() 给日期增加一个时间间隔
- date_sub() 给日期减去一个时间间隔
mysql> set @dt = now();
Query OK, 0 rows affected
mysql> select date_add(@dt, interval 1 day);
+-------------------------------+
| date_add(@dt, interval 1 day) |
+-------------------------------+
| 2019-05-16 14:50:05 |
+-------------------------------+
1 row in set
date_add(@dt, interval 1 hour)
date_add(@dt, interval 1 week)
date_add(@dt, interval 1 year)
- datediff(date1,date2) 计算两个日期之间的差值
- timediff(time1,time2) 计算两个时间点之间的差值
mysql> select datediff('2019-05-15 10:15:30','2019-05-05 13:16:22');
+-------------------------------------------------------+
| datediff('2019-05-15 10:15:30','2019-05-05 13:16:22') |
+-------------------------------------------------------+
| 10 |
+-------------------------------------------------------+
1 row in set
mysql> select timediff('2019-05-15 10:15:30','2019-05-
05 13:16:22');
+-------------------------------------------------------+
| timediff('2019-05-15 10:15:30','2019-05-05 13:16:22') |
+-------------------------------------------------------+
| 236:59:08 |
+-------------------------------------------------------+
1 row in set
mysql> select timediff('22:15:30','13:16:22');
+---------------------------------+
| timediff('22:15:30','13:16:22') |
+---------------------------------+
| 08:59:08 |
+---------------------------------+
1 row in set
先写这么多，后期会继续更新。
