MySQL 时间转换

淡淡的烟草味﹌ 2021-06-24 16:10 354阅读 0赞

### 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)