MySQL 常用的时间、日期函数

一、获取当前日期、时间函数

• 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

先写这么多，后期会继续更新。