MYSQL基礎03(日期函數)
工作中對日期的處理是經常遇到的,需求可能多種多樣,因此重點介紹。
1.獲取當前日期
select NOW()
-- 結果:2015-10-28 22:41:11
select NOW(),SLEEP(3),NOW()
-- 結果 2015-10-28 22:43:34 0 2015-10-28 22:43:34
SELECT 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-28
SELECT DATE_FORMAT(NOW(),'%y-%m-%d') -- 15-10-28
SELECT 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') -- 結果 736264
SELECT 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.Bkjia.com/database/201202/118061.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
