2009年11月11日

Mysql的日期加減運算

YOGO在知識+看到一篇與酷學園相同的問題,分別是 「[php]如何於資料表中到期日前寄出一封信至用戶信箱?」「[求助]如何於資料表中的到期日到時寄出一封信至該用戶信箱?」 ,其中有不少朋友其實已經提出解決之道,只是沒有加以實作而已,有鑑於此YOGO就野人獻曝一下,將之實作出來。

在原始問題中,提問者的原始問題如下:

目前我有做一個客戶資料管理程式,而在這支程式的資料表中我有規劃了用戶的信箱(email)租書到期日(booklimdate)與租片到期日(videolimdate),我想要讓租書到期日與租片到期日前30/15/7/3/1天寄出一封到期通知信給用戶的信箱,【內容比如說:敬愛的顧客xx您好!您向本店租賃的書籍將於$booklimdate到期,敬請注意還書日期,謝謝!! 】請問我該怎麼做呢?

要解決這個問題目前YOGO想的到的方法有2個,一個是用PHP中的date()函式處理,而另一個則是用Mysql的DATE_ADD() 函式來處理,而YOGO的這篇教學是用下Mysql中DATE_ADD()的這個函式來實作,若要用PHP來解決的話,請參考「PHP的日期加減運算」這篇文章,在實作之前我們先來認識一下Mysql中DATE_ADD() 的這個函式,其函式原型如下:

DATE_ADD(date,INTERVAL expr type)

date是一個指定開始日期的DATETIME或DATE值,expr是指定加到開始日期或從開始日期減去的間隔值一個表達式,expr是一個字符串﹔它可以以一個"-"開始表示負間隔。type是一個關鍵詞,指明表達式應該如何被解釋。EXTRACT(type FROM date)函數從日期中返回"type"間隔。下表顯示了type和expr參數怎樣被關聯:

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"

MySQL在expr格式中允許任何標點分隔符。表示顯示的是建議的分隔符。如果date參數是一個DATE值並且你的計算僅僅包含YEAR、MONTH和DAY部分(即,沒有時間部分),結果是一個DATE值。否則結果是一個DATETIME值。

例:

SELECT DATE_ADD("1997-12-31 23:59:59",INTERVAL 1 DAY);

得到的結果如下

1998-01-01 23:59:59

若覺得YOGO這篇文章寫的還不錯的話,就麻煩點一下下方的廣告算是對YOGO一點的小鼓勵吧!!

講解完Mysql的DATE_ADD()函式後,在來就是實作的部份了,首先建立一個名為test的資料表,而這個資料表共有五個欄位,其設定的部份如下:

欄位 型態 屬性 Null 附加 欄位說明
id int UNSIGNED auto_increment 主鍵
name varchar(10)     租借人姓名
e_mail varchar(100)     租借人mail
st_date date     租借時間
ed_date date     到期日

date_01.jpg

再來分別新增5筆與今天日期(2009-11-11)相隔1天、3天、7天、15天、30天到期日的資料,如下圖所示:

date_02.jpg

開啟phpmyadmin中的Query window並輸入如下語法:

SELECT * FROM `test` WHERE `ed_date` = DATE_ADD( CURDATE( ) , INTERVAL 1 DAY )

date_03.jpg

可以看到結果如下:

date_04.jpg

可以看到已查詢到隔天(1天)到期的租借人資料,再來換到期前3天的的租借人資料,一樣是開啟phpmyadmin中的Query window並輸入如下語法:

SELECT * FROM `test` WHERE `ed_date` = DATE_ADD( CURDATE( ) , INTERVAL 3 DAY )

date_05.jpg

可以看到結果如下:

date_06.jpg

可以看到已查詢到3天後到期的租借人資料,再來換到期前7天的的租借人資料,一樣是開啟phpmyadmin中的Query window並輸入如下語法:

SELECT * FROM `test` WHERE `ed_date` = DATE_ADD( CURDATE( ) , INTERVAL 7 DAY )

date_07.jpg

可以看到結果如下:

date_08.jpg

可以看到已查詢到7天後到期的租借人資料,再來換到期前15天的的租借人資料,一樣是開啟phpmyadmin中的Query window並輸入如下語法:

SELECT * FROM `test` WHERE `ed_date` = DATE_ADD( CURDATE( ) , INTERVAL 15 DAY )

date_09.jpg

可以看到結果如下:

date_10.jpg

可以看到已查詢到15天後到期的租借人資料,再來換到期前30天的的租借人資料,一樣是開啟phpmyadmin中的Query window並輸入如下語法:

SELECT * FROM `test` WHERE `ed_date` = DATE_ADD( CURDATE( ) , INTERVAL 30 DAY )

date_11.jpg

可以看到結果如下:

date_12.jpg

可以看到已查詢到30天後到期的租借人資料,且連跨月的部份Mysql都已經幫你算好了,所以不用擔心跨月、閏月之類的問題,因為Mysql中DATE_ADD()函式都會幫你處理。

聖誕跑趴歡樂降★韓版假兩件針織連衣裙$890,新款顯瘦棒球服連帽風衣外套下殺$510,冬新款歐美棉襖棉服連帽短款外套限量↘$530,秋冬新款韓版大碼毛領氣質毛料外套↘$680,快上左耳貓網路購物

您好!如圖太小看不清楚,請 點選此處 看詳細內容 左耳貓蝦皮賣場 https://shopee.tw/hrf5168 左耳貓 露天賣場 http://class.ruten.com.tw/user/index00.php?s=starbox 左耳貓粉...