ÇöÀç ¼½¼Ç : À¥/ÀÎÅͳÝ

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 Çü½Ä
MICROSECONDMICROSECONDS
SECONDSECONDS
MINUTEMINUTES
HOURHOURS
DAYDAYS
WEEKWEEKS
MONTHMONTHS
QUARTERQUARTERS
YEARYEARS
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 ¹®ÀÚ¿­¿¡´Â ´ÙÀ½ ÁöÁ¤ÀÚµéÀÌ »ç¿ëµÈ´Ù.

ÁöÁ¤ÀÚ¼³¸í
%aAbbreviated weekday name (Sun..Sat)
%bAbbreviated month name (Jan..Dec)
%cMonth, numeric (0..12)
%DDay of the month with English suffix (0th, 1st, 2nd, 3rd, ...)
%dDay of the month, numeric (00..31)
%eDay of the month, numeric (0..31)
%fMicroseconds (000000..999999)
%HHour (00..23)
%hHour (01..12)
%IHour (01..12)
%iMinutes, numeric (00..59)
%jDay of year (001..366)
%kHour (0..23)
%lHour (1..12)
%MMonth name (January..December)
%mMonth, numeric (00..12)
%pAM or PM
%rTime, 12-hour (hh:mm:ss followed by AM or PM)
%SSeconds (00..59)
%sSeconds (00..59)
%TTime, 24-hour (hh:mm:ss)
%UWeek (00..53), where Sunday is the first day of the week
%uWeek (00..53), where Monday is the first day of the week
%VWeek (01..53), where Sunday is the first day of the week; used with %X
%vWeek (01..53), where Monday is the first day of the week; used with %x
%WWeekday name (Sunday..Saturday)
%wDay of the week (0=Sunday..6=Saturday)
%XYear for the week where Sunday is the first day of the week, numeric, four digits; used with %V
%xYear for the week, where Monday is the first day of the week, numeric, four digits; used with %v
%YYear, numeric, four digits
%yYear, 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...
0Sunday0-53with a Sunday in this year
1Monday0-53with more than 3 days this year
2Sunday1-53with a Sunday in this year
3Monday1-53with more than 3 days this year
4Sunday0-53with more than 3 days this year
5Monday0-53with a Monday in this year
6Sunday1-53with more than 3 days this year
7Monday1-53with 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 ¹öÀü¿¡¼­ Ãß°¡µÇ¾ú´Ù.