mysql 时间函数多了8小时_MySQL时间函数了解

NOW() -- 获取当前格式化时间

• 原表

mysql> CREATE TABLE blog (
    ->     id INT PRIMARY KEY auto_increment,
    ->     NAME CHAR (32),
    ->     sub_time datetime
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO blog (NAME, sub_time)
    -> VALUES
    ->     ('第1篇','2015-03-01 11:31:21'),
    ->     ('第2篇','2015-03-11 16:31:21'),
    ->     ('第3篇','2016-07-01 10:21:31'),
    ->     ('第4篇','2016-07-22 09:23:21'),
    ->     ('第5篇','2016-07-23 10:11:11'),
    ->     ('第6篇','2016-07-25 11:21:31'),
    ->     ('第7篇','2017-03-01 15:33:21'),
    ->     ('第8篇','2017-03-01 17:32:21'),
    ->     ('第9篇','2017-03-01 18:31:21');
Query OK, 9 rows affected (0.00 sec)
Records: 9  Duplicates: 0  Warnings: 0

• 对表进行数据插入,插入数据为当前时间

mysql> insert blog(name,sub_time) values('第10篇',NOW());
Query OK, 1 row affected (0.00 sec)

date_format -- 对时间进行格式化操作

• 查看每个月都分别发表了几篇文章

mysql> select date_format(sub_time,'%Y-%m') as 时间,count(id) as 发表数量 from blog group by date_format(sub_time,'%Y-%m');
+---------+--------------+
| 时间    | 发表数量     |
+---------+--------------+
| 2015-03 |            2 |
| 2016-07 |            4 |
| 2017-03 |            3 |
| 2020-09 |            1 |
+---------+--------------+
4 rows in set (0.00 sec)