MySQL日期函数

1、在指定的日期字段上加上一个时间(包括直接的一个时间、年、月、周、日、时、分、秒等等)。

函数格式为：DATE_ADD(date,INTERVAL expr type)，如：
①、在出生日期字段上加1天：DATE_ADD(出生日期,INTERVAL 1 DAY)。
②、在出生日期字段上加1周：DATE_ADD(出生日期,INTERVAL 1 WEEK)。
③、在出生日期字段上加1年：DATE_ADD(出生日期,INTERVAL 1 MONTH)。
其中type取值包括：(汉字前面是关键字，后面表示传入的数据格式)
   SECOND 秒 SECONDS 
　MINUTE 分钟 MINUTES 
　HOUR 时间 HOURS 
　DAY 天 DAYS 
　MONTH 月 MONTHS 
　YEAR 年 YEARS 
　MINUTE_SECOND 分钟和秒 "MINUTES:SECONDS" 
　HOUR_MINUTE 小时和分钟 "HOURS:MINUTES" 
　DAY_HOUR 天和小时 "DAYS HOURS" 
　YEAR_MONTH 年和月 "YEARS-MONTHS" 
　HOUR_SECOND 小时, 分钟， "HOURS:MINUTES:SECONDS" 
　DAY_MINUTE 天, 小时, 分钟 "DAYS HOURS:MINUTES" 
　DAY_SECOND 天, 小时, 分钟, 秒 "DAYS HOURS:MINUTES:SECONDS" WEEK 周 WEEKS

 

2、当前日期

 (1)、获取当前日期(年月日)，使用CURDATE() 或者CURRENT_DATE，以'YYYY-MM-DD'或YYYYMMDD格式返回今天日期值，具体采用哪种格式取决于函数是在一个字符串还是数字上下文被使用。 例如：

1、SELECT CURDATE() 
//结果：2013-03-31

2、SELECT CURRENT_DATE 
//结果：2013-03-31

3、SELECT CURDATE() + 3;
//结果：20130334

 (2)获取当前时间(时分秒)，使用CURTIME() 或者CURRENT_TIME ，以'HH:MM:SS'或HHMMSS格式返回当前时间值，取决于函数是在一个字符串还是在数字的上下文被使用。 

1、SELECT CURRENT_TIME ;
//结果：17:11:46

2、SELECT CURTIME();
//结果：17:12:25

3、SELECT CURTIME() + 0; 
//结果：171303

 (3)、获取当前时间(年月日时分秒)，使用NOW() 或者SYSDATE() ，以'YYYY-MM-DD HH:MM:SS'或YYYYMMDDHHMMSS格式返回当前的日期和时间，取决于函数是在一个字符串还是在数字的上下文被使用。

1、SELECT CURRENT_TIMESTAMP ;
//结果：2013-03-31 17:14:37

2、SELECT NOW();
//结果：2013-03-31 17:15:04

3、SELECT NOW() + 0;
//结果：20130331171517

 (4)、获取时间的秒数，使用UNIX_TIMESTAMP() 或者UNIX_TIMESTAMP(date)，如果没有参数调用，返回一个Unix时间戳记(从'1970-01-01 00:00:00'GMT开始的秒数)。如果UNIX_TIMESTAMP()用一 个date参数被调用，它返回从'1970-01-01 00:00:00' GMT开始的秒数值。date可以是一个DATE字符串、一个DATETIME 字符串、一个TIMESTAMP或以YYMMDD或YYYYMMDD格式的本地时间的一个数字。

注意：当UNIX_TIMESTAMP被用于一个TIMESTAMP列，函数将直接接受值，没有隐含的“string-to-unix-timestamp”变换。

1、SELECT UNIX_TIMESTAMP();
//结果：1364721417

2、SELECT UNIX_TIMESTAMP('1997-10-04 22:23:00');
//结果：875974980

 

3、日期格式化

(1)、将数据库中datetime、date类型的数据格式化成指定的字符串格式：

DATE_FORMAT(date,format) 
根据format字符串格式化date值。下列修饰符可以被用在format字符串中： 
%M 月名字(January……December) 
%W 星期名字(Sunday……Saturday) 
%D 有英语前缀的月份的日期(1st, 2nd, 3rd, 等等。） 
%Y 年, 数字, 4 位 
%y 年, 数字, 2 位 
%a 缩写的星期名字(Sun……Sat) 
%d 月份中的天数, 数字(00……31) 
%e 月份中的天数, 数字(0……31) 
%m 月, 数字(01……12) 
%c 月, 数字(1……12) 
%b 缩写的月份名字(Jan……Dec) 
%j 一年中的天数(001……366) 
%H 小时(00……23) 
%k 小时(0……23) 
%h 小时(01……12) 
%I 小时(01……12) 
%l 小时(1……12) 
%i 分钟, 数字(00……59) 
%r 时间,12 小时(hh:mm:ss [AP]M) 
%T 时间,24 小时(hh:mm:ss) 
%S 秒(00……59) 
%s 秒(00……59) 
%p AM或PM 
%w 一个星期中的天数(0=Sunday ……6=Saturday ） 
%U 星期(0……52), 这里星期天是星期的第一天 
%u 星期(0……52), 这里星期一是星期的第一天 
%% 一个文字“%”。 
所有的其他字符不做解释被复制到结果中。
MySQL3.23中，在格式修饰符字符前需要%。在MySQL更早的版本中，%是可选的。

 举例：

1、SELECT DATE_FORMAT(`notice_time`, '%Y-%m-%d %H:%i:%s') notice_time FROM tbl_bcall_task;
//结果：2013-03-31 16:47:14

2、select DATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y'); 
//结果：Saturday October 1997

3、select DATE_FORMAT('1997-10-04 22:23:00', '%H:%i:%s');
//结果：22:23:00

4、select DATE_FORMAT('1997-10-04 22:23:00', '%D %y %a %d %m %b %j');
//结果：4th 97 Sat 04 10 Oct 277

5、select DATE_FORMAT('1997-10-04 22:23:00', '%H %k %I %r %T %S %w');
//结果：22 22 10 10:23:00 PM 22:23:00 00 6

 (2)、如果格式化的是date类型的，格式化字符串中包含时分秒，则默认为0，例如：

SELECT DATE_FORMAT(`ttt`, '%Y-%m-%d %H:%i:%s') ttt FROM tst
//表tst中的ttt字段类型为date类型，结果：2013-03-31 00:00:00

 (3)、将秒数转换成日期，使用FROM_UNIXTIME(unix_timestamp) ，以'YYYY-MM-DD HH:MM:SS'或YYYYMMDDHHMMSS格式返回unix_timestamp参数所表示的值，是函数UNIX_TIMESTAMP()的逆操作，取决于函数是在一个字符串还是或数字上下文中被使用。

1、SELECT FROM_UNIXTIME(875974980);
//结果：1997-10-04 22:23:00

2、SELECT FROM_UNIXTIME(875996580) + 0;
//结果：19971005042300

 (4)、将指定的秒数格式化成指定的日期格式(秒数标示：从'1970-01-01 00:00:00'GMT开始的秒数)。

1、SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(), '%Y %D %M %h:%i:%s %x'); 
//结果：2013 31st March 05:25:06 2013(UNIX_TIMESTAMP()表示从'1970-01-01 00:00:00'GMT开始到当前的秒数)。

2、SELECT FROM_UNIXTIME(1364721998, '%Y-%m-%d %H:%i:%s'); 
//结果：2013-03-31 17:26:38

(5)、将秒数转换成时分秒格式，变换成小时、分钟和秒，值以'HH:MM:SS'或HHMMSS格式化，取决于函数是在一个字符串还是在数字上下文中被使用。  

1、SELECT SEC_TO_TIME(4378);
//结果：01:12:58

2、SELECT SEC_TO_TIME(4378)+0;
//结果：11258

(6)、将时分秒格式的时间转换成秒数。

1、SELECT TIME_TO_SEC('22:23:00');
//结果：80580

2、SELECT TIME_TO_SEC('00:00:38'); 
//结果：38

  

 

4、时间格式化

主要用于处理日期中的时分秒一下的时间，不包括年月日部分，如果在结果中包含，则默认用0填充，例如(所用格式化修饰符与DATE_FORMAT一样)：

1、SELECT TIME_FORMAT(NOW(),'%H:%i:%s') 
//结果：17:03:02

2、SELECT TIME_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s') 
//结果：SELECT TIME_FORMAT(NOW(),'%H:%i:%s')