MySQL中的时间函数

UNIX_TIMESTAMP() 返回当前时间戳

mysql> select UNIX_TIMESTAMP();
+------------------+
| UNIX_TIMESTAMP() |
+------------------+
|       1593576560 |
+------------------+
1 row in set (0.00 sec)

UNIX_TIMESTAMP(datetime) 返回指定日期的时间戳

这个函数和上一个函数一样，只是增加了参数

mysql> select UNIX_TIMESTAMP('2020-07-01 12:11:00');
+---------------------------------------+
| UNIX_TIMESTAMP('2020-07-01 12:11:00') |
+---------------------------------------+
|                            1593576660 |
+---------------------------------------+
1 row in set (0.01 sec)

FROM_UNIXTIME 将时间戳转换成datetime格式

mysql> select FROM_UNIXTIME(UNIX_TIMESTAMP());
+---------------------------------+
| FROM_UNIXTIME(UNIX_TIMESTAMP()) |
+---------------------------------+
| 2020-07-01 12:11:00             |
+---------------------------------+
1 row in set (0.00 sec)

mysql> select FROM_UNIXTIME(1593576660);
+---------------------------+
| FROM_UNIXTIME(1593576660) |
+---------------------------+
| 2020-07-01 12:11:00       |
+---------------------------+
1 row in set (0.01 sec)

mysql> select FROM_UNIXTIME(createtime), FROM_UNIXTIME(starttime) from project limit 1 \G
*************************** 1. row ***************************
FROM_UNIXTIME(createtime): 2016-01-20 16:59:25
 FROM_UNIXTIME(starttime): 2016-01-20 00:30:00
1 row in set (0.00 sec)

DATE_FORMAT格式化一个datetime时间

mysql> select DATE_FORMAT(FROM_UNIXTIME(1593576444), '%Y/%m/%d %H/%i/%s %M %p');
+-------------------------------------------------------------------+
| DATE_FORMAT(FROM_UNIXTIME(1593576444), '%Y/%m/%d %H/%i/%s %M %p') |
+-------------------------------------------------------------------+
| 2020/07/01 12/07/24 July PM                                       |
+-------------------------------------------------------------------+
1 row in set (0.00 sec)