|
Linux中MySQL日期和时间戳互相转换的函数和方法
1、date命令转换
[yanfa@test ~]$ date
Wed May 3 22:59:57 CST 2017
[yanfa@test ~]$ date +%s
1493823611
[yanfa@test ~]$ date +%s #时间戳转换为日期
1493823613
[yanfa@test ~]$ date -d @1493823613 #时间戳转换为日期
Wed May 3 23:00:13 CST 2017
2、mysql中转换
mysql> select eventid,clock from events where eventid=2156;
+---------+------------+
| eventid | clock |
+---------+------------+
| 2156 | 1493734037 |
+---------+------------+
1 row in set (0.00 sec)
#FROM_UNIXTIME 可以把时间戳转换为日期
mysql> select eventid,from_unixtime(clock,'%Y-%m-%d %H:%i:%s')from events where eventid=22156;
+---------+------------------------------------------+
| eventid | from_unixtime(clock,'%Y-%m-%d %H:%i:%s') |
+---------+------------------------------------------+
| 2156 | 2017-05-02 22:07:17 |
+---------+------------------------------------------+
1 row in set (0.00 sec)
#把日期转换为时间戳
mysql> SELECT UNIX_TIMESTAMP('2017-05-02 22:07:17');
+---------------------------------------+
| UNIX_TIMESTAMP('2017-05-02 22:07:17') |
+---------------------------------------+
| 1493734037 |
+---------------------------------------+
|