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

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

一个简单的实例如下

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
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)

II. 其他

1. 一灰灰Bloghttps://liuyueyi.github.io/hexblog

一灰灰的个人博客,记录所有学习和工作中的博文,欢迎大家前去逛逛

2. 声明

尽信书则不如,已上内容,纯属一家之言,因个人能力有限,难免有疏漏和错误之处,如发现bug或者有更好的建议,欢迎批评指正,不吝感激

3. 扫描关注

一灰灰blog

QrCode

知识星球

goals