MySQL 时间转换

1. 获取当前时间(dateTime 格式)

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2019-04-26 12:01:37 |
+---------------------+
1 row in set (0.00 sec)

2. 将 dateTime 转化为时间戳格式

获取当前时间戳

mysql> SELECT unix_timestamp(now());
+-----------------------+
| unix_timestamp(now()) |
+-----------------------+
|            1556257027 |
+-----------------------+
1 row in set (0.00 sec)
mysql> SELECT unix_timestamp('090826');
+--------------------------+
| unix_timestamp('090826') |
+--------------------------+
|               1251216000 |
+--------------------------+
1 row in set (0.02 sec)
 
mysql> SELECT unix_timestamp('20090826');
+----------------------------+
| unix_timestamp('20090826') |
+----------------------------+
|                 1251216000 |
+----------------------------+
1 row in set (0.02 sec)
 
mysql> SELECT unix_timestamp('900826');
+--------------------------+
| unix_timestamp('900826') |
+--------------------------+
|                651596400 |
+--------------------------+
1 row in set (0.02 sec)
 
mysql> SELECT unix_timestamp('19900826');
+----------------------------+
| unix_timestamp('19900826') |
+----------------------------+
|                  651596400 |
+----------------------------+
1 row in set (0.02 sec)
 
mysql> SELECT unix_timestamp('1990-08-26');
+------------------------------+
| unix_timestamp('1990-08-26') |
+------------------------------+
|                    651596400 |
+------------------------------+
1 row in set (0.03 sec)
 
mysql> SELECT unix_timestamp('1990/08/26');
+------------------------------+
| unix_timestamp('1990/08/26') |
+------------------------------+
|                    651596400 |
+------------------------------+
1 row in set (0.03 sec)

3. 将时间戳转化为 dateTime 格式

mysql> SELECT from_UNIXTIME(1555555555,'%Y-%m-%d %H:%i:%s');
+-----------------------------------------------+
| from_UNIXTIME(1555555555,'%Y-%m-%d %H:%i:%s') |
+-----------------------------------------------+
| 2019-04-18 10:45:55                           |
+-----------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT from_UNIXTIME(1555555555);
+---------------------------+
| from_UNIXTIME(1555555555) |
+---------------------------+
| 2019-04-18 10:45:55       |
+---------------------------+
1 row in set (0.00 sec)