MySql查询时间戳日期互转

mysql内部提供了时间戳和日期互转的函数方便直接使用

• from_unixtime(time_stamp) : 将时间戳转换为日期
• unix_timestamp(date) : 将指定的日期或者日期字符串转换为时间戳

一个简单的实例如下

mysql> select * from Subscribe;
+----+------------------+-----------+--------+------------+------------+-------+---------+
| id | email            | nick      | status | created    | updated    | extra | channel |
+----+------------------+-----------+--------+------------+------------+-------+---------+
|  1 | bangzewu@126.com | 小灰灰    |      1 | 1523008294 | 1523008294 |       |       0 |
|  2 | test@test.com    | 123       |      2 | 1523008453 | 1523008453 |       |       0 |
+----+------------------+-----------+--------+------------+------------+-------+---------+
2 rows in set (0.00 sec)

mysql> select from_unixtime(updated) from Subscribe limit 1;
+------------------------+
| from_unixtime(updated) |
+------------------------+
| 2018-04-06 17:51:34    |
+------------------------+
1 row in set (0.00 sec)

mysql> select unix_timestamp(from_unixtime(updated)) from Subscribe limit 1;
+----------------------------------------+
| unix_timestamp(from_unixtime(updated)) |
+----------------------------------------+
|                             1523008294 |
+----------------------------------------+
1 row in set (0.00 sec)