日期和時間函數
日期和時間函數主要用來處理日期和時間值,一般的日期函數除了使用date類型的參數外,也使用datetime或者timestamp類型的參數,但會忽略這些值的時間部分。相同的,以time類型值為參數的函數,可以接受timestamp類型的參數,但會忽略日期部分,許多日期函數可以同時接受數和字元串類型兩種參數。
1.擷取目前日期的函數和擷取目前時間的函數
curdate()和current_date()函數作用相同,将目前日期按照'YYYY-MM-DD'或'YYYYMMDD'格式的值傳回,具體格式根據函數在字元串或數字語境而定。
案例一:使用日期函數擷取系統目前日期,SQL語句如下:
SELECT CURDATE(), CURRENT_DATE(), CURDATE() + 0;
案例二:使用時間函數擷取系統目前時間,SQL語句如下:
SELECT CURTIME(), CURRENT_TIME(), CURTIME() + 0;
2.擷取目前日期和時間函數
current_timestamp(),localtime(),now(),sysdate()4個函數的作用相同,均傳回目前日期和時間值,格式為'YYYY-MM-DD HH:MM:SS'或'YYYYMMDDHHMMSS',具體格式根據函數在字元串或數字語境中而定。
案例:使用日期時間函數擷取目前系統日期和時間,SQL語句如下:
SELECT CURRENT_TIMESTAMP(), LOCALTIME(), NOW(), SYSDATE();
3.unix時間戳函數
a.unix_timestamp(date)函數,若無參數調用,則傳回一個unix時間戳('1970-01-01 00:00:00'GMT之後的秒數)作為無符号整數。其中,GMT(Greenwich mean time)為格林尼治标準時間。若date來調用unix_timestamp(),它會将參數以'1970-01-01 00:00:00' GMT後的秒數的形式傳回。date可以是一個date字元串、datetime字元串、timestamp或一個當地時間的YYMMDD或YYYYMMDD格式的數字。
案例:使用unix_timestamp函數傳回unix格式的時間戳,SQL語句如下:
SELECT UNIX_TIMESTAMP(), UNIX_TIMESTAMP(NOW()), NOW();
b.from_unixtime(date)函數把unix時間戳轉換為普通格式的時間,與unix_timestamp(date)函數互為反函數。
案例:使用from_unixtime函數将unix時間戳轉換為普通格式的時間,SQL語句如下:
SELECT FROM_UNIXTIME('1466393937');
4.傳回utc日期的函數和傳回utc時間的函數
a.utc_date()函數傳回目前utc(世界标準時間)日期值,其格式為'YYYY-MM-DD'或'YYYYMMDD',具體格式取決于函數是否在用字元串或數字語境中。
案例:使用utc_date()函數傳回目前utc日期值,SQL語句如下:
SELECT UTC_DATE(), UTC_DATE() + 0;
(注:utc_date()函數傳回值為目前時區的日期值。)
b.utc_time()函數傳回目前utc時間值,其格式為'HH:MM:SS'或'HHMMSS',具體格式取決于函數是否用在字元串或數字語境中。
案例:使用utc_time()函數傳回目前utc時間值,SQL語句如下:
SELECT UTC_TIME(), UTC_TIME() + 0;
(注:utc_time()函數傳回值為目前時區的時間值。)
5.擷取月份的函數month(date)和monthname(date)
a.month(date)函數傳回date對應的月份,範圍從1~12。
案例:使用month()函數傳回指定日期中的月份,SQL語句如下:
SELECT MONTH('2016-02-13');
b.monthname(date)函數傳回日期date對應月份的英文全名。
案例:使用monthname()函數傳回指定日期中的月份的名稱,SQL語句如下:
SELECT MONTHNAME('2016-02-13');
6.擷取星期的函數dayname(d)、dayofweek(d)和weekday(d)
a.dayname(d)函數傳回d對應的工作日的英文名稱,例如Sunday、Monday等。
案例:使用dayname()函數傳回指定日期的工作日名稱,SQL語句如下:
SELECT DAYNAME('2016-02-13');
b.dayofweek(d)函數傳回d對應的一周中的索引(位置)。1表示周日,2表示周一,...,7表示周六。
案例:使用dayofweek()函數傳回日期對應的周索引,SQL語句如下:
SELECT DAYOFWEEK('2016-02-13');
c.weekday(d)傳回d對應的工作日索引。0表示周一,1表示周二,...,6表示周日。
案例:使用weekday()函數傳回日期對應的工作日索引,SQL語句如下:
SELECT WEEKDAY('2016-02-14 22:23:00'), WEEKDAY('2016-04-01');
(注:可以看到weekday()和dayofweek()函數都是傳回指定日期在某一周内的位置,隻是索引編号不同。)
7.擷取星期數的函數week(d)和weekofyear(d)
a.week(d)計算日期d是一年中的第幾周。week()的雙參數形式允許指定該星期是否起始于周日或周一,以及傳回值的範圍是否從0~53或從1~53.若mode參數被省略,則使用defaut_week_format系統自變量的值,可參考下表:
Mode | 一周的第一天 | 範圍 | week1為第一周... |
周日 | 0~53 | 本年度中有一個周日 | |
1 | 周一 | 0~53 | 本年度中有3天以上 |
2 | 周日 | 1~53 | 本年度中有一個周日 |
3 | 周一 | 1~53 | 本年度中有3天以上 |
4 | 周日 | 0~53 | 本年度中有3天以上 |
5 | 周一 | 0~53 | 本年度中有一個周一 |
6 | 周日 | 1~53 | 本年度中有3天以上 |
7 | 周一 | 1~53 | 本年度中有一個周一 |
案例:使用week()函數查詢指定日期是一年中的第幾周,SQL語句如下:
SELECT WEEK('2011-02-20'), WEEK('2011-02-20', 0), WEEK('2011-02-20', 1);
b.weekofyear(d)計算某天位于一年中第幾周,範圍是從1~53。相當于week(d,3)。
案例:使用weekofyear()查詢指定日期是一年中第幾周,SQL語句如下:
SELECT WEEK('2011-02-20'), WEEKOFYEAR('2011-02-20');
8.擷取天數的函數dayofyear(d)和dayofmonth(d)
a.dayofyear(d)函數傳回d是一年中的第幾天,範圍是從1~365。
案例:使用dayofyear()函數傳回指定日期在一年中的位置,SQL語句如下:
SELECT DAYOFYEAR('2016-02-20');
b.dayofmonth(d)函數傳回d是一個月中的第幾天,範圍是從1~31。
案例:使用dayofmonth()函數傳回指定日期在一個月中的位置,SQL語句如下:
SELECT DAYOFMONTH('2016-02-20');
9.擷取年份、季度、小時、分鐘和秒鐘的函數
a.year(date)傳回date對應的年份,範圍是從1970~2069。
案例:使用year()函數傳回指定日期對應的年份,SQL語句如下:
SELECT YEAR('11-02-03'), YEAR('96-02-03');
b.quarter(date)傳回date對應的一年中的季度,範圍是從1~4。
案例:使用quarter()函數傳回指定日期對應的季度,SQL語句如下:
SELECT QUARTER('16-04-01');
c.minute(time)傳回time對應的分鐘數,範圍是從0~59。
案例:使用minute()函數傳回指定時間的分鐘值,SQL語句如下:
SELECT MINUTE('16-02-03 10:10:03');
d.second(time)傳回time對應的秒數,範圍是從0~59。
案例:使用second()函數傳回指定時間的秒值,SQL語句如下:
SELECT SECOND('10:05:03');
10.擷取日期的指定值的函數extract(type from date)
extract(type from date)函數所使用的時間間隔類型說明符同date_add()或date_sub()的相同,但它從日期中提取一部分,而不是執行日期運算。
案例:使用extract函數提取日期或者時間值,SQL語句如下:
SELECT EXTRACT(YEAR FROM '2016-07-02') AS col11, EXTRACT(YEAR_MONTH FROM '2016-07-12 01:02:03') AS col2, EXTRACT(DAY_MINUTE FROM '2016-07-12 01:02:03') AS col3;
11.時間和秒鐘轉換的函數
a.time_to_sec(time)傳回已轉化為秒的time參數。轉換公式為:小時×3600+分鐘*60+秒。
案例:使用time_to_sec()函數将時間值轉換為秒值,SQL語句如下:
SELECT TIME_TO_SET('23:23:00');
b.sec_to_time(seconds)傳回被轉化為小時、分鐘和秒數的seconds參數值,其格式為'HH:MM:SS'或'HHMMSS',具體格式根據該函數是否用在字元串或數字語境而定。
案例:使用sec_to_time()函數将秒值轉換為時間格式,SQL語句如下:
SELECT SEC_TO_TIME(2345), SEC_TO_TIME(2345) + 0, TIME_TO_SEC('23:23:00'), SEC_TO_TIME(84180);
(注:可以看到,set_to_time()函數加上0值後變成了小數值;time_to_sec正好和set_to_time互為反函數。)
12.計算日期和時間的函數
計算日期和時間的函數有date_add()、adddate()、date_sub()、subdate()、addtime()、subtime()和date_diff()。
a.date_add(date,interval expr type)和date_sub(date,interval expr type),其中,date是一個datetime或date值,用來指定起始時間。expr是一個表達式,用來指定從起始日期添加或減去的時間間隔。expr是一個字元串;對于負值的時間間隔,它可以以一個符号'-'開頭。type為關鍵字,它訓示了表達式被解釋的方式。下表顯示了type和expr參數的關系:
type值 | 預期的expr格式 |
MICROSECOND | MICROSECONDS |
SECOND | SECONDS |
MINUTE | MINUTES |
HOUR | HOURS |
DAY | DAYS |
WEEK | WEEKS |
MONTH | MONTHS |
QUARTER | QUARTERS |
YEAR | YEARS |
SECOND_MICROSECOND | 'SECONDS.MICROSECONDS' |
MINUTE_MICROSECOND | 'MINUTES.MICROSECONDS' |
MINUTE_SECOND | 'MINUTES.SECONDS' |
HOUR_MICROSECOND | 'HOURS.MICROSECONDS' |
HOUR_SECOND | 'HOURS:MINUTES:SECONDS' |
HOUR_MINUTE | 'HOURS:MINUTES' |
DAY_MICROSECOND | 'DAYS.MICROSECONDS' |
DAY_SECOND | 'DAYS HOURS:MINUTES:SECONDS' |
DAY_MINUTE | 'DAYS HOURS:MINUTES' |
DAY_HOUR | 'DAYS HOURS' |
YEAR_MONTH | 'YEARS-MONTHS' |
若date參數是一個date值,計算隻會包括type、month和day部分(即沒有時間部分),其結果是一個date值,否則,結果将是一個datetime值。
b.date_add(date,interval expr type)和adddate(date,interval expr type)兩個函數作用相同,執行日期的加運算。
案例:使用date_add()和adddate()函數執行日期加運算,SQL語句如下:
SELECT DATE_ADD('2010-12-31 23:59:59', INTERVAL 1 SECOND) AS col1, ADDDATE('2010-12-31 23:59:59', INTERVAL 1 SECOND) AS col2, DATE_ADD('2010-12-31 23:59:59', INTERVAL '1:1' MINUTE_SECOND) AS col3;
c.date_sub(date,interval expr type)和subdate(date,interval expr type)兩個函數作用相同,執行日期的減運算。
案例:使用date_sub()和subdate()函數執行日期減運算,SQL語句如下:
SELECT DATE_SUB('2011-01-02', INTERVAL 31 DAY) AS col1, SUBDATE('2011-01-02', INTERVAL 31 DAY) AS col2, DATE_SUB('2011-01-01 00:01:00', INTERVAL '0 0:1:1' DAY_SECOND) AS col3;
(注:date_add和date_sub在指定修改的時間段時,也可以指定負值,負值代表相減,即傳回以前的日期和時間。)
d.addtime(date,expr)函數将expr值添加到date,并傳回修改後的值,date是一個日期或者日期時間表達式,而expr是一個時間表達式。
案例:使用addtime()函數進行時間加操作,SQL語句如下:
SELECT ADDTIME('2000-12-31 23:59:59', '1:1:1'); ADDTIME('02:02:02', '02:00:00');
e.subtime(date,expr)函數中date減去expr值,并傳回修改後的值,date是一個日期或者日期時間表達式,而expr是一個時間表達式。
案例:使用subtime()函數執行減操作,SQL語句如下:
SELECT SUBTIME('2000-12-31 23:59:59', '1:1:1'),SUBTIME('02:02:02', '02:00:00');
f.datediff(date1,date2)傳回起始時間date1和結束時間date2之間的天數。date1和date2為日期或date-and-time表達式。計算中隻用到這些值的日期部分。
案例:使用datediff()函數計算兩個日期之間的間隔天數,SQL語句如下:
SELECT DATEDIFF('2010-12-31 23:59:59', '2010-12-30') AS col1, DATEDIFF('2010-11-30 23:59:59', '2010-12-31') AS col2;
13.将日期和時間格式化的函數
a.date_form(date,format)根據format指定的格式顯示date值。主要format格式如下表所示:
說明符 | 說明 |
%a | 工作日的縮寫名稱(Sun...Sat) |
%b | 月份的縮寫名稱(Jan...Dec) |
%c | 月份,數字形式(0...12) |
%D | 帶有英語字尾的該月日期(0th,1st,2nd,3rd,...) |
%d | 該月日期,數字形式(00...31) |
%e | 該月日期,數字形式(0...31) |
%f | 微妙(000000...999999) |
%H | 以2位數表示24小時(00...23) |
%h,%I | 以2位數表示12小時(01...12) |
%i | 分鐘,數字形式(00...59) |
%j | 一年中的天數(001...366) |
%k | 以24(00...23)小時表示時間 |
%l | 以12(1...12)小時表示時間 |
%M | 月份名稱(January...December) |
%m | 月份,數字形式(00...12) |
%p | 上午(AM)或下午(PM) |
%r | 時間,12小時制(小時 hh:分鐘 mm:秒數 ss 後加 AM 或 PM) |
%S,%s | 以2位數形式表示秒(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_form()函數格式化輸出日期和時間值,SQL語句如下:
SELECT DATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y') AS col1, DATE_FORMAT('1997-10-04 22:23:00', '%D %y %a %d %m %b %j') AS col2;
b.time_format(time,format)根據format字元串安排time值的格式,format字元串可能僅會處理包含小時、分鐘和秒鐘的格式說明符,其他說明符産生一個null值或0,若time值包含一個大于23的小時部分,則%H和%k小時格式說明符會産生一個大于0.23的通常範圍的值。
案例:使用time_format()函數格式化輸入時間值,SQL語句如下:
SELECT TIME_FORMAT('16:00:00', '%H %k %h %I %l');
c.get_format(val_type,format_type)傳回日期時間字元串的顯示格式,val_type表示日期資料類型,包括date、datetime和time;format_type表示格式化顯示類型,包括eur、interval、iso、jis、usa。get_format根據兩個值類型組合傳回的字元串顯示格式如下表所示:
值類型 | 格式化類型 | 顯示格式字元串 |
DATE | EUR | %d.%m.%Y |
DATE | INTERVAL | %Y%m%d |
DATE | ISO | %Y-%m-%d |
DATE | JIS | %Y-%m-%d |
DATE | USA | %m.%d.%Y |
TIME | EUR | %H.%i.%s |
TIME | INTERVAL | %H%i%s |
TIME | ISO | %H:%i:%s |
TIME | JIS | %H:%i:%s |
TIME | USA | %h:%i:%s %p |
DATETIME | EUR | %Y-%m-%d %H.%i.%s |
DATETIME | INTERVAL | %Y%m%d%H%i%s |
DATETIME | ISO | %Y-%m-%d %H:%i:%s |
DATETIME | JIS | %Y-%m-%d %H:%i:%s |
DATETIME | USA | %Y-%m-%d %H.%i.%s |
案例一:使用get_format()函數顯示不同格式化類型下的格式字元串,SQL語句如下:
SELECT GET_FORMAT(DATE, 'EUR'), GET_FORMAT(DATE, 'USA');
案例二:在date_format()函數中,使用get_format()函數傳回顯示格式字元串來顯示指定的日期值,SQL語句如下:
SELECT DATE_FORMAT('2000-10-05 22:23:00', GET_FORMAT(DATE, 'USA'));