MySQL里的日期计算

MySQL提供了几个函数，可以用来计算日期，常用的例子就是，计算年龄或提取日期部分。

1. 计算年龄：

mysql中要想计算一个人的年龄,相当于当前日期的年和出生日期之间的差。如果当前日期的日历年比出生日期早，则减去一年。
通过以下查询来显示出生日期、当前日期和年龄数值的年数字。

SELECT name, birth, CURDATE(),    
 (YEAR(CURDATE())-YEAR(birth)) - (RIGHT(CURDATE(),5)<RIGHT(birth,5)) AS age    
FROM pet; 

结果：
+----------+------------+------------+------+
| name | birth | CURDATE() | age |
+----------+------------+------------+------+
| Fluffys | 1993-02-04 | 2009-10-28 | 10 |
| Claws | 1994-03-17 | 2009-10-28 | 9 |
| Buffy | 1989-05-13 | 2009-10-28 | 14 |
| Fang | 1990-08-27 | 2009-10-28 | 12 |
| Bowser | 1989-08-31 | 2009-10-28 | 13 |
| Chirpy | 1998-09-11 | 2009-10-28 | 4 |
| Whistler | 1997-12-09 | 2009-10-28 | 5 |
| Slim | 1996-04-29 | 2009-10-28 | 7 |
| Puffball | 1999-03-30 | 2009-10-28 | 4 |
+----------+------------+------------+------+

函数解释：
此处，YEAR()提取日期的年部分，RIGHT()提取日期的MM-DD (日历年)部分的最右面5个字符。比较MM-DD值的表达式的结果值一般为1或0，如果CURDATE()的年比birth的年早，则年份应减去1。（这个比较表达式需要理解一下）。
(1). 尽管查询可行，如果以某个顺序排列行，则能更容易地浏览结果。添加ORDER BY name子句按照名字对输出进行排序则能够实现.

SELECT name, birth, CURDATE(),
(YEAR(CURDATE())-YEAR(birth)) - (RIGHT(CURDATE(),5)<RIGHT(birth,5)) AS age
FROM pet ORDER BY name;

+----------+------------+------------+------+
| name | birth | CURDATE() | age |
+----------+------------+------------+------+
| Bowser | 1989-08-31 | 2003-08-19 | 13 |
| Buffy | 1989-05-13 | 2003-08-19 | 14 |
| Chirpy | 1998-09-11 | 2003-08-19 | 4 |
| Claws | 1994-03-17 | 2003-08-19 | 9 |
| Fang | 1990-08-27 | 2003-08-19 | 12 |
| Fluffy | 1993-02-04 | 2003-08-19 | 10 |
| Puffball | 1999-03-30 | 2003-08-19 | 4 |
| Slim | 1996-04-29 | 2003-08-19 | 7 |
| Whistler | 1997-12-09 | 2003-08-19 | 5 |
+----------+------------+------------+------+

(2). 为了按age而非name排序输出，只要再使用一个ORDER BY子句：

SELECT name, birth, CURDATE(),
(YEAR(CURDATE())-YEAR(birth)) - (RIGHT(CURDATE(),5)<RIGHT(birth,5)) AS age
FROM pet ORDER BY age;

+----------+------------+------------+------+
| name | birth | CURDATE() | age |
+----------+------------+------------+------+
| Chirpy | 1998-09-11 | 2003-08-19 | 4 |
| Puffball | 1999-03-30 | 2003-08-19 | 4 |
| Whistler | 1997-12-09 | 2003-08-19 | 5 |
| Slim | 1996-04-29 | 2003-08-19 | 7 |
| Claws | 1994-03-17 | 2003-08-19 | 9 |
| Fluffy | 1993-02-04 | 2003-08-19 | 10 |
| Fang | 1990-08-27 | 2003-08-19 | 12 |
| Bowser | 1989-08-31 | 2003-08-19 | 13 |
| Buffy | 1989-05-13 | 2003-08-19 | 14 |
+----------+------------+------------+------+

(3). 可以使用一个类似的查询来确定已经死亡动物的死亡年龄。你通过检查death值是否是NULL来确定是哪些动物，然后，对于那些非NULL值的动物，需要计算出death和birth值之间的差：

SELECT name, birth, death,
(YEAR(death)-YEAR(birth)) - (RIGHT(death,5)<RIGHT(birth,5)) AS age
FROM pet WHERE death IS NOT NULL ORDER BY age;

+--------+------------+------------+------+
| name | birth | death | age |
+--------+------------+------------+------+
| Bowser | 1989-08-31 | 1995-07-29 | 5 |
+--------+------------+------------+------+

解析：查询使用death IS NOT NULL而非death != NULL，因为NULL是特殊的值，不能使用普通比较符来比较，以后会给出解释。参见3.3.4.6节，“NULL值操作”。

(4). 如果你想要知道哪个动物下个月过生日，怎么办？对于这类计算，年和天是无关的，你只需要提取birth列的月份部分。MySQL提供几个日期部分的提取函数，例如YEAR( )、MONTH( )和DAYOFMONTH( )。在这里MONTH()是适合的函数。为了看它怎样工作，运行一个简单的查询，显示birth和MONTH(birth)的值：
SELECT name, birth, MONTH(birth) FROM pet;
+----------+------------+--------------+
| name | birth | MONTH(birth) |
+----------+------------+--------------+
| Fluffy | 1993-02-04 | 2 |
| Claws | 1994-03-17 | 3 |
| Buffy | 1989-05-13 | 5 |
| Fang | 1990-08-27 | 8 |
| Bowser | 1989-08-31 | 8 |
| Chirpy | 1998-09-11 | 9 |
| Whistler | 1997-12-09 | 12 |
| Slim | 1996-04-29 | 4 |
| Puffball | 1999-03-30 | 3 |
+----------+------------+--------------+

(5). 找出下个月生日的动物也是容易的。假定当前月是4月，那么月值是4，你可以找在5月出生的动物 (5月)，方法是：

SELECT name, birth FROM pet WHERE MONTH(birth) = 5;

+-------+------------+
| name | birth |
+-------+------------+
| Buffy | 1989-05-13 |
+-------+------------+
解析：如果当前月份是12月，就有点复杂了。你不能只把1加到月份数(12)上并寻找在13月出生的动物，因为没有这样的月份。相反，你应寻找在1月出生的动物(1月) 。

(6). 你甚至可以编写查询，不管当前月份是什么它都能工作。采用这种方法不必在查询中使用一个特定的月份，DATE_ADD( )允许在一个给定的日期上加上时间间隔。如果在NOW( )值上加上一个月，然后用MONTH()提取月份，结果产生生日所在月份：

SELECT name, birth FROM pet
WHERE MONTH(birth) = MONTH(DATE_ADD(CURDATE(),INTERVAL 1 MONTH));

完成该任务的另一个方法是加1以得出当前月份的下一个月(在使用取模函数(MOD)后，如果月份当前值是12，则“回滚”到值0)：

SELECT name, birth FROM pet
WHERE MONTH(birth) = MOD(MONTH(CURDATE()), 12) + 1;

注意，MONTH返回在1和12之间的一个数字，且MOD(something,12)返回在0和11之间的一个数字，因此必须在MOD( )以后加1，否则我们将从11月( 11 )跳到1月(1)。

2、查询今天、昨天、7天、近30天、本月、上一月 数据

#今天
select * from 表名 where to_days(时间字段名) = to_days(now());
#昨天
SELECT * FROM 表名 WHERE TO_DAYS( NOW( ) ) – TO_DAYS( 时间字段名) <= 1
#7天
SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(时间字段名)
#近30天
SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(时间字段名)
#本月
SELECT * FROM 表名 WHERE DATE_FORMAT( 时间字段名, ‘%Y%m’ ) = DATE_FORMAT( CURDATE( ) , ‘%Y%m’ )
#上一月
SELECT * FROM 表名 WHERE PERIOD_DIFF( date_format( now( ) , ‘%Y%m’ ) , date_format( 时间字段名, ‘%Y%m’ ) ) =1

MySQL Str to Date （字符串转换为日期）
函数：str_to_date(str, format)

select str_to_date(’08/09/2008′, ‘%m/%d/%Y’); — 2008-08-09
select str_to_date(’08/09/08′ , ‘%m/%d/%y’); — 2008-08-09
select str_to_date(‘08.09.2008′, ‘%m.%d.%Y’); — 2008-08-09
select str_to_date(’08:09:30′, ‘%h:%i:%s’); — 08:09:30
select str_to_date(‘08.09.2008 08:09:30′, ‘%m.%d.%Y %h:%i:%s’); — 2008-08-09 08:09:30 

MySQL 为日期增加一个时间间隔
函数：date_add()

set @dt = now();
select date_add(@dt, interval 1 day); — add 1 day
select date_add(@dt, interval 1 hour); — add 1 hour
select date_add(@dt, interval 1 minute); — …
select date_add(@dt, interval 1 second);
select date_add(@dt, interval 1 microsecond);
select date_add(@dt, interval 1 week);
select date_add(@dt, interval 1 month);
select date_add(@dt, interval 1 quarter);
select date_add(@dt, interval 1 year);
select date_add(@dt, interval -1 day); — sub 1 day

MySQL 日期、时间相减

函数：datediff(date1,date2), timediff(time1,time2)
MySQL datediff(date1,date2)：两个日期相减 date1 – date2，返回天数。
select datediff(‘2008-08-08′, ‘2008-08-01′); — 7
select datediff(‘2008-08-01′, ‘2008-08-08′); — -7
MySQL timediff(time1,time2)：两个日期相减 time1 – time2，返回 time 差值。
select timediff(‘2008-08-08 08:08:08′, ‘2008-08-08 00:00:00′); — 08:08:08
select timediff(’08:08:08′, ’00:00:00′); — 08:08:08

返回月份中的最后一天
MySQL 函数： last_day()

select last_day(‘2008-02-01′); — 2008-02-29
select last_day(‘2008-08-08′); — 2008-08-31

MySQL last_day()函数非常有用，比如我想得到当前月份中有多少天，可以这样来计算：
mysql> select now(), day(last_day(now())) as days;
+—————————————————————+————+
| now() |days|
+—————————————————————+————+
| 2008-08-09 11:45:45 | 31 |
+—————————————————————+————+

选取日期时间的各个部分：
日期、时间、年、季度、月、日、小时、分钟、秒、微秒

set @dt = ‘2008-09-10 07:15:30.123456′;
select date(@dt); — 2008-09-10
select time(@dt); — 07:15:30.123456
select year(@dt); — 2008
select quarter(@dt); — 3
select month(@dt); — 9
select week(@dt); — 36
select day(@dt); — 10
select hour(@dt); — 7
select minute(@dt); — 15
select second(@dt); — 30
select microsecond(@dt); — 123456

MySQL Extract() 函数
可以上面实现类似的功能：

set @dt = ‘2008-09-10 07:15:30.123456′;
select extract(year from @dt); — 2008
select extract(quarter from @dt); — 3
select extract(month from @dt); — 9
select extract(week from @dt); — 36
select extract(day from @dt); — 10
select extract(hour from @dt); — 7
select extract(minute from @dt); — 15
select extract(second from @dt); — 30
select extract(microsecond from @dt); — 123456

select extract(year_month from @dt); — 200809
select extract(day_hour from @dt); — 1007
select extract(day_minute from @dt); — 100715
select extract(day_second from @dt); — 10071530
select extract(day_microsecond from @dt); — 10071530123456
select extract(hour_minute from @dt); — 715
select extract(hour_second from @dt); — 71530
select extract(hour_microsecond from @dt); — 71530123456
select extract(minute_second from @dt); — 1530
select extract(minute_microsecond from @dt); — 1530123456
select extract(second_microsecond from @dt); — 30123456

参考：
[1] http://www.cnblogs.com/tv151579/archive/2012/08/16/2641078.html
[2] http://www.gosoa.com.cn/mysql查询今天、昨天、7天、近30天、本月、上一月-数据