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)