MYSQL基礎03(日期函數)

標籤：

工作中對日期的處理是經常遇到的,需求可能多種多樣,因此重點介紹。

1.擷取當前日期

select NOW()-- 結果:2015-10-28 22:41:11select NOW(),SLEEP(3),NOW()-- 結果 2015-10-28 22:43:34  0  2015-10-28 22:43:34SELECT SYSDATE(),SLEEP(3),SYSDATE()-- 結果 2015-10-28 22:46:48  0  2015-10-28 22:46:52-- NOW()是sql執行前就得到,而SYSDATE()是該函數執行時才得到,一般來說NOW()已經足夠,所以更常用

2.日期格式化

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), 這裡星期一是星期的第一天 
　%% 字元% )

-- 根據上面的參數,我們可以得到各種各樣的日期格式,比mssql的convert函數強大太多了,以下是常用的標準日期格式SELECT DATE_FORMAT(NOW(),‘%Y-%m-%d‘) -- 2015-10-28SELECT DATE_FORMAT(NOW(),‘%y-%m-%d‘) -- 15-10-28SELECT DATE_FORMAT(NOW(),‘%Y-%m-%d %H:%m:%s‘) -- 2015-10-28 22:10:56

3.日期截取

-- 擷取日期當天是星期幾(1=星期天,2=星期一....7=星期六)SELECT DAYOFWEEK(now()) -- 擷取日期當天是幾號 SELECT DAYOFMONTH(‘2015-10-28‘)  -- 結果28-- 擷取日期當天是一年中的第幾天SELECT DAYOFYEAR(‘2015-10-28‘)  -- 結果 301-- 擷取日期的年份SELECT YEAR(‘2015-10-28‘) -- 結果 2015-- 擷取日期第幾季度SELECT QUARTER(‘2015-10-28‘)  -- 結果 4-- 擷取日期的月份SELECT MONTH(‘2015-10-28‘) -- 結果 10-- 擷取日期是第幾周SELECT WEEK(‘2015-10-28‘)  -- 結果43 (預設星期天為一周的第一天)SELECT WEEK(‘2015-10-28‘,0)  -- 結果43 (星期天為一周的第一天)SELECT WEEK(‘2015-10-28‘,1)  -- 結果44 (星期一為一周的第一天)-- 擷取日期的小時SELECT HOUR(‘2015-10-28 23:11:56‘) -- 結果 23-- 擷取日期的分鐘SELECT MINUTE(‘2015-10-28 23:11:56‘) -- 結果 11-- 擷取日期的秒鐘SELECT SECOND(‘2015-10-28 23:11:56‘) -- 結果 56
-- 擷取日期部分
SELECT DATE(‘2015-10-28 23:11:56‘) -- 結果‘2015-10-28‘
-- 擷取時間部分
SELECT TIME(‘2015-10-28 23:11:56‘) -- 結果‘23:11:56‘

4.日期運算

DATE_ADD(date,INTERVAL expr type) 

[type值 含義 期望的expr格式]:
　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"
　expr中允許任何標點做分隔字元,如果所有是DATE值時結果是一個DATE值,否則結果是一個DATETIME值)
　如果type關鍵詞不完整,則MySQL從右端取值,DAY_SECOND因為缺少小時分鐘等於MINUTE_SECOND)
　如果增加MONTH、YEAR_MONTH或YEAR,天數大於結果月份的最大天數則使用最大天數) 

-- 說明 正號為加法,負號為減法-- 增加4天SELECT DATE_ADD(‘2015-10-28 23:11:56‘, INTERVAL 4 DAY ) --2015-11-01 23:11:56-- 減少3天SELECT DATE_ADD(‘2015-10-28 23:11:56‘, INTERVAL -3 DAY ) -- 2015-10-25 23:11:56-- 增加1年SELECT DATE_ADD(‘2015-10-28 23:11:56‘, INTERVAL 1 YEAR ) -- 2016-10-28 23:11:56-- 增加1天3小時SELECT DATE_ADD(‘2015-10-28 23:11:56‘, INTERVAL ‘1 3‘ DAY_HOUR ) -- 2015-10-27 20:11:56-- 減1天1小時1分1秒SELECT DATE_ADD(‘2015-10-28 23:11:56‘, INTERVAL ‘-1 1:1:1‘ DAY_SECOND) -- 2015-10-27 22:10:55

5.日期比較

-- 計算2個日期間隔的天數SELECT DATEDIFF(‘2015-10-28‘,‘2015-10-26‘)  -- 結果2-- 計算2個日期間隔的時分秒SELECT TIMEDIFF(‘2015-10-28 23:11:56‘,‘2015-10-28 20:11:56‘) -- 結果 03:00:00

6.日期轉化

-- 將日期轉化為天數 (西元0年至今多少天)SELECT TO_DAYS(‘2015-10-28 23:11:56‘) -- 結果 736264SELECT TO_DAYS(‘0000-00-00 00:00:00‘) -- 結果 0-- 將日期轉化為秒數 (從‘1970-01-01 00:00:00‘GMT開始的秒數)select UNIX_TIMESTAMP(‘2015-10-28 23:11:56‘) -- 結果 1446045116-- 將時間轉化為秒數SELECT TIME_TO_SEC(‘01:00:05‘); -- 結果 3605-- 將秒數轉化為日期SELECT SEC_TO_TIME(3605); -- ‘01:00:05‘

以上 ，參考資料:http://www.cnblogs.com/zeroone/archive/2010/05/05/1727659.html

注意問題

1. DATE_FORMAT

-- 錯誤樣本SELECT * FROM mytable WHERE create_date>=DATE_FORMAT(NOW(),‘%Y-%m-%d‘)-- 說明: DATE_FORMAT返回的是字串,所以不能直接與日期欄位比較,這個問題我工作中已經遇到過了-- 解決辦法 使用DATE,返回日期類型SELECT * FROM mytable WHERE create_date>=DATE(NOW());

功能實現

-- 根據日期擷取當月初與當月末SET @DATE_B:=DATE(DATE_FORMAT(‘2015-10-28‘,‘%Y-%m-1‘));SET @DATE_E:=DATE_ADD(DATE_ADD(@DATE_B,INTERVAL 1 MONTH),INTERVAL -1 DAY);SELECT @DATE_B,@DATE_E

 

MYSQL基礎03(日期函數)