关于日期处理的实例:
从mysql给出的 example 这个是官方源码下载以及导入,http://dev.mysql.com/doc/employee/en/employees-installation.html
然后执行下面的操作:
mysql> create table employees like employees.employees; Query OK, 0 rows affected (0.11 sec) mysql> desc employees; +------------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+---------------+------+-----+---------+-------+ | emp_no | int(11) | NO | PRI | NULL | | | birth_date | date | NO | | NULL | | | first_name | varchar(14) | NO | | NULL | | | last_name | varchar(16) | NO | | NULL | | | gender | enum('M','F') | NO | | NULL | | | hire_date | date | NO | | NULL | | +------------+---------------+------+-----+---------+-------+ 6 rows in set (0.00 sec) 从其他数据库中指定的表中导入数据，employees.employees 导入前10条数据 mysql> insert into employees select * from employees.employees limit 10; Query OK, 10 rows affected (0.04 sec) Records: 10 Duplicates: 0 Warnings: 0 仅查询employess表中的last_name,first_name和birthd_date三个数据 mysql> select concat(last_name,' ',first_name) as name,birth_date as birthday from employees; mysql> select concat(last_name,' ',first_name) as name,birth_date as birthday from employees; +--------------------+------------+ | name | birthday | +--------------------+------------+ | Facello Georgi | 1953-09-02 | | Simmel Bezalel | 1964-06-02 | | Bamford Parto | 1959-12-03 | | Koblick Chirstian | 1954-05-01 | | Maliniak Kyoichi | 1955-01-21 | | Preusig Anneke | 1953-04-20 | | Zielinski Tzvetan | 1957-05-23 | | Kalloufi Saniya | 1958-02-19 | | Peac Sumant | 1952-04-19 | | Piveteau Duangkaew | 1963-06-01 | | amos li | 1972-02-29 | +--------------------+------------+ 11 rows in set (0.00 sec)
然后执行下面的命令:用来计算每位员工的出生日期与当前日期相差的年份,以及当前的日期.
select concat(last_name,first_name) as name,birth_date as birthday,(YEAR(now())-YEAR(birth_date)) as diff, now() as today from employees;
+-------------------+------------+------+---------------------+ | name | birthday | diff | today | +-------------------+------------+------+---------------------+ | FacelloGeorgi | 1953-09-02 | 60 | 2013-12-08 02:12:54 | | SimmelBezalel | 1964-06-02 | 49 | 2013-12-08 02:12:54 | | BamfordParto | 1959-12-03 | 54 | 2013-12-08 02:12:54 | | KoblickChirstian | 1954-05-01 | 59 | 2013-12-08 02:12:54 | | MaliniakKyoichi | 1955-01-21 | 58 | 2013-12-08 02:12:54 | | PreusigAnneke | 1953-04-20 | 60 | 2013-12-08 02:12:54 | | ZielinskiTzvetan | 1957-05-23 | 56 | 2013-12-08 02:12:54 | | KalloufiSaniya | 1958-02-19 | 55 | 2013-12-08 02:12:54 | | PeacSumant | 1952-04-19 | 61 | 2013-12-08 02:12:54 | | PiveteauDuangkaew | 1963-06-01 | 50 | 2013-12-08 02:12:54 | | amosli | 1972-02-29 | 41 | 2013-12-08 02:12:54 | +-------------------+------------+------+---------------------+ 11 rows in set (0.00 sec)
接下来是为了计算今年和明年的生日,注意如果生日为２月２９日,且目标日期不是闰月,那么这两列所包含的将是２月２８日,而非３月１日.
select name,birthday,today,date_add(birthday,interval diff year) as cur,date_add(birthday,interval diff+1 year ) as next from (select concat(last_name,first_name) as name,birth_date as birthday,(YEAR(now())-YEAR(birth_date)) as diff, now() as today from employees) as a;
+-------------------+------------+---------------------+------------+------------+ | name | birthday | today | cur | next | +-------------------+------------+---------------------+------------+------------+ | FacelloGeorgi | 1953-09-02 | 2013-12-08 02:14:27 | 2013-09-02 | 2014-09-02 | | SimmelBezalel | 1964-06-02 | 2013-12-08 02:14:27 | 2013-06-02 | 2014-06-02 | | BamfordParto | 1959-12-03 | 2013-12-08 02:14:27 | 2013-12-03 | 2014-12-03 | | KoblickChirstian | 1954-05-01 | 2013-12-08 02:14:27 | 2013-05-01 | 2014-05-01 | | MaliniakKyoichi | 1955-01-21 | 2013-12-08 02:14:27 | 2013-01-21 | 2014-01-21 | | PreusigAnneke | 1953-04-20 | 2013-12-08 02:14:27 | 2013-04-20 | 2014-04-20 | | ZielinskiTzvetan | 1957-05-23 | 2013-12-08 02:14:27 | 2013-05-23 | 2014-05-23 | | KalloufiSaniya | 1958-02-19 | 2013-12-08 02:14:27 | 2013-02-19 | 2014-02-19 | | PeacSumant | 1952-04-19 | 2013-12-08 02:14:27 | 2013-04-19 | 2014-04-19 | | PiveteauDuangkaew | 1963-06-01 | 2013-12-08 02:14:27 | 2013-06-01 | 2014-06-01 | | amosli | 1972-02-29 | 2013-12-08 02:14:27 | 2013-02-28 | 2014-02-28 | +-------------------+------------+---------------------+------------+------------+ 11 rows in set (0.00 sec)
如果出生的日期是闰月,并且当前的年份不是闰年,那么日期加一,表示３月１日为生日,对于下一个年份使用同样的操作:
select name,birthday,date_add(next,interval if(day(birthday)=29&&day(next)=28,1,0) day) as next,today,date_add(cur,interval if(day(birthday)=29&&day(cur)=28,1,0) day) as cur from(select name,birthday,today,date_add(birthday,interval diff year) as cur,date_add(birthday,interval diff+1 year ) as next from (select concat(last_name,first_name) as name,birth_date as birthday,(YEAR(now())-YEAR(birth_date)) as diff, now() as today from employees) as a) as b;
+-------------------+------------+------------+---------------------+------------+ | name | birthday | next | today | cur | +-------------------+------------+------------+---------------------+------------+ | FacelloGeorgi | 1953-09-02 | 2014-09-02 | 2013-12-08 02:19:07 | 2013-09-02 | | SimmelBezalel | 1964-06-02 | 2014-06-02 | 2013-12-08 02:19:07 | 2013-06-02 | | BamfordParto | 1959-12-03 | 2014-12-03 | 2013-12-08 02:19:07 | 2013-12-03 | | KoblickChirstian | 1954-05-01 | 2014-05-01 | 2013-12-08 02:19:07 | 2013-05-01 | | MaliniakKyoichi | 1955-01-21 | 2014-01-21 | 2013-12-08 02:19:07 | 2013-01-21 | | PreusigAnneke | 1953-04-20 | 2014-04-20 | 2013-12-08 02:19:07 | 2013-04-20 | | ZielinskiTzvetan | 1957-05-23 | 2014-05-23 | 2013-12-08 02:19:07 | 2013-05-23 | | KalloufiSaniya | 1958-02-19 | 2014-02-19 | 2013-12-08 02:19:07 | 2013-02-19 | | PeacSumant | 1952-04-19 | 2014-04-19 | 2013-12-08 02:19:07 | 2013-04-19 | | PiveteauDuangkaew | 1963-06-01 | 2014-06-01 | 2013-12-08 02:19:07 | 2013-06-01 | | amosli | 1972-02-29 | 2014-03-01 | 2013-12-08 02:19:07 | 2013-03-01 | +-------------------+------------+------------+---------------------+------------+ 11 rows in set (0.00 sec)
最后判断今年的生日是否已经过了,如果是,那么返回下一年的生日,最后得到的查询结果如下所示:
select name,birthday,if(cur>today,cur,next) as birth_day from(select name,birthday,date_add(next,interval if(day(birthday)=29&&day(next)=28,1,0) day) as next,today,date_add(cur,interval if(day(birthday)=29&&day(cur)=28,1,0) day) as cur from(select name,birthday,today,date_add(birthday,interval diff year) as cur,date_add(birthday,interval diff+1 year ) as next from (select concat(last_name,first_name) as name,birth_date as birthday,(YEAR(now())-YEAR(birth_date)) as diff, now() as today from employees) as a) as b) as c;
+-------------------+------------+------------+ | name | birthday | birth_day | +-------------------+------------+------------+ | FacelloGeorgi | 1953-09-02 | 2014-09-02 | | SimmelBezalel | 1964-06-02 | 2014-06-02 | | BamfordParto | 1959-12-03 | 2014-12-03 | | KoblickChirstian | 1954-05-01 | 2014-05-01 | | MaliniakKyoichi | 1955-01-21 | 2014-01-21 | | PreusigAnneke | 1953-04-20 | 2014-04-20 | | ZielinskiTzvetan | 1957-05-23 | 2014-05-23 | | KalloufiSaniya | 1958-02-19 | 2014-02-19 | | PeacSumant | 1952-04-19 | 2014-04-19 | | PiveteauDuangkaew | 1963-06-01 | 2014-06-01 | | amosli | 1972-02-29 | 2014-03-01 | +-------------------+------------+------------+ 11 rows in set (0.00 sec)
