Question

参见 http://doc.mysql.cn/mysql5/refman-5.1-zh.html-chapter/functions.html#date-and-time-functions

2.4.1 日期和时间的列类型

要根据列类型的时间和日期类型来进行转化。

时间和日期类型有五种：DATE（格式：YY-MM-DD）, TIME（格式：HH:MM:SS）, DATETIME, TIMESTAMP（格式：秒数）, YEAR（格式：YYYY）。

表格 3 日期时间

类型	范围	说明
Date	日期(yyyy-mm-dd)
Time	时间(hh:mm:ss)
DateTime	日期与时间組合(yyyy-mm-dd hh:mm:ss)
TimeStamp	yyyymmddhhmmss	unix_timestamp 返回秒数
Year	年份 yyyy

2.4.2 日期和时间的函数

1、获取当前日期时间的函数：

• NOW：返回 DATETIME 类型 YYYY-MM-DD HH:MM:SS
• CURRENT_TIMESTAMP：同 NOW
• CURDATE: 返回 DATE 类型 YYMM-MM-DD
• CURTIME: 返回 TIME 类型 HH:MM:SS
• UNNIX_TIMESTAMP：返回 1970 年至今的秒数

2、时间戳函数 TIMESTAMP：

• unix_timestamp：返回 1970 年至今的秒数。
• from_unixtime：将秒数转化为可读的时间格式。

mysql> select unix_timestamp();
+------------------+
| unix_timestamp() |
+------------------+
|    1490274715 |

mysql> select from_unixtime(1490273328, '%Y-%m-%d %H:%i:%S');
+------------------------------------------------+
| from_unixtime(1490273328, '%Y-%m-%d %H:%i:%S') |
+------------------------------------------------+
| 2017-03-23 20:48:48              |
+------------------------------------------------+
1 row in set (0.00 sec)

3）日期和时间操作函数

月份操作：PERIOD_ADD，PERIOD_DIFF

PERIOD_ADD(P,N)

增加 N 个月到阶段 P（以格式 YYMM 或 YYYYMM)。以格式 YYYYMM 返回值。注意阶段参数 P 不是日期值。

mysql> select PERIOD_ADD(9801,2);
  -> 199803

日期操作

DATE_ADD(date, INTERVAL expr type)
DATE_SUB(date, INTERVAL expr type)
ADDDATE(date, INTERVAL expr type)
SUBDATE(date, INTERVAL expr type)

参数说明：

date 参数是合法的日期表达式。expr 参数是您希望添加的时间间隔。

type 参数可以是下列值：

MICROSECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR,
SECOND_MICROSECOND, MINUTE_MICROSECOND, MINUTE_SECOND,
HOUR_MICROSECOND, HOUR_SECOND, HOUR_MINUTE,
DAY_MICROSECOND, DAY_SECOND, DAY_MINUTE, DAY_HOUR, YEAR_MONT

示例 ：

mysql> select DATE_SUB(CURDATE(),INTERVAL 30 DAY);
+-------------------------------------+
| DATE_SUB(CURDATE(),INTERVAL 30 DAY) |
+-------------------------------------+
| 2016-05-30             |
+-------------------------------------+
1 row in set (0.00 sec)

4） 日期和时间格式的转化函数 ：，

DATE 类型：DATE，DATE_FORMAT，DAYOFWEEK，WEEKDAY，DAYOFYEAR，MONTH，DAYNAME，MONTHNAME，QUARTER，WEEK

TIME 类型：HOUR(time)，MINUTE，SECOND

日期和时间转化：EXTRACT

mysql> SELECT EXTRACT(YEAR FROM '1999-07-02');
  -> 1999

2.4.3 日期和时间统计

1、按年/月/日/时的汇总统计

• 按年汇总，统计： SELECT date_format(ctime, '%Y') AS YEAR, count(*) FROM news GROUP BY YEAR;
• 按月汇总，统计： SELECT date_format(ctime, '%Y-%m') AS MONTH, count(*) FROM news GROUP BY MONTH;

按季度汇总，统计：

SELECT date_format(ctime, '%Y') AS YEAR,FLOOR((date_format(ctime, '%m')+2)/3) AS QUARTER, count(*)
  FROM news
  GROUP BY concat(YEAR,QUARTER);

按日汇总，统计 ：

SELECT date_format(ctime, '%Y-%m-%d') AS DAY, count(*) FROM news GROUP BY DAY;

按时汇总，统计 ：

SELECT date_format(ctime, '%Y-%m-%d %H') AS HOUR,count(*) FROM news GROUP BY HOUR;

某天按时统计 ：

SELECT date_format(ctime, '%Y-%m-%d %H ') AS HOUR,count(*)
    FROM news
    WHERE date_format(ctime, '%Y-%m-%d')='2016-06-20'
    GROUP BY HOUR;

本年统计:

SELECT * FROM tbl_name WHERE year(FROM_UNIXTIME(my_time)) = year(curdate())

查询数据附带季度数:

SELECT id, quarter(FROM_UNIXTIME(my_time)) FROM tbl_name;

本季度统计:

SELECT * FROM tbl_name WHERE quarter(FROM_UNIXTIME(my_time)) = quarter(curdate());

本月统计:

SELECT * FROM tbl_name WHERE month(my_time1) = month(curdate()) and year(my_time2) = year(curdate())

本周统计:
SELECT * FROM tbl_name WHERE month(my_time1) = month(curdate()) and week(my_time2) = week(curdate())

2、查询指定时间段的数据行

说明 ：date_col 是指列类型为 DATETIME 的数据列字段名称, date(column_time) 是将 column_time 转化成 date 格式。DATE_SUB 用来得到时间间隔。

查询一天：

SELECT * FROM tbl_name WHERE to_days(column_time) = to_days(now());
SELECT * FROM tbl_name WHERE date(column_time) = curdate();

查询一周：

SELECT * FROM tbl_name WHERE DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(column_time);

查询一个月：

SELECT * FROM tbl_name WHERE DATE_SUB(CURDATE(), INTERVAL 1 MONTH) <= date(column_time);
SELECT * FROM tbl_name WHERE DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date_col;

查询指定天数

SELECT * FROM tbl_name WHERE DATE_SUB(CURDATE(), INTERVAL 2 DAY) <= date(column_time);

N 天内记录:

SELECT * FROM tbl_name WHERE TO_DAYS(NOW())-TO_DAYS(时间字段)<=N

获取指定时间段的数据行统计数：时间戳秒数

select count(*) from xyq7
where insert_time<unix_timestamp() and 
insert_time>unix_timestamp('2016-04-13');

统计一天内更新的数据行数，约 2500 行。

mysql> select count(*) from xyq7 where unix_timestamp()-insert_time<3600*24;
| count(*) |
+----------+
|   2535 |

3、统计案例

查询指定日期的行

示例 1：有一个会员表，有个 birthday 字段，值为 YYYY-MM-DD 格式，现在要查询一个时间段内过生日的会员，比如 06-03 到 07-08 这个时间段内所有过生日的会员。

Select * From user Where DATE_FORMAT(birthday,' %m-%d') >= '06-03' and DATE_FORMAT(birthday,'%m-%d') <= '07-08';

2.4.4 定时统计：存储过程和定时器

需求：一个庞大的日志表，现每天做定时统计一天的总数，放另一个表中，方便查看，运营。

旧方案：用脚本写好程序、用 linux 的 crontab 定时执行。

第一步：编写存储程序（需了解基本的存储程序的语法）

第二步：开启定时器

show VARIABLES LIKE "event"
| event_scheduler | OFF  |

$ show VARIABLES
| --------------- | ---- |
|                 |      |
$set global event_scheduler=1;

第三步：创建定时任务

语法：

CREATE EVENT 的语法如下：

CREATE EVENT
[IF NOT EXISTS] ---------------------------------------------*标注 1
event_name -----------------------------------------------------*标注 2

ON SCHEDULE schedule ------------------------------------*标注 3
[ON COMPLETION [NOT] PRESERVE] -----------------*标注 4
[ENABLE | DISABLE] ----------------------------------------*标注 5
[COMMENT 'comment'] --------------------------------------*标注 6
DO sql_statement -----------------------------------------------*标注 7