Mysql日期时间格式化 %Y-%m-%d %H:%i:%S

获取当前时间戳

mysql> select unix_timestamp(now());
+-----------------------+
| unix_timestamp(now()) |
+-----------------------+
|            1584524789 |
+-----------------------+
1 row in set (0.00 sec)

mysql> select unix_timestamp();
+------------------+
| unix_timestamp() |
+------------------+
|       1584524524 |
+------------------+
1 row in set (0.00 sec)

获取当前时间

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2020-03-18 17:39:13 |
+---------------------+
1 row in set (0.00 sec)

mysql> select date(now());
+-------------+
| date(now()) |
+-------------+
| 2020-03-18  |
+-------------+
1 row in set (0.00 sec)

获取三天前的时间

mysql> SELECT NOW() - interval 72 hour;
+--------------------------+
| NOW() - interval 72 hour |
+--------------------------+
| 2020-03-15 17:39:44      |
+--------------------------+
1 row in set (0.00 sec)

时间转时间戳

mysql> select unix_timestamp('2018-01-15 09:45:16');
+---------------------------------------+
| unix_timestamp('2018-01-15 09:45:16') |
+---------------------------------------+
|                            1515980716 |
+---------------------------------------+
1 row in set (0.00 sec)

时间戳转时间

mysql> select from_unixtime(1515980716);
+---------------------------+
| from_unixtime(1515980716) |
+---------------------------+
| 2018-01-15 09:45:16       |
+---------------------------+
1 row in set (0.02 sec)

时间戳格式化

mysql> SELECT from_unixtime(1515980716, '%Y-%m-%d %H:%i:%S');
+------------------------------------------------+
| from_unixtime(1515980716, '%Y-%m-%d %H:%i:%S') |
+------------------------------------------------+
| 2018-01-15 09:45:16                            |
+------------------------------------------------+
1 row in set (0.00 sec)

时间格式化

mysql> select date_format(now(), '%Y-%m-%d');
+--------------------------------+
| date_format(now(), '%Y-%m-%d') |
+--------------------------------+
| 2020-03-18                     |
+--------------------------------+
1 row in set (0.00 sec)

mysql> select date_format('2018-01-15 09:45:16', '%Y-%m-%d');
+------------------------------------------------+
| date_format('2018-01-15 09:45:16', '%Y-%m-%d') |
+------------------------------------------------+
| 2018-01-15                                     |
+------------------------------------------------+
1 row in set (0.00 sec)