mysql> select year('2012-03-20');+--------------------+| year('2012-03-20') |+--------------------+|               2012 |+--------------------+1 row in set (0.00 sec)mysql> select month('2012-03-20');+---------------------+| month('2012-03-20') |+---------------------+|                   3 |+---------------------+1 row in set (0.00 sec)mysql> select day('2012-03-20');+-------------------+| day('2012-03-20') |+-------------------+|                20 |+-------------------+1 row in set (0.00 sec)mysql> select hour('12:30:55');+------------------+| hour('12:30:55') |+------------------+|               12 |+------------------+1 row in set (0.00 sec)mysql> select minute('12:30:55');+--------------------+| minute('12:30:55') |+--------------------+|                 30 |+--------------------+1 row in set (0.00 sec)mysql> select second('12:30:55');+--------------------+| second('12:30:55') |+--------------------+|                 55 |+--------------------+1 row in set (0.00 sec)

mysql> SELECT UNIX_TIMESTAMP('2005-03-27 02:00:00');+---------------------------------------+| UNIX_TIMESTAMP('2005-03-27 02:00:00') |+---------------------------------------+|                            1111885200 |+---------------------------------------+mysql> SELECT FROM_UNIXTIME(1111885200);+---------------------------+| FROM_UNIXTIME(1111885200) |+---------------------------+| 2005-03-27 03:00:00       |+---------------------------+

SELECT UNIX_TIMESTAMP('2012-01-01 00:00:00');SELECT UNIX_TIMESTAMP('2012-07-30 00:00:00');SELECT UNIX_TIMESTAMP();SELECT UNIX_TIMESTAMP('2009-08-06') ;SELECT UNIX_TIMESTAMP( curdate( ) );select FROM_UNIXTIME(UNIX_TIMESTAMP('2012-07-30 00:00:00'), '%Y-%m-%d');SELECT FROM_UNIXTIME( 1249488000, '%Y年%m月%d日' );select FROM_UNIXTIME(createtime, '%m') as month, count(1) as count from members where createtime BETWEEN UNIX_TIMESTAMP('2012-01-01 00:00:00') and UNIX_TIMESTAMP('2012-12-31 00:00:00') group by FROM_UNIXTIME(createtime, '%m');select FROM_UNIXTIME(createtime, '%m') as month, count(1) as count from members where createtime BETWEEN UNIX_TIMESTAMP('2011-01-01 00:00:00') and UNIX_TIMESTAMP('2011-12-31 00:00:00') group by FROM_UNIXTIME(createtime, '%m');select FROM_UNIXTIME(createtime, '%m-%d') as month, count(1) as count from members where createtime BETWEEN UNIX_TIMESTAMP('2011-01-01 00:00:00') and UNIX_TIMESTAMP('2011-12-31 00:00:00') group by FROM_UNIXTIME(createtime, '%m-%d');select FROM_UNIXTIME(createtime, '%m-%d') as month, count(1) as count from members where createtime BETWEEN UNIX_TIMESTAMP('2012-01-01 00:00:00') and UNIX_TIMESTAMP('2012-12-31 00:00:00') group by FROM_UNIXTIME(createtime, '%m-%d');

DATE_FORMAT() 函数用于以不同的格式显示日期/时间数据。

语法DATE_FORMAT(date,format)date 参数是合法的日期。format 规定日期/时间的输出格式。可以使用的格式有：格式	描述%a	缩写星期名%b	缩写月名%c	月，数值%D	带有英文前缀的月中的天%d	月的天，数值(00-31)%e	月的天，数值(0-31)%f	微秒%H	小时 (00-23)%h	小时 (01-12)%I	小时 (01-12)%i	分钟，数值(00-59)%j	年的天 (001-366)%k	小时 (0-23)%l	小时 (1-12)%M	月名%m	月，数值(00-12)%p	AM 或 PM%r	时间，12-小时（hh:mm:ss AM 或 PM）%S	秒(00-59)%s	秒(00-59)%T	时间, 24-小时 (hh:mm:ss)%U	周 (00-53) 星期日是一周的第一天%u	周 (00-53) 星期一是一周的第一天%V	周 (01-53) 星期日是一周的第一天，与 %X 使用%v	周 (01-53) 星期一是一周的第一天，与 %x 使用%W	星期名%w	周的天 （0=星期日, 6=星期六）%X	年，其中的星期日是周的第一天，4 位，与 %V 使用%x	年，其中的星期一是周的第一天，4 位，与 %v 使用%Y	年，4 位%y	年，2 位

实例

下面的脚本使用 DATE_FORMAT() 函数来显示不同的格式。我们使用 NOW() 来获得当前的日期/时间：DATE_FORMAT(NOW(),'%b %d %Y %h:%i %p')DATE_FORMAT(NOW(),'%m-%d-%Y')DATE_FORMAT(NOW(),'%d %b %y')DATE_FORMAT(NOW(),'%d %b %Y %T:%f')SELECT DATE_FORMAT(NOW(),'%Y-%m-%d');select DATE_FORMAT(asctime,'%Y-%m-%d') as Date, count(1) as Count from logging where tag='www' and facility='login' group by DATE_FORMAT(asctime,'%Y-%m-%d') order by asctime desc;

当前时间向后推10天

mysql> select DATE_SUB(now(), INTERVAL 240 HOUR);+------------------------------------+| DATE_SUB(now(), INTERVAL 240 HOUR) |+------------------------------------+| 2012-03-09 10:26:03                |+------------------------------------+1 row in set (0.00 sec)mysql> select DATE_SUB(now(), INTERVAL 24 HOUR);+-----------------------------------+| DATE_SUB(now(), INTERVAL 24 HOUR) |+-----------------------------------+| 2012-03-18 10:28:43               |+-----------------------------------+1 row in set (0.00 sec)

DELETE from Message where created < DATE_sub(now(), INTERVAL 240 HOUR);select * from PRICES_HISTORY where DATE_FORMAT(TIME ,GET_FORMAT(DATE,'ISO')) = (select if ( WEEKDAY(CURRENT_DATE())=6 , DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY) , CURRENT_DATE()))

SELECT DATE_ADD('1998-01-02', INTERVAL 31 DAY);

计算时间差，两个时间相减结果

mysql> select timediff('22:20:00','17:30:00');+---------------------------------+| timediff('22:20:00','17:30:00') |+---------------------------------+| 04:50:00                        |+---------------------------------+1 row in set (0.00 sec)mysql> select datediff('2008-08-08 12:00:00', '2008-08-01 00:00:00');+--------------------------------------------------------+| datediff('2008-08-08 12:00:00', '2008-08-01 00:00:00') |+--------------------------------------------------------+|                                                      7 |+--------------------------------------------------------+1 row in set (0.00 sec)