³¯Â¥¿Í ½Ã°£ ÇÔ¼ö
DATE °ªÀ» ±â´ëÇÏ´Â ÇÔ¼öµéÀº ÀϹÝÀûÀ¸·Î DATETIME °ªÀ» ¼ö¿ëÇϰí, TIME ºÎºÐÀº ¹«½ÃÇÑ´Ù. TIME °ªÀ» ±â´ëÇÏ´Â ÇÔ¼öµéÀº ÀϹÝÀûÀ¸·Î DATETIME °ªÀ» ¼ö¿ëÇϰí, DATE ºÎºÐÀº ¹«½ÃÇÑ´Ù.
ÇöÀç ³¯Â¥³ª ½Ã°£À» ¹Ýȯ(return)ÇÏ´Â ÇÔ¼öµéÀº Äõ¸®°¡ ½ÇÇàµÉ ¶§ ´Ü Çѹø¸¸ ±× °ªÀ» ±¸ÇÑ´Ù. À̰ÍÀº ÇÑ Äõ¸® ¾È¿¡ NOW()¿Í °°Àº ÇÔ¼ö°¡ ¿©·¯¹ø »ç¿ëµÇ¾úÀ» °æ¿ì¿¡µµ ¸ðµÎ °°Àº °á°ú°ªÀ» ÂüÁ¶ÇÑ´Ù´Â °ÍÀ» ÀǹÌÇÑ´Ù. ÀÌ ¿øÄ¢Àº CURDATE(), CURTIME(), UTC_DATE(), UTC_TIME(), UTC_TIMESTAMP() µîÀÇ ÇÔ¼ö¿¡µµ Àû¿ëµÈ´Ù.
MySQL 4.1.3 ¹öÀüºÎÅÍ Á¦°øµÈ CURRENT_TIMESTAMP(), CURRENT_TIME(), CURRENT_DATE(), FROM_UNIXTIME() ÇÔ¼öµéÀº ¿¬°á »óÅÂÀÇ ÇöÀç ½Ã°£´ë¿¡ ÇØ´çµÇ´Â ¹Ýȯ°ªÀ» °®´Â´Ù. ¶ÇÇÑ UNIX_TIMESTAMP()µµ ±× ÀÎÀÚ(argument)°¡ ÇöÀç ½Ã°£´ë¿¡ ÇØ´çµÇ´Â DATETIME °ªÀ̶ó´Â °ÍÀ» °¡Á¤ÇÑ´Ù.
´ÙÀ½ ÇÔ¼ö ¼³¸íµéÀÇ ¹Ýȯ°ª ¹üÀ§´Â ¿ÏÀüÇÑ ³¯Â¥¸¦ ¿ä±¸ÇÑ´Ù. ³¯Â¥°¡ '0'À̰ųª '2001-11-00'ó·³ ºÒ¿ÏÀüÇÏ´Ù¸é, DATE ºÎºÐÀ» ÃßÃâÇÏ´Â ÇÔ¼ö´Â '0'À» ¹ÝȯÇÒ °ÍÀÌ´Ù. ¿¹¸¦ µé¾î, DAYOFMONTH('2001-11-00')Àº '0'À» ¹ÝȯÇÑ´Ù.
ADDDATE(date,INTERVAL expr type) , ADDDATE(expr,days)
ADDDATE()´Â µÎ¹øÂ° ÀÎÀÚ¿¡¼ INTERVAL°ú ÇÔ²² »ç¿ëµÇ¸é DATE_ADD()ÀÇ º°ÄªÀÌ µÈ´Ù. ¸¶Âù°¡Áö·Î SUBDATE()´Â DATE_SUB()ÀÇ º°ÄªÀÌ´Ù. INTERVAL ÀÎÀÚ¿¡ °üÇØ¼´Â DATE_ADD() ¼³¸íÀ» ÂüÁ¶Ç϶ó.
mysql> SELECT DATE_ADD('1998-01-02', INTERVAL 31 DAY); -> '1998-02-02' mysql> SELECT ADDDATE('1998-01-02', INTERVAL 31 DAY); -> '1998-02-02'
MySQL 4.1.1 ¹öÀüºÎÅÍ µÎ¹øÂ° ¹®¹ýÀÌ Çã¿ëµÇ¾ú´Ù. expr °¡ DATE ¶Ç´Â DATETIME Çü½ÄÀÏ ¶§, days ´Â expr ¿¡ Ãß°¡µÇ´Â ÀϼöÀÌ´Ù.
mysql> SELECT ADDDATE('1998-01-02', 31); -> '1998-02-02'
ADDTIME(expr,expr2)
ADDTIME()´Â expr ¿¡ expr2 ¸¦ ´õÇÏ°í ±× °á°ú¸¦ ¹ÝȯÇÑ´Ù. expr ´Â TIME ¶Ç´Â DATETIME Çü½ÄÀ̰í, expr2 ´Â ½Ã°£ Ç¥ÇöÀÌ´Ù.
mysql> SELECT ADDTIME('1997-12-31 23:59:59.999999', '1 1:1:1.000002'); -> '1998-01-02 01:01:01.000001' mysql> SELECT ADDTIME('01:00:00.999999', '02:00:00.999998'); -> '03:00:01.999997'
ADDTIME()´Â MySQL 4.1.1 ¹öÀü¿¡¼ Ãß°¡µÇ¾ú´Ù.
CONVERT_TZ(dt,from_tz,to_tz)
CONVERT_TZ()´Â DATETIME °ª dt ¸¦ from_tz ½Ã°£´ë¿¡¼ to_tz ½Ã°£´ë·Î º¯È¯Çϰí, °á°ú°ªÀ» ¹ÝȯÇÑ´Ù. ÀÌ ÇÔ¼ö´Â ÀÎÀÚ°¡ À¯È¿ÇÏÁö ¾ÊÀ¸¸é NULL °ªÀ» ¹ÝȯÇÑ´Ù.
from_tz ¿¡¼ UTCÀ¸·Î º¯È¯µÉ ¶§ ÀԷ°ªÀÌ TIMESTAMP ÇüÀÇ ¹üÀ§¸¦ ¹þ¾î³ª¸é º¯È¯Àº ÀϾÁö ¾Ê´Â´Ù.
mysql> SELECT CONVERT_TZ('2004-01-01 12:00:00','GMT','MET'); -> '2004-01-01 13:00:00' mysql> SELECT CONVERT_TZ('2004-01-01 12:00:00','+00:00','-07:00'); -> '2004-01-01 05:00:00'
'MET'À̳ª 'Europe/Moscow'¿Í °°Àº ½Ã°£´ë¸¦ »ç¿ëÇϱâ À§Çؼ´Â, ½Ã°£´ë Ç¥°¡ ÀûÀýÇÏ°Ô ¼³Á¤µÇ¾î¾ß ÇÑ´Ù.
CONVERT_TZ()´Â MySQL 4.1.3 ¹öÀü¿¡¼ Ãß°¡µÇ¾ú´Ù.
CURDATE()
ÇÔ¼ö°¡ ¹®ÀÚ¿À̳ª ¼ýÀÚ·Î »ç¿ëµÇ¾ú´ÂÁö ¹®¸Æ¿¡ µû¶ó¼ 'YYYY-MM-DD'À̳ª YYYYMMDD Çü½ÄÀ¸·Î ÇöÀç ³¯Â¥¸¦ ¹ÝȯÇÑ´Ù.
mysql> SELECT CURDATE(); -> '1997-12-15' mysql> SELECT CURDATE() + 0; -> 19971215
CURRENT_DATE , CURRENT_DATE()
CURRENT_DATE¿Í CURRENT_DATE()´Â CURDATE()ÀÇ º°ÄªÀÌ´Ù.
CURTIME()
ÇÔ¼ö°¡ ¹®ÀÚ¿À̳ª ¼ýÀÚ·Î »ç¿ëµÇ¾ú´ÂÁö ¹®¸Æ¿¡ µû¶ó¼ 'HH:MM:SS'À̳ª HHMMSS Çü½ÄÀ¸·Î ÇöÀç ½Ã°£À» ¹ÝȯÇÑ´Ù.
mysql> SELECT CURTIME(); -> '23:50:26' mysql> SELECT CURTIME() + 0; -> 235026
CURRENT_TIME , CURRENT_TIME()
CURRENT_TIME¿Í CURRENT_TIME()´Â CURTIME()ÀÇ º°ÄªÀÌ´Ù.
CURRENT_TIMESTAMP , CURRENT_TIMESTAMP()
CURRENT_TIMESTAMP¿Í CURRENT_TIMESTAMP()´Â NOW()ÀÇ º°ÄªÀÌ´Ù.
DATE(expr)
³¯Â¥(date)³ª DATETIME Ç¥Çö expr ¿¡¼ DATE ºÎºÐÀ» ÃßÃâÇÑ´Ù.
mysql> SELECT DATE('2003-12-31 01:02:03'); -> '2003-12-31'
DATE()´Â MySQL 4.1.1 ¹öÀüºÎÅÍ »ç¿ëÀÌ °¡´ÉÇÏ´Ù.
DATEDIFF(expr,expr2)
DATEDIFF()´Â ½ÃÀÛ ³¯Â¥ expr ¿Í ¸¶Áö¸· ³¯Â¥ expr2 »çÀÌÀÇ Àϼö¸¦ ¹ÝȯÇÑ´Ù. expr ¿Í expr2 ´Â ³¯Â¥(date) ¶Ç´Â date-and-time Ç¥ÇöÀÌ´Ù. ¹Ýȯ°ªÀÇ DATE ºÎºÐ¸¸ °è»êµÈ´Ù.
mysql> SELECT DATEDIFF('1997-12-31 23:59:59','1997-12-30'); -> 1 mysql> SELECT DATEDIFF('1997-11-30 23:59:59','1997-12-31'); -> -31
DATEDIFF()´Â MySQL 4.1.1 ¹öÀü¿¡¼ Ãß°¡µÇ¾ú´Ù.
DATE_ADD(date,INTERVAL expr type) , DATE_SUB(date,INTERVAL expr type)
ÀÌ ÇÔ¼öµéÀº ³¯Â¥ °è»êÀ» ¼öÇàÇÑ´Ù. date ´Â ½ÃÀÛ ³¯Â¥¸¦ ÁöÁ¤ÇÏ´Â DATETIME ¶Ç´Â DATE °ªÀÌ´Ù. expr ´Â ½ÃÀÛ ³¯Â¥·ÎºÎÅÍ ´õÇϰųª »« °£°Ý °ªÀ» ÁöÁ¤Çϴ ǥÇöÀÌ´Ù. expr ´Â ¹®ÀÚ¿ÀÌ´Ù. ¸¶À̳ʽº('-')·Î ½ÃÀÛµÉ ¼öµµ ÀÖ´Ù. type ´Â ¾î¶»°Ô ÇØ¼®ÇÒÁö¸¦ ÁöÁ¤Çϴ Ű¿öµåÀÌ´Ù.
INTERVAL Ű¿öµå¿Í 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' |
type °ª DAY_MICROSECOND, HOUR_MICROSECOND, MINUTE_MICROSECOND, SECOND_MICROSECOND, MICROSECOND ´Â MySQL 4.1.1 ¹öÀüºÎÅÍ »ç¿ëÀÌ °¡´ÉÇÏ´Ù. °ª QUARTER°ú WEEK´Â MySQL 5.0.0 ¹öÀüºÎÅÍ »ç¿ëÀÌ °¡´ÉÇÏ´Ù.
MySQLÀº expr Çü½Ä ¾È¿¡¼ ¾î¶² ±¸¹® ±¸È¹ÀÚµµ Çã¿ëÇÑ´Ù. Ç¥¿¡¼ º¼ ¼ö ÀÖ´Â °ÍµéÀº Á¦¾ÈµÈ ±¸È¹ÀÚµéÀÌ´Ù. date ÀÎÀÚ°¡ DATE °ªÀÌ°í ´ÜÁö YEAR, MONTH, DAY ¸¸ °è»êÇϰíÀÚ ÇÑ´Ù¸é(TIME ºÎºÐÀÌ ÇÊ¿ä ¾ø´Ù¸é), °á°ú´Â DATE °ªÀÌ´Ù. ±×·¸Áö ¾Ê´Ù¸é, °á°ú´Â DATETIME °ªÀÌ´Ù.
MySQL 3.23 ¹öÀüºÎÅÍ, INTERVAL expr type ´Â ´Ù¸¥ ºÎºÐÀÌ DATE ¶Ç´Â DATETIME °ªÀ¸·Î Ç¥ÇöµÇ¾î ÀÖ´Ù¸é ¾î´À ÇÑÂÊÀÌ¶óµµ + ¿¬»êÀÚÀÇ »ç¿ëÀ» Çã¿ëÇÑ´Ù. - ¿¬»êÀÚ´Â ¿À¸¥ÂÊ¿¡¸¸ Çã¿ëµÈ´Ù. °£°Ý¿¡¼ DATE ¶Ç´Â DATETIME °ªÀ» »©´Â °ÍÀº ¹«ÀǹÌÇϱ⠶§¹®ÀÌ´Ù. (¾Æ·¡ ¿¹¹®À» ÂüÁ¶Ç϶ó.)
mysql> SELECT '1997-12-31 23:59:59' + INTERVAL 1 SECOND; -> '1998-01-01 00:00:00' mysql> SELECT INTERVAL 1 DAY + '1997-12-31'; -> '1998-01-01' mysql> SELECT '1998-01-01' - INTERVAL 1 SECOND; -> '1997-12-31 23:59:59' mysql> SELECT DATE_ADD('1997-12-31 23:59:59', INTERVAL 1 SECOND); -> '1998-01-01 00:00:00' mysql> SELECT DATE_ADD('1997-12-31 23:59:59', INTERVAL 1 DAY); -> '1998-01-01 23:59:59' mysql> SELECT DATE_ADD('1997-12-31 23:59:59', INTERVAL '1:1' MINUTE_SECOND); -> '1998-01-01 00:01:00' mysql> SELECT DATE_SUB('1998-01-01 00:00:00', INTERVAL '1 1:1:1' DAY_SECOND); -> '1997-12-30 22:58:59' mysql> SELECT DATE_ADD('1998-01-01 00:00:00', INTERVAL '-1 10' DAY_HOUR); -> '1997-12-30 14:00:00' mysql> SELECT DATE_SUB('1998-01-02', INTERVAL 31 DAY); -> '1997-12-02' mysql> SELECT DATE_ADD('1992-12-31 23:59:59.000002', INTERVAL '1.999999' SECOND_MICROSECOND); -> '1993-01-01 00:00:01.000001'
ÁöÁ¤ÇÑ °£°Ý(interval) °ªÀÌ ³Ê¹« ª´Ù¸é(type Ű¿öµå·ÎºÎÅÍ ±â´ëµÇ´Â ¸ðµç °£°Ý ºÎºÐÀÌ Æ÷ÇԵǾî ÀÖÁö ¾Ê´Ù¸é), MySQLÀº °£°Ý °ªÀÇ ¿ÞÂÊ ºÎºÐÀ» ³²°å´Ù°í °¡Á¤ÇÑ´Ù. ¿¹¸¦ µé¾î, type DAY_SECOND¸¦ ÁöÁ¤Çß´Ù¸é, expr °ªÀº ÀÏ, ½Ã, ºÐ, ÃÊ ºÎºÐÀÌ ±â´ëµÈ´Ù. '1:10'°ú °°Àº °ªÀ» ÁöÁ¤Çß´Ù¸é, MySQLÀº ÀÏ, ½Ã ºÎºÐÀÌ ¾ø´Â ºÐ, ÃÊ °ªÀ̶ó°í °¡Á¤ÇÑ´Ù. ´Ù¸£°Ô ¸»Çϸé, '1:10' DAY_SECOND´Â '1:10' MINUTE_SECOND°ú µ¿ÀÏÇÑ °ªÀ¸·Î ÇØ¼®µÈ´Ù. À̰ÍÀº MySQL°¡ TIME °ªÀ» ½Ã°¢º¸´Ù ½Ã°£À¸·Î ÇØ¼®ÇÏ´Â °Í°ú ºñ½ÁÇÏ´Ù.
TIME ºÎºÐÀ» Æ÷ÇÔÇÏ´Â ¾î¶² °ª¿¡¼ date ¸¦ ´õÇϰųª »«´Ù¸é, °á°ú´Â ÀÚµ¿À¸·Î DATETIME °ªÀ¸·Î º¯È¯µÈ´Ù.
mysql> SELECT DATE_ADD('1999-01-01', INTERVAL 1 DAY); -> '1999-01-02' mysql> SELECT DATE_ADD('1999-01-01', INTERVAL 1 HOUR); -> '1999-01-01 01:00:00'
±âÇüÀûÀÎ ³¯Â¥¸¦ ÀÔ·ÂÇÏ¸é °á°ú´Â NULLÀÌ µÈ´Ù. ¸¸ÀÏ MONTH, YEAR_MONTH, ¶Ç´Â YEAR¸¦ ´õÇØ¼ »õ·Î¿î ´ÞÀÇ Àϼöº¸´Ù ´õ Å« ³¯Â¥°¡ µÈ´Ù¸é, ³¯Â¥´Â »õ·Î¿î ´ÞÀÇ ¸¶Áö¸· ³¯·Î º¸Á¤µÈ´Ù.
mysql> SELECT DATE_ADD('1998-01-30', INTERVAL 1 MONTH); -> '1998-02-28'
DATE_FORMAT(date,format)
format ¹®ÀÚ¿¿¡ µû¶ó date °ªÀ» Çü½ÄÈÇÑ´Ù. format ¹®ÀÚ¿¿¡´Â ´ÙÀ½ ÁöÁ¤ÀÚµéÀÌ »ç¿ëµÈ´Ù.
ÁöÁ¤ÀÚ | ¼³¸í |
---|---|
%a | Abbreviated weekday name (Sun..Sat) |
%b | Abbreviated month name (Jan..Dec) |
%c | Month, numeric (0..12) |
%D | Day of the month with English suffix (0th, 1st, 2nd, 3rd, ...) |
%d | Day of the month, numeric (00..31) |
%e | Day of the month, numeric (0..31) |
%f | Microseconds (000000..999999) |
%H | Hour (00..23) |
%h | Hour (01..12) |
%I | Hour (01..12) |
%i | Minutes, numeric (00..59) |
%j | Day of year (001..366) |
%k | Hour (0..23) |
%l | Hour (1..12) |
%M | Month name (January..December) |
%m | Month, numeric (00..12) |
%p | AM or PM |
%r | Time, 12-hour (hh:mm:ss followed by AM or PM) |
%S | Seconds (00..59) |
%s | Seconds (00..59) |
%T | Time, 24-hour (hh:mm:ss) |
%U | Week (00..53), where Sunday is the first day of the week |
%u | Week (00..53), where Monday is the first day of the week |
%V | Week (01..53), where Sunday is the first day of the week; used with %X |
%v | Week (01..53), where Monday is the first day of the week; used with %x |
%W | Weekday name (Sunday..Saturday) |
%w | Day of the week (0=Sunday..6=Saturday) |
%X | Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V |
%x | Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v |
%Y | Year, numeric, four digits |
%y | Year, numeric, two digits |
%% | A literal '%'. |
´Ù¸¥ ¸ðµç ¹®ÀÚµéÀº º¯È¯¾øÀÌ °á°ú¿¡ ±×´ë·Î º¹»çµÈ´Ù.
%v, %V, %x, %X format ÁöÁ¤ÀÚµéÀº MySQL 3.23.8 ¹öÀüºÎÅÍ »ç¿ëÀÌ °¡´ÉÇϰí, %f´Â MySQL 4.1.1 ¹öÀüºÎÅÍ °¡´ÉÇÏ´Ù.
MySQL 3.23 ¹öÀüºÎÅÍ´Â '%' ¹®ÀÚ°¡ format ÁöÁ¤¹®ÀÚ ¾Õ¿¡ ¿ä±¸µÈ´Ù. ±× ÀÌÀü ¹ö¹ø¿¡¼ '%'´Â ¼±Åûç¾çÀÌ´Ù.
¿ùÀÏÀÇ ¹üÀ§°¡ '0'À¸·Î ½ÃÀ۵DZ⠶§¹®¿¡ MySQL 3.23 ¹öÀüºÎÅÍ´Â '2004-00-00'¿Í °°Àº ºÒ¿ÏÀüÇÑ ³¯Â¥°¡ Çã¿ëµÈ´Ù.
mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y'); -> 'Saturday October 1997' mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00', '%H:%i:%s'); -> '22:23:00' mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00', '%D %y %a %d %m %b %j'); -> '4th 97 Sat 04 10 Oct 277' mysql> 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' mysql> SELECT DATE_FORMAT('1999-01-01', '%X %V'); -> '1998 52'
DAY(date)
DAY()´Â DAYOFMONTH()ÀÇ º°ÄªÀÌ´Ù. MySQL 4.1.1 ¹öÀüºÎÅÍ »ç¿ëÀÌ °¡´ÉÇÏ´Ù.
DAYNAME(date)
date ¿¡ ´ëÇÑ ¿äÀÏ À̸§À» ¹ÝȯÇÑ´Ù.
mysql> SELECT DAYNAME('1998-02-05'); -> 'Thursday'
DAYOFMONTH(date)
date ¿¡ ´ëÇÑ ´ç¿ùÀÇ ³¯Â¥¸¦ ¹ÝȯÇÑ´Ù. (¹üÀ§ 1~31)
mysql> SELECT DAYOFMONTH('1998-02-03'); -> 3
DAYOFWEEK(date)
date ¿¡ ´ëÇÏ¿© ¿äÀÏ »öÀÎ(1 = ÀÏ¿äÀÏ, 2 = ¿ù¿äÀÏ, ..., 7 = Åä¿äÀÏ)À» ¹ÝȯÇÑ´Ù. ÀÌ »öÀવéÀº ODBC Ç¥ÁØ¿¡ µû¸¥´Ù.
mysql> SELECT DAYOFWEEK('1998-02-03'); -> 3
DAYOFYEAR(date)
date °¡ ÇØ´ç ¿¬µµ¿¡ ¸îÀϰÀÎÁö ¹ÝȯÇÑ´Ù. (¹üÀ§ 1~366)
mysql> SELECT DAYOFYEAR('1998-02-03'); -> 34
EXTRACT(type FROM date)
EXTRACT() ÇÔ¼ö´Â DATE_ADD()³ª DATE_SUB()¿Í °°Àº Á¾·ùÀÇ °£°Ý ÁöÁ¤ÀÚ¸¦ »ç¿ëÇÏÁö¸¸, ³¯Â¥¸¦ °è»êÇÏ´Â °Ô ¾Æ´Ï¶ó ³¯Â¥·ÎºÎÅÍ ºÎºÐÀ» ÃßÃâÇÑ´Ù.
mysql> SELECT EXTRACT(YEAR FROM '1999-07-02'); -> 1999 mysql> SELECT EXTRACT(YEAR_MONTH FROM '1999-07-02 01:02:03'); -> 199907 mysql> SELECT EXTRACT(DAY_MINUTE FROM '1999-07-02 01:02:03'); -> 20102 mysql> SELECT EXTRACT(MICROSECOND FROM '2003-01-02 10:30:00.00123'); -> 123
EXTRACT()´Â MySQL 3.23.0 ¹öÀü¿¡¼ Ãß°¡µÇ¾ú´Ù.
FROM_DAYS(N)
Àϼö N °¡ ÁÖ¾îÁö¸é, DATE °ªÀ» ¹ÝȯÇÑ´Ù.
mysql> SELECT FROM_DAYS(729669); -> '1997-10-07'
FROM_DAYS()´Â ±×·¹°í¸®¾È ´Þ·ÂÀÇ ÃâÇö(1582³â) ÀÌÀüÀÇ °ªÀ» »ç¿ëÇÒ ¼ö ÀÖµµ·Ï °èȹµÇÁö ¾Ê¾Ò´Ù. ´Þ·ÂÀÌ ¹Ù²î¾úÀ» ¶§ ¼Õ½ÇµÈ ³¯Â¥´Â °í·ÁÇÏÁö ¾Ê´Â´Ù.
FROM_UNIXTIME(unix_timestamp) , FROM_UNIXTIME(unix_timestamp,format)
ÇÔ¼ö¿¡ »ç¿ëµÈ ¹®¸ÆÀÌ ¹®ÀÚ¿ÀÎÁö ¼ýÀÚÀÎÁö¿¡ µû¶ó 'YYYY-MM-DD HH:MM:SS' ¶Ç´Â YYYYMMDDHHMMSS format °ªÀ¸·Î unix_timestamp ÀÎÀÚ°¡ Ç¥½ÃµÇ¾î ¹ÝȯµÈ´Ù.
mysql> SELECT FROM_UNIXTIME(875996580); -> '1997-10-04 22:23:00' mysql> SELECT FROM_UNIXTIME(875996580) + 0; -> 19971004222300
format ÀÌ ÁÖ¾îÁø´Ù¸é °á°ú´Â format ¹®ÀÚ¿¿¡ µû¶ó Çü½ÄȵȴÙ. format Àº DATE_FORMAT() ÇÔ¼ö¿¡ ¾²ÀÌ´Â ÁöÁ¤ÀÚ¸¦ ¶È°°ÀÌ »ç¿ëÇÑ´Ù.
mysql> SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(), '%Y %D %M %h:%i:%s %x'); -> '2003 6th August 06:22:58 2003'
GET_FORMAT(DATE|TIME|DATETIME, 'EUR'|'USA'|'JIS'|'ISO'|'INTERNAL')
Çü½Ä ¹®ÀÚ¿À» ¹ÝȯÇÑ´Ù. ÀÌ ÇÔ¼ö´Â DATE_FORMAT()°ú STR_TO_DATE() ÇÔ¼ö¿Í Á¶ÇÕÇÒ ¶§ À¯¿ëÇÏ´Ù.
ù¹øÂ° ÀÎÀڷδ 3°¡Áö °¡´ÉÇÑ °ªÀÌ ÀÖ°í, µÎ¹øÂ° ÀÎÀڷδ 5°¡Áö °¡´ÉÇÑ °ªÀÌ ÀÖ¾î¼ °á°úÀûÀ¸·Î 15°¡Áö Çü½Ä ¹®ÀÚ¿ÀÌ °¡´ÉÇÏ´Ù. (»ç¿ëµÇ´Â ÁöÁ¤ÀÚ¸¦ À§Çؼ DATE_FORMAT() ¼³¸íÀ» ÂüÁ¶Ç϶ó.)
ÇÔ¼ö È£Ãâ | °á°ú |
---|---|
GET_FORMAT(DATE,'USA') | '%m.%d.%Y' |
GET_FORMAT(DATE,'JIS') | '%Y-%m-%d' |
GET_FORMAT(DATE,'ISO') | '%Y-%m-%d' |
GET_FORMAT(DATE,'EUR') | '%d.%m.%Y' |
GET_FORMAT(DATE,'INTERNAL') | '%Y%m%d' |
GET_FORMAT(DATETIME,'USA') | '%Y-%m-%d-%H.%i.%s' |
GET_FORMAT(DATETIME,'JIS') | '%Y-%m-%d %H:%i:%s' |
GET_FORMAT(DATETIME,'ISO') | '%Y-%m-%d %H:%i:%s' |
GET_FORMAT(DATETIME,'EUR') | '%Y-%m-%d-%H.%i.%s' |
GET_FORMAT(DATETIME,'INTERNAL') | '%Y%m%d%H%i%s' |
GET_FORMAT(TIME,'USA') | '%h:%i:%s %p' |
GET_FORMAT(TIME,'JIS') | '%H:%i:%s' |
GET_FORMAT(TIME,'ISO') | '%H:%i:%s' |
GET_FORMAT(TIME,'EUR') | '%H.%i.%S' |
GET_FORMAT(TIME,'INTERNAL') | '%H%i%s' |
ISO Çü½ÄÀº ISO 8601ÀÌ ¾Æ´Ï¶ó ISO 9075ÀÌ´Ù.
MySQL 4.1.4 ¹öÀüºÎÅÍ´Â TIMESTAMP ¶ÇÇÑ »ç¿ëÇÒ ¼ö ÀÖ°Ô µÇ¾ú´Ù. GET_FORMAT() ÇÔ¼ö´Â °°Àº °ªÀ» DATETIME Çü½ÄÀ¸·Î ¹ÝȯÇÑ´Ù.
mysql> SELECT DATE_FORMAT('2003-10-03',GET_FORMAT(DATE,'EUR')); -> '03.10.2003' mysql> SELECT STR_TO_DATE('10.31.2003',GET_FORMAT(DATE,'USA')); -> 2003-10-31
GET_FORMAT()´Â MySQL 4.1.1 ¹öÀüºÎÅÍ »ç¿ëÀÌ °¡´ÉÇÏ´Ù.
HOUR(time)
time ¿¡¼ ½Ã°£À» ¹ÝȯÇÑ´Ù. (¹üÀ§ 0~23)
mysql> SELECT HOUR('10:05:03'); -> 10
±×·¯³ª TIME °ªÀÇ Å©±â´Â ½ÇÁ¦·Î ÈξÀ ´õ Å©´Ù. HOUR´Â 23º¸´Ù ´õ Å« °ªÀ» ¹ÝȯÇÒ ¼ö ÀÖ´Ù.
mysql> SELECT HOUR('272:59:59'); -> 272
LAST_DAY(date)
DATE ¶Ç´Â DATETIME °ªÀ» ÀÔ·ÂÇÏ¸é ´ç¿ùÀÇ ¸¶Áö¸· ³¯¿¡ ´ëÇÑ »óÀÀÇÏ´Â °ªÀ» ¹ÝȯÇÑ´Ù. ÀÎÀÚ°¡ À¯È¿ÇÏÁö ¾ÊÀ¸¸é NULLÀ» ¹ÝȯÇÑ´Ù.
mysql> SELECT LAST_DAY('2003-02-05'); -> '2003-02-28' mysql> SELECT LAST_DAY('2004-02-05'); -> '2004-02-29' mysql> SELECT LAST_DAY('2004-01-01 01:01:01'); -> '2004-01-31' mysql> SELECT LAST_DAY('2003-03-32'); -> NULL
LAST_DAY()´Â MySQL 4.1.1 ¹öÀüºÎÅÍ »ç¿ëÀÌ °¡´ÉÇÏ´Ù.
LOCALTIME , LOCALTIME()
LOCALTIME°ú LOCALTIME()Àº NOW()ÀÇ º°ÄªÀÌ´Ù.
µÎ ÇÔ¼ö´Â MySQL 4.0.6 ¹öÀü¿¡¼ Ãß°¡µÇ¾ú´Ù.
LOCALTIMESTAMP , LOCALTIMESTAMP()
LOCALTIMESTAMP¿Í LOCALTIMESTAMP()´Â NOW()ÀÇ º°ÄªÀÌ´Ù.
µÎ ÇÔ¼ö´Â MySQL 4.0.6 ¹öÀü¿¡¼ Ãß°¡µÇ¾ú´Ù.
MAKEDATE(year,dayofyear)
year ¿Í dayofyear °ªÀÌ ÁÖ¾îÁö¸é ³¯Â¥¸¦ ¹ÝȯÇÑ´Ù. dayofyear ´Â 0º¸´Ù Ä¿¾ß ÇÑ´Ù. ±×·¸Áö ¾ÊÀ¸¸é °á°ú´Â NULLÀÌ´Ù.
mysql> SELECT MAKEDATE(2001,31), MAKEDATE(2001,32); -> '2001-01-31', '2001-02-01' mysql> SELECT MAKEDATE(2001,365), MAKEDATE(2004,365); -> '2001-12-31', '2004-12-30' mysql> SELECT MAKEDATE(2001,0); -> NULL
MAKEDATE()´Â MySQL 4.1.1 ¹öÀüºÎÅÍ »ç¿ëÀÌ °¡´ÉÇÏ´Ù.
MAKETIME(hour,minute,second)
hour,minute,second ÀÎÀڷκÎÅÍ °è»êµÈ ½Ã°£ °ªÀ» ¹ÝȯÇÑ´Ù.
mysql> SELECT MAKETIME(12,15,30); -> '12:15:30'
MAKETIME()´Â MySQL 4.1.1 ¹öÀüºÎÅÍ »ç¿ëÀÌ °¡´ÉÇÏ´Ù.
MICROSECOND(expr)
TIME ¶Ç´Â DATETIME Çü½ÄÀÇ expr ·ÎºÎÅÍ ¸¶ÀÌÅ©·ÎÃʸ¦ ¹ÝȯÇÑ´Ù. (¹üÀ§ 0~999999)
mysql> SELECT MICROSECOND('12:00:00.123456'); -> 123456 mysql> SELECT MICROSECOND('1997-12-31 23:59:59.000010'); -> 10
MICROSECOND()´Â MySQL 4.1.1 ¹öÀüºÎÅÍ »ç¿ëÀÌ °¡´ÉÇÏ´Ù.
MINUTE(time)
time ¿¡ ´ëÇÏ¿© ¸î ºÐÀÎÁö ¹ÝȯÇÑ´Ù. (¹üÀ§ 0~59)
mysql> SELECT MINUTE('98-02-03 10:05:03'); -> 5
MONTH(date)
date ¿¡ ´ëÇÏ¿© ¸î ¿ùÀÎÁö ¹ÝȯÇÑ´Ù. (¹üÀ§ 1~12)
mysql> SELECT MONTH('1998-02-03'); -> 2
MONTHNAME(date)
date ¿¡ ´ëÇÏ¿© ´ç¿ùÀÇ ¿µ¹® À̸§À» ¹ÝȯÇÑ´Ù.
mysql> SELECT MONTHNAME('1998-02-05'); -> 'February'
NOW()
ÇÔ¼ö¿¡ »ç¿ëµÈ ¹®¸Æ¿¡ µû¶ó 'YYYY-MM-DD HH:MM:SS' ¶Ç´Â YYYYMMDDHHMMSS Çü½ÄÀ¸·Î ÇöÀç ³¯Â¥¿Í ½Ã°£À» ¹ÝȯÇÑ´Ù.
mysql> SELECT NOW(); -> '1997-12-15 23:50:26' mysql> SELECT NOW() + 0; -> 19971215235026
PERIOD_ADD(P,N)
±â°£ P ¿¡ N ¿ùÀ» ´õÇÑ´Ù(YYMM ¶Ç´Â YYYYMM Çü½ÄÀ¸·Î). YYYYMM Çü½ÄÀ¸·Î °á°ú¸¦ ¹ÝȯÇÑ´Ù. ±â°£ P °¡ DATE °ªÀÌ ¾Æ´Ï¶ó´Â °Í¿¡ ÁÖÀÇÇ϶ó.
mysql> SELECT PERIOD_ADD(9801,2); -> 199803
PERIOD_DIFF(P1,P2)
±â°£ P1, P2 »çÀÌÀÇ °³¿ù¼ö¸¦ ¹ÝȯÇÑ´Ù. P1 °ú P2 ´Â YYMM ¶Ç´Â YYYYMM Çü½ÄÀ̾î¾ß ÇÑ´Ù. ±â°£ P1, P2 °¡ DATE °ªÀÌ ¾Æ´Ï¶ó´Â °Í¿¡ ÁÖÀÇÇ϶ó.
mysql> SELECT PERIOD_DIFF(9802,199703); -> 11
QUARTER(date)
date °¡ ¸î ºÐ±âÀÎÁö ¹ÝȯÇÑ´Ù. (¹üÀ§ 1~4)
mysql> SELECT QUARTER('98-04-01'); -> 2
SECOND(time)
time ¿¡¼ ÃÊ °ªÀ» ¹ÝȯÇÑ´Ù. (¹üÀ§ 0~59)
mysql> SELECT SECOND('10:05:03'); -> 3
SEC_TO_TIME(seconds)
ÇÔ¼ö°¡ ¾î¶² ¹®¸ÆÀ¸·Î »ç¿ëµÇ¾ú´ÂÁö¿¡ µû¶ó seconds ÀÎÀÚ¸¦ 'HH:MM:SS' ¶Ç´Â HHMMSS Çü½ÄÀ¸·Î º¯È¯½ÃÄѼ ¹ÝȯÇÑ´Ù.
mysql> SELECT SEC_TO_TIME(2378); -> '00:39:38' mysql> SELECT SEC_TO_TIME(2378) + 0; -> 3938
STR_TO_DATE(str,format)
ÀÌ ÇÔ¼ö´Â DATE_FORMAT() ÇÔ¼öÀÇ ¿ª±â´ÉÀÌ´Ù. ¹®ÀÚ¿ str ¿Í Çü½Ä ¹®ÀÚ¿ format À» ÀԷ¹޴´Ù. STR_TO_DATE()´Â Çü½Ä ¹®ÀÚ¿ÀÌ ³¯Â¥¿Í ½Ã°£À» ¸ðµÎ Æ÷ÇÔÇϰí ÀÖ´Ù¸é DATETIME °ªÀ» ¹ÝȯÇÑ´Ù. ±×·¸Áö ¾Ê°í ³¯Â¥³ª ½Ã°£ µÑ Áß ÇÑ ºÎºÐ¸¸À» Æ÷ÇÔÇÑ´Ù¸é DATE ¶Ç´Â TIME °ªÀ» ¹ÝȯÇÑ´Ù.
str ¿¡ Æ÷ÇÔµÈ DATE, TIME ¶Ç´Â DATETIME °ªÀº format ¿¡ ÀÇÇØ ÁöÁ¤µÈ Çü½ÄÀ¸·Î ÁÖ¾îÁ®¾ß ÇÑ´Ù. format ¿¡ »ç¿ëÇÒ ¼ö ÀÖ´Â ÁöÁ¤ÀÚ¿¡ ´ëÇØ¼´Â DATE_FORMAT() ¼³¸íÀ» ÂüÁ¶Ç϶ó. ´Ù¸¥ ¸ðµç ¹®ÀÚ´Â ÇØ¼®µÇÁö ¾Ê°í ±×´ë·Î ¹Ý¿µµÈ´Ù. ¸¸ÀÏ str °¡ À¯È¿ÇÏÁö ¾ÊÀº °ªÀ» Æ÷ÇÔÇÑ´Ù¸é NULLÀÌ ¹ÝȯµÈ´Ù. MySQL 5.0.3 ¹öÀüºÎÅÍ´Â À߸øµÈ °ª ¶ÇÇÑ °æ°í¸¦ ¹ß»ýÇÑ´Ù.
mysql> SELECT STR_TO_DATE('03.10.2003 09.20', '%d.%m.%Y %H.%i'); -> '2003-10-03 09:20:00' mysql> SELECT STR_TO_DATE('10arp', '%carp'); -> '0000-10-00 00:00:00' mysql> SELECT STR_TO_DATE('2003-15-10 00:00:00', '%Y-%m-%d %H:%i:%s'); -> NULL
¾î¶² ´ÞÀÇ Àϼöº¸´Ù Å« Àϼö¸¦ °¡Áø ³¯Â¥´Â 1-31 ¹üÀ§ ¾È¿¡¼ Çã¿ëµÈ´Ù. ¶ÇÇÑ '0'À̳ª '0'°ªÀ» °¡Áø ³¯Â¥µµ Çã¿ëµÈ´Ù.
mysql> SELECT STR_TO_DATE('00/00/0000', '%m/%d/%Y'); -> '0000-00-00' mysql> SELECT STR_TO_DATE('04/31/2004', '%m/%d/%Y'); -> '2004-04-31'
STR_TO_DATE()´Â MySQL 4.1.1 ¹öÀüºÎÅÍ »ç¿ëÀÌ °¡´ÉÇÏ´Ù.
SUBDATE(date,INTERVAL expr type) , SUBDATE(expr,days)
µÎ¹øÂ° ÀÎÀÚ INTERVAL Çü½ÄÀ» Æ÷ÇÔÇÏ¿© »ç¿ëµÇ¾úÀ» ¶§ SUBDATE()´Â DATE_SUB()ÀÇ º°ÄªÀÌ´Ù. INTERVAL ÀÎÀÚ¿¡ ´ëÇÑ Á¤º¸´Â DATE_ADD() ¼³¸íÀ» ÂüÁ¶Ç϶ó.
mysql> SELECT DATE_SUB('1998-01-02', INTERVAL 31 DAY); -> '1997-12-02' mysql> SELECT SUBDATE('1998-01-02', INTERVAL 31 DAY); -> '1997-12-02'
MySQL 4.1.1 ¹öÀüºÎÅÍ µÎ¹øÂ° ¹®¹ýÀÌ Çã¿ëµÈ´Ù. expr ´Â DATE ¶Ç´Â DATETIME Çü½ÄÀ̰í days ´Â expr ¿¡¼ »¬ ÀϼöÀÌ´Ù.
mysql> SELECT SUBDATE('1998-01-02 12:00:00', 31); -> '1997-12-02 12:00:00'
SUBTIME(expr,expr2)
SUBTIME()´Â expr ¿¡¼ expr2 ¸¦ »©°í ±× °ªÀ» ¹ÝȯÇÑ´Ù. expr ´Â TIME ¶Ç´Â DATETIME Çü½ÄÀ̰í, expr2 ´Â TIME Çü½ÄÀÌ´Ù.
mysql> SELECT SUBTIME('1997-12-31 23:59:59.999999', -> '1 1:1:1.000002'); -> '1997-12-30 22:58:58.999997' mysql> SELECT SUBTIME('01:00:00.999999', '02:00:00.999998'); -> '-00:59:59.999999'
SUBTIME()´Â MySQL 4.1.1 ¹öÀü¿¡¼ Ãß°¡µÇ¾ú´Ù.
SYSDATE()
SYSDATE()´Â NOW()ÀÇ º°ÄªÀÌ´Ù.
TIME(expr)
TIME ¶Ç´Â DATETIME Çü½ÄÀÇ expr ¿¡¼ TIME ºÎºÐÀ» ÃßÃâÇÑ´Ù.
mysql> SELECT TIME('2003-12-31 01:02:03'); -> '01:02:03' mysql> SELECT TIME('2003-12-31 01:02:03.000123'); -> '01:02:03.000123'
TIME()´Â MySQL 4.1.1 ¹öÀüºÎÅÍ »ç¿ëÀÌ °¡´ÉÇÏ´Ù.
TIMEDIFF(expr,expr2)
TIMEDIFF()´Â ½ÃÀÛ ½Ã°£ expr ¿Í ¸¶Áö¸· ½Ã°£ expr2 ¿ÍÀÇ Â÷À̸¦ TIME °ªÀ¸·Î ¹ÝȯÇÑ´Ù. expr ¿Í expr2 ´Â TIME ¶Ç´Â DATETIME Çü½ÄÀ̰í, µÎ Çü½ÄÀº °°¾Æ¾ß ÇÑ´Ù.
mysql> SELECT TIMEDIFF('2000:01:01 00:00:00', -> '2000:01:01 00:00:00.000001'); -> '-00:00:00.000001' mysql> SELECT TIMEDIFF('1997-12-31 23:59:59.000001', -> '1997-12-30 01:01:01.000002'); -> '46:58:57.999999'
TIMEDIFF()´Â MySQL 4.1.1 ¹öÀü¿¡¼ Ãß°¡µÇ¾ú´Ù.
TIMESTAMP(expr) , TIMESTAMP(expr,expr2)
ÀÎÀÚ Çϳª¸¸À» »ç¿ëÇÑ´Ù¸é, DATE ¶Ç´Â DATETIME expr ¸¦ DATETIME °ªÀ¸·Î ¹ÝȯÇÑ´Ù. ÀÎÀÚ µÎ °³¸¦ »ç¿ëÇÑ´Ù¸é, DATE ¶Ç´Â DATETIME Çü½ÄÀÇ expr ¿¡ TIME Çü½ÄÀÇ expr2 ¸¦ ´õÇÏ°í ±× DATETIME °ªÀ» ¹ÝȯÇÑ´Ù.
mysql> SELECT TIMESTAMP('2003-12-31'); -> '2003-12-31 00:00:00' mysql> SELECT TIMESTAMP('2003-12-31 12:00:00','12:00:00'); -> '2004-01-01 00:00:00'
TIMESTAMP()´Â MySQL 4.1.1 ¹öÀüºÎÅÍ »ç¿ëÀÌ °¡´ÉÇÏ´Ù.
TIMESTAMPADD(interval,int_expr,DATETIME_expr)
DATE ¶Ç´Â DATETIME Çü½ÄÀÇ DATETIME_expr ¿¡ Á¤¼ö Çü½ÄÀÇ int_expr ¸¦ ´õÇÑ´Ù. int_expr ÀÇ ´ÜÀ§´Â interval ÀÎÀÚ·Î ÁÖ¾îÁö´Âµ¥, ´ÙÀ½ °ª °¡¿îµ¥ ÇϳªÀ̾î¾ß ÇÑ´Ù. FRAC_SECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR.
interval °ªÀº »ó±âµÈ Ű¿öµå °¡¿îµ¥ Çϳª¸¦ ÁöÁ¤Çϰųª SQL_TSI_ Á¢µÎ»ç¸¦ »ç¿ëÇÒ ¼ö ÀÖ´Ù. ¿¹¸¦ µé¾î, DAY ¶Ç´Â SQL_TSI_DAY µÑ ´Ù ¸ðµÎ Çã¿ëµÈ´Ù.
mysql> SELECT TIMESTAMPADD(MINUTE,1,'2003-01-02'); -> '2003-01-02 00:01:00' mysql> SELECT TIMESTAMPADD(WEEK,1,'2003-01-02'); -> '2003-01-09'
TIMESTAMPADD()´Â MySQL 5.0.0 ¹öÀüºÎÅÍ »ç¿ëÀÌ °¡´ÉÇÏ´Ù.
TIMESTAMPDIFF(interval,DATETIME_expr1,DATETIME_expr2)
DATE ¶Ç´Â DATETIME Çü½ÄÀÇ DATETIME_expr1,DATETIME_expr2 »çÀÌÀÇ °ÝÂ÷¸¦ Á¤¼ö°ªÀ¸·Î ¹ÝȯÇÑ´Ù. °á°ú°ªÀÇ ´ÜÀ§´Â interval ÀÎÀÚ¿¡ ÀÇÇØ ÁÖ¾îÁø´Ù. interval ÀÇ Çã¿ë°ªÀº TIMESTAMPADD() ÇÔ¼ö¿Í °°´Ù.
mysql> SELECT TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01'); -> 3 mysql> SELECT TIMESTAMPDIFF(YEAR,'2002-05-01','2001-01-01'); -> -1
TIMESTAMPDIFF()´Â MySQL 5.0.0 ¹öÀüºÎÅÍ »ç¿ëÀÌ °¡´ÉÇÏ´Ù.
TIME_FORMAT(time,format)
ÀÌ ÇÔ¼ö´Â DATE_FORMAT() ÇÔ¼öó·³ »ç¿ëµÇÁö¸¸, format ¹®ÀÚ¿Àº ½Ã, ºÐ, ÃÊ¿¡ °ü·ÃµÈ ÁöÁ¤ÀÚ¸¸À» Æ÷ÇÔÇÒ ¼ö ÀÖ´Ù. ´Ù¸¥ ÁöÁ¤ÀÚµéÀº NULL °ªÀ̳ª '0'À» ¹ß»ýÇÑ´Ù.
time °ªÀÌ TIME ºÎºÐ¿¡¼ 23º¸´Ù Å« °ªÀ» °®´Â´Ù¸é, %H¿Í %k ½Ã°£ ÁöÁ¤ÀÚ´Â ÀÏ»óÀûÀÎ ¹üÀ§ 0-23º¸´Ù ´õ Å« °ªÀ» ¹ß»ýÇÑ´Ù. ´Ù¸¥ ½Ã°£ ÁöÁ¤ÀÚµéÀº 12 ¹ý(modulo)ÀÇ °ªÀ» ¹ß»ýÇÑ´Ù.
mysql> SELECT TIME_FORMAT('100:00:00', '%H %k %h %I %l'); -> '100 100 04 04 4'
TIME_TO_SEC(time)
time ÀÎÀÚ¸¦ ÃÊ·Î º¯È¯ÇÏ¿© ¹ÝȯÇÑ´Ù.
mysql> SELECT TIME_TO_SEC('22:23:00'); -> 80580 mysql> SELECT TIME_TO_SEC('00:39:38'); -> 2378
TO_DAYS(date)
³¯Â¥ date °¡ ÁÖ¾îÁö¸é, Àϼö¸¦ ¹ÝȯÇÑ´Ù. (0³âºÎÅÍÀÇ Àϼö).
mysql> SELECT TO_DAYS(950501); -> 728779 mysql> SELECT TO_DAYS('1997-10-07'); -> 729669
TO_DAYS()´Â ±×·¹°í¸®¾È ´Þ·ÂÀÇ ÃâÇö(1582³â) ÀÌÀüÀÇ °ªÀ» »ç¿ëÇÒ ¼ö ÀÖµµ·Ï °èȹµÇÁö ¾Ê¾Ò´Ù. ´Þ·ÂÀÌ ¹Ù²î¾úÀ» ¶§ ¼Õ½ÇµÈ ³¯Â¥´Â °í·ÁÇÏÁö ¾Ê´Â´Ù.
MySQL´Â ³¯Â¥¿¡ ÀÖ´Â 2ÀÚ¸® Çü½ÄÀÇ ¿¬µµ¸¦ 4ÀÚ¸® Çü½ÄÀ¸·Î º¯È¯ÇÑ´Ù´Â °ÍÀ» ±â¾ïÇ϶ó. ¿¹¸¦ µé¾î, '1997-10-07'°ú '97-10-07'´Â µ¿ÀÏÇÑ ³¯Â¥·Î °£ÁÖÇÑ´Ù.
mysql> SELECT TO_DAYS('1997-10-07'), TO_DAYS('97-10-07'); -> 729669, 729669
1582³â ÀÌÀüÀÇ ´Ù¸¥ ³¯Â¥¿¡ ´ëÇØ¼´Â ÀÌ ÇÔ¼ö´Â °á°ú°ªÀÌ Á¤ÀǵÇÁö ¾Ê¾Ò´Ù.
UNIX_TIMESTAMP() , UNIX_TIMESTAMP(date)
ÀÎÀÚ ¾øÀÌ È£ÃâÀÌ µÈ´Ù¸é, ºÎÈ£¾ø´Â Á¤¼öÀÇ À¯´Ð½º ½Ã°£('1970-01-01 00:00:00' GMTºÎÅÍ °è»êµÈ ÃÊ)À» ¹ÝȯÇÑ´Ù. UNIX_TIMESTAMP()°¡ date ÀÎÀÚ¿Í ÇÔ²² È£ÃâµÈ´Ù¸é, '1970-01-01 00:00:00' GMTºÎÅÍ °è»êµÈ ÃÊ °ªÀ» ¹ÝÇÑ´Ù. date ´Â DATE ¹®ÀÚ¿, DATETIME ¹®ÀÚ¿, TIMESTAMP ¹®ÀÚ¿, YYMMDD ¶Ç´Â YYYYMMDD Çü½ÄÀÇ ¼ýÀÚ¸¦ Çã¿ëÇÑ´Ù.
mysql> SELECT UNIX_TIMESTAMP(); -> 882226357 mysql> SELECT UNIX_TIMESTAMP('1997-10-04 22:23:00'); -> 875996580
UNIX_TIMESTAMP°¡ TIMESTAMP Çü½ÄÀ¸·Î »ç¿ëµÇ¾úÀ» ¶§ ÀÌ ÇÔ¼ö´Â ³»ºÎÀÇ TIMESTAMP °ªÀ» Á÷Á¢ ¹ÝȯÇÑ´Ù. ¹«Á¶°Ç ¹®ÀÚ¿À» À¯´Ð½º ½Ã°£À¸·Î º¯È¯ÇÏÁö ¾Ê´Â´Ù. UNIX_TIMESTAMP()¿¡ ¹üÀ§¿¡¼ ¹þ¾î³ ³¯Â¥¸¦ ÀÔ·ÂÇß´Ù¸é 0ÀÌ ¹ÝȯµÇÁö¸¸, ±âº»ÀûÀÎ ¹üÀ§¸¸ È®Àεȴٴ °Í¿¡ ÁÖÀÇÇ϶ó. (¿¬µµ´Â1970-2037, ¿ù 01-12, ÀÏ 01-31)
UTC_DATE , UTC_DATE()
ÇÔ¼ö°¡ »ç¿ëµÈ ¹®¸Æ¿¡ µû¶ó 'YYYY-MM-DD' ¶Ç´Â YYYYMMDD Çü½ÄÀ¸·Î ÇöÀçÀÇ UTC ³¯Â¥ °ªÀ» ¹ÝȯÇÑ´Ù.
mysql> SELECT UTC_DATE(), UTC_DATE() + 0; -> '2003-08-14', 20030814
UTC_DATE()´Â MySQL 4.1.1 ¹öÀüºÎÅÍ »ç¿ëÀÌ °¡´ÉÇÏ´Ù.
UTC_TIME , UTC_TIME()
ÇÔ¼ö°¡ »ç¿ëµÈ ¹®¸Æ¿¡ µû¶ó 'HH:MM:SS' ¶Ç´Â HHMMSS Çü½ÄÀ¸·Î ÇöÀçÀÇ UTC ½Ã°£ °ªÀ» ¹ÝȯÇÑ´Ù.
mysql> SELECT UTC_TIME(), UTC_TIME() + 0; -> '18:07:53', 180753
UTC_TIME()´Â MySQL 4.1.1 ¹öÀüºÎÅÍ »ç¿ëÀÌ °¡´ÉÇÏ´Ù.
UTC_TIMESTAMP , UTC_TIMESTAMP()
ÇÔ¼ö°¡ »ç¿ëµÈ ¹®¸Æ¿¡ µû¶ó 'YYYY-MM-DD HH:MM:SS' ¶Ç´Â YYYYMMDDHHMMSS Çü½ÄÀ¸·Î ÇöÀçÀÇ UTC ÀϽà °ªÀ» ¹ÝȯÇÑ´Ù.
mysql> SELECT UTC_TIMESTAMP(), UTC_TIMESTAMP() + 0; -> '2003-08-14 18:08:04', 20030814180804
UTC_TIMESTAMP()´Â MySQL 4.1.1 ¹öÀüºÎÅÍ »ç¿ëÀÌ °¡´ÉÇÏ´Ù.
WEEK(date[,mode])
date ¿¡ ´ëÇÏ¿© ¸î¹øÂ° ÁÖÀÎÁö ¹ÝȯÇÑ´Ù. 2°³ ÀÎÀÚ¸¦ »ç¿ëÇÏ´Â Çü½Ä¿¡¼´Â, ÇÑ ÁÖÀÇ ½ÃÀÛÀ» ÀÏ¿äÀÏ·Î ÇÒ °ÍÀÎÁö ¿ù¿äÀÏ·Î ÇÒ °ÍÀÎÁö, °á°ú°ªÀÇ ¹üÀ§¸¦ 0-53À¸·Î ÇÒ °ÍÀÎÁö 1-53À¸·Î ÇÒ °ÍÀÎÁö¸¦ ÁöÁ¤ÇÒ ¼ö ÀÖ´Ù. mode ÀÎÀÚ°¡ »ý·«µÇ¸é ½Ã½ºÅÛ ±âº»°ªÀÌ »ç¿ëµÈ´Ù. (MySQL 4.0.14 ¹öÀü ÀÌÀü¿¡´Â 0)
mode ÀÎÀÚ´Â ¾Æ·¡ Ç¥¿Í °°ÀÌ ÀÛµ¿ÇÑ´Ù.
¸ðµå | ÇÑ ÁÖÀÇ ½ÃÀÛ¿äÀÏ | ¹üÀ§ | Week 1 is the first week... |
---|---|---|---|
0 | Sunday | 0-53 | with a Sunday in this year |
1 | Monday | 0-53 | with more than 3 days this year |
2 | Sunday | 1-53 | with a Sunday in this year |
3 | Monday | 1-53 | with more than 3 days this year |
4 | Sunday | 0-53 | with more than 3 days this year |
5 | Monday | 0-53 | with a Monday in this year |
6 | Sunday | 1-53 | with more than 3 days this year |
7 | Monday | 1-53 | with a Monday in this year |
mode 3Àº MySQL 4.0.5 ¹öÀüºÎÅÍ »ç¿ëÇÒ ¼ö ÀÖÀ¸¸ç, 4 ÀÌ»óÀÇ mode ´Â MySQL 4.0.17 ¹öÀüºÎÅÍ »ç¿ëÇÒ ¼ö ÀÖ´Ù.
mysql> SELECT WEEK('1998-02-20'); -> 7 mysql> SELECT WEEK('1998-02-20',0); -> 7 mysql> SELECT WEEK('1998-02-20',1); -> 8 mysql> SELECT WEEK('1998-12-31',1); -> 53
ÁÖÀÇ : MySQL 4.0 ¹öÀü¿¡¼ WEEK(date,0)´Â ¹Ì±¹ ´Þ·Â¿¡ ¾Ë¸Â°Ô º¯Çß´Ù. ±× Àü¿¡ WEEK()´Â ¹Ì±¹ ³¯Â¥¿¡¼ À߸ø °è»êµÇ¾ú´Ù. (»ç½Ç»ó WEEK(date)¿Í WEEK(date,0)´Â ¸ðµç °æ¿ì¿¡ ¿À·ù°¡ ÀÖ¾ú´Ù.)
ÀÌÀü ¿¬µµÀÇ ¸¶Áö¸·ÁÖ¿¡¼ ³¯Â¥°¡ ¸Â¾Æ¶³¾îÁö¸é, mode ÀÎÀÚ¸¦ 2, 3, 6, 7 À¸·Î ¼±ÅÃÇÏÁö ¾Ê´Â ÇÑ MySQLÀº 0À» ¹ÝȯÇÑ´Ù.
mysql> SELECT YEAR('2000-01-01'), WEEK('2000-01-01',0); -> 2000, 0
¾î¶² ÀÌ´Â ½ÇÁ¦·Î ÁÖ¾îÁø ³¯Â¥°¡ 1999³âÀÇ 52°ÁÖÀ̱⠶§¹®¿¡ WEEK() ÇÔ¼ö°¡ 52¸¦ ¹ÝÈ¯ÇØ¾ß ÇÑ´Ù°í ÁÖÀåÇÑ´Ù. ¿ì¸®´Â ÁÖ¾îÁø ¿¬µµ¿¡¼ ¸î °ÁÖÀÎÁö ¹ÝȯÇÒ °ÍÀ» ¿øÇ߱⠶§¹®¿¡ ±× ´ë½Å 0À» ¹ÝȯÇϱâ·Î Çß´Ù. À̰ÍÀº WEEK() ÇÔ¼ö¸¦ ³¯Â¥¿¡¼ DATE ºÎºÐÀ» ÃßÃâÇÏ´Â ´Ù¸¥ ÇÔ¼öµé°ú °áÇÕÇÏ¿© »ç¿ëÇÒ ¶§ À¯¿ëÇÏ´Ù.
ÁÖ¾îÁø ³¯Â¥ÀÇ ÁÖ°£ ù³¯À» Æ÷ÇÔÇÑ ÇØ¸¦ °í·ÁÇÑ °á°ú°ªÀ» ¿øÇÑ´Ù¸é, mode ÀÎÀÚ¸¦ 0, 2, 5, 7 ·Î ¼±ÅÃÇØ¾ß ÇÑ´Ù.
mysql> SELECT WEEK('2000-01-01',2); -> 52
YEARWEEK() ÇÔ¼ö¸¦ »ç¿ëÇÏ´Â °Íµµ ´ë¾ÈÀÌ µÉ ¼ö ÀÖ´Ù.
mysql> SELECT YEARWEEK('2000-01-01'); -> 199952 mysql> SELECT MID(YEARWEEK('2000-01-01'),5,2); -> '52'
WEEKDAY(date)
date ¿¡ ´ëÇÑ ¿äÀÏ »öÀΰª(0 = ¿ù¿äÀÏ, 1 = È¿äÀÏ, ... 6 = ÀÏ¿äÀÏ)À» ¹ÝȯÇÑ´Ù.
mysql> SELECT WEEKDAY('1998-02-03 22:23:00'); -> 1 mysql> SELECT WEEKDAY('1997-11-05'); -> 2
WEEKOFYEAR(date)
date ÀÇ ´Þ·Â¿¡¼ ¸î ÁÖ°ÀÎÁö 1-53 ¹üÀ§ÀÇ °ªÀ» ¹ÝȯÇÑ´Ù. ÀÌ ÇÔ¼ö´Â WEEK(date,3)°ú µ¿ÀÏÇÏ´Ù.
mysql> SELECT WEEKOFYEAR('1998-02-20'); -> 8
WEEKOFYEAR()´Â MySQL 4.1.1 ¹öÀüºÎÅÍ »ç¿ëÀÌ °¡´ÉÇÏ´Ù.
YEAR(date)
date ¿¡¼ ÇØ´ç ¿¬µµ¸¦ ¹ÝȯÇÑ´Ù. (¹üÀ§ 1000-9999)
mysql> SELECT YEAR('98-02-03'); -> 1998
YEARWEEK(date) , YEARWEEK(date,start)
date ¿¡ ´ëÇÏ¿© ¿¬µµ¿Í ¸î ÁÖ°ÀÎÁö ¹ÝȯÇÑ´Ù. ù¹øÂ° ÀÎÀÚ´Â WEEK()ÀÇ Ã¹¹øÂ° ÀÎÀÚ¿Í Á¤È®ÇÏ°Ô ÀÛµ¿ÇÑ´Ù. °á°ú ¾ÈÀÇ ¿¬µµ´Â ù ÁÖ¿Í ¸¶Áö¸· ÁÖ¿¡ ÇÑÇÏ¿© date ÀÎÀÚÀÇ ¿¬µµ¿Í ´Ù¸¦ ¼ö ÀÖ´Ù.
mysql> SELECT YEARWEEK('1987-01-01'); -> 198653
ÁÖ¼ö´Â ¼±Åà ÀÎÀÚ 0 ¶Ç´Â 1 ¿¡ ´ëÇÏ¿© WEEK() ÇÔ¼ö°¡ ¹ÝȯÇÏ´Â °Í°ú ´Ù¸£´Ù. WEEK()´Â ÁÖ¾îÁø ¿¬µµÀÇ ¹®¸Æ¿¡¼ ÁÖ °ªÀ» ¹ÝȯÇÑ´Ù.
YEARWEEK()´Â MySQL 3.23.8 ¹öÀü¿¡¼ Ãß°¡µÇ¾ú´Ù.
MySQL ¼³Ä¡Çϱâ | MySQL |