¾È³çÇϼ¼¿ä. DB2 ¹× XpertMon »ç¿ëÀÚ ¿©·¯ºÐ!
¢ß ¾ÆÀÌƼ¿¢½ºÆÛÆ®±×·ì DB »ç¾÷ºÎÀÔ´Ï´Ù.
À̹øÁÖ ´º½º·¹ÅÍ¿¡¼´Â DB2 ¿¡¼ ³¯Â¥¿Í ½Ã°£À» Ç¥ÇöÇÏ´Â ¹æ¹ý¿¡ ´ëÇØ Á¤¸®Çغ¸¾Ò½À´Ï´Ù.
¸¹Àº µµ¿òÀÌ µÇ½Ã±æ ¹Ù¶ø´Ï´Ù.
³¯Â¥/½Ã°£ Ç¥Çö½Ä
±âº»»çÇ×
ÇöÀç ³¯Â¥, ½Ã°£, ½Ã°£¼ÒÀÎÀ» ¾òÀ¸·Á¸é ´ÙÀ½°ú °°ÀÌ ¼öÇàÇÕ´Ï´Ù.
- select current date(time,timestamp) from sysibm.sysdummy1
- values current date(time,timestamp)
ƯÁ¤ ³¯Â¥, ½Ã°£, ½Ã°£¼ÒÀο¡¼ ÇØ´ç ³âµµ, ¿ù, ÀÏ, ½Ã, ºÐ, ÃÊ, ¸¶ÀÌÅ©·Î ÃÊ, ³¯Â¥, ½Ã°£À»
ÃßÃâÇÏ·Á¸é ¾Æ·¡¿Í °°Àº ÇÔ¼ö¸¦ »ç¿ëÇÕ´Ï´Ù.
YEAR (current timestamp) , MONTH (current timestamp), DAY (current timestamp)
HOUR (current timestamp), MINUTE (current timestamp), SECOND (current timestamp)
MICROSECOND (current timestamp), DATE ( current timestamp), TIME (current timestamp)
|
³¯Â¥³ª ½Ã°£¿¡ ´ëÇÑ °è»êÀº ¾Æ·¡¿Í °°ÀÌ ÇÕ´Ï´Ù.
current date + 1 YEAR
current date + 3 YEARS + 2 MONTHS + 15 DAYS
current time + 5 HOURS ? 3 MINUTES + 10 SECONDS
|
µÎ ³¯Â¥°£ÀÇ Â÷ÀÌ°¡ ¸î ÀÏÀ̳ª ³ª´ÂÁö °è»êÇϱâ À§Çؼ´Â ´ÙÀ½°ú °°ÀÌ °è»êÇÕ´Ï´Ù.
days ( current date ) ? days (date(¡®2003-02-21¡¯) )
|
ÇöÀçÀÇ ½Ã°£¼ÒÀο¡¼ ¸¶ÀÌÅ©·Î ÃÊ ºÎºÐÀ» 0À¸·Î ¸¸µå´Â ¹æ¹ýÀº ´ÙÀ½°ú °°½À´Ï´Ù.
current timestamp ? MICROSECOND ( current timestamp ) MICROSECONDS
|
³¯Â¥³ª ½Ã°£À» ´Ù¸¥ ¹®ÀÚ¿°ú ¿¬°á½ÃÅ°°í ½ÍÀ» ¶§´Â ¿ì¼± ÅؽºÆ® ÇüÅ·ΠÇü º¯È¯À» °ÅÃÄ¾ß ÇÕ´Ï´Ù.
ÅؽºÆ® ÇüÅ·ÎÀÇ Çü º¯È¯Àº CHAR() ÇÔ¼ö¸¦ »ç¿ëÇÕ´Ï´Ù.
CHAR ( current date )
CHAR ( current time )
CHAR ( current date + 12 HOURS )
|
¹®ÀÚ¿À» ³¯Â¥³ª ½Ã°£ °ªÀ¸·Î º¯È¯ÇÏ°íÀÚ ÇÒ °æ¿ì, ´ÙÀ½°ú °°ÀÌ ÇÕ´Ï´Ù.
TIMESTAMP ( ¡®2005-07-21-12.00.00.000000¡¯ )
TIMESTAMP ( ¡®2005-04-07 12:00:00 )
DATE ( ¡®2004-09-10¡¯ )
DATE ( ¡®2002/06/30¡¯ )
TIME ( ¡¯12:00:00¡¯ )
TIME ( ¡¯12.00.00¡¯ )
|
TIMESTAMP(), DATE(), TIME() ÇÔ¼ö´Â À§ÀÇ ¿¹ ¿Ü¿¡µµ ´Ù¾çÇÑ Æ÷¸ËÀ» Áö¿øÇÕ´Ï´Ù.
³¯Â¥ÇÔ¼ö
¶§¶§·Î, µÎ ½Ã°£¼ÒÀÎÀÇ Â÷¸¦ ¾Ë¾Æ³¾ ÇÊ¿ä°¡ ÀÖ½À´Ï´Ù.
ÀÌ·± °æ¿ì DB2´Â TIMESTAMPDIFF() ¶ó´Â ³»ÀåÇÔ¼ö¸¦ Áö¿øÇÕ´Ï´Ù.
ÇÏÁö¸¸ ÀÌ ÇÔ¼ö´Â À±³âÀ» °è»êÇÏÁö ¾Ê°í, ÇÑ ´ÞÀ» 30ÀÏ·Î °è»êÇϱ⠶§¹®¿¡ ´ë·«ÀûÀÎ °ªÀ» ¹ÝȯÇÕ´Ï´Ù.
ÇÔ¼öÀÇ »ç¿ë ¿¹´Â ´ÙÀ½°ú °°½À´Ï´Ù.
timestampdiff ( , char ( timestamp ( `2004-09-11-00.00.00`) - timestamp ( `2004-09-02-00.00.00`) ) )
|
¿©±â¼
Àº °á°ú °ªÀÇ Æ÷¸ËÀ» ³ªÅ¸³À´Ï´Ù.
- 1 : ÃÊÀÇ ¼Ò¼öºÎºÐ
- 2 : ÃÊ
- 4 : ºÐ
- 8 : ½Ã
- 16 : ÀÏ
- 32 : ÁÖ
- 64 : ¿ù
- 128 : ºÐ±â
- 256 : ³â
TIMESTAMPDIFF() ÇÔ¼ö´Â µÎ ³¯Â¥°£ÀÇ »çÀÌ°¡ °¡±î¿ï¼ö·Ï Á¤È®ÇÕ´Ï´Ù.
Á»´õ Á¤È®ÇÑ °ªÀ» ¾ò°í ½Í´Ù¸é ´ÙÀ½°ú °°Àº ¹æ¹ýÀ» ÀÌ¿ëÇÒ ¼ö ÀÖ½À´Ï´Ù.
( DAYS ( t1 ) - DAYS( t2 ) ) * 86400 + (MIDNIGHT_SECONDS ( t1 ) - MIDNIGHT_SECONDS ( t2 ) )
|
À§ÀÇ °ªÀ» ÀÚÁÖ »ç¿ëÇØ¾ß ÇÑ´Ù¸é, »ç¿ëÀÚ ÇÔ¼ö¸¦ »ý¼ºÇؼ ¾µ ¼öµµ ÀÖ½À´Ï´Ù.
ƯÁ¤ ³âµµ°¡ À±³âÀÎÁö È®ÀÎÇÏ°í ½Í´Ù¸é ´ÙÀ½°ú °°ÀÌ À±³âÀÇ ÀÏ ¼ö¸¦ ´Ù¸£°Ô ¹ÝȯÇÏ´Â ÇÔ¼ö¸¦ ¸¸µå½Ê½Ã¿À.
³¯Â¥³»ÀåÇÔ¼ö ¿ä¾à
DB2¿¡¼ Áö¿øÇÏ´Â ³¯Â¥ Á¶ÀÛ¿¡ °ü·ÃµÈ ³»Àå ÇÔ¼ö´Â ´ÙÀ½°ú °°Àº °ÍµéÀÌ ÀÖ½À´Ï´Ù.
ÀÚ¼¼ÇÑ ³»¿ëÀº SQL ÂüÁ¶¼¸¦ Âü°íÇϽñ⠹ٶø´Ï´Ù.
SQL ³¯Â¥ / ½Ã°£ ÇÔ¼ö |
DAYNAME | ÀÔ·ÂÇÑ ³¯ÀÇ ¿äÀÏ ¸íÀ» ¹Ýȯ (¿¹. Friday) |
DAYOFWEEK | ÀÔ·ÂÇÑ ³¯À» ÁÖÁßÀÇ 1ºÎÅÍ 7±îÁöÀÇ ¼ýÀÚ·Î ¹Ýȯ, 1 - ÀÏ¿äÀÏ |
DAYOFWEEK_ISO | ÀÔ·ÂÇÑ ³¯À» ÁÖÁßÀÇ 1ºÎÅÍ 7±îÁöÀÇ ¼ýÀÚ·Î ¹Ýȯ, 1 - ¿ù¿äÀÏ |
DAYOFYEAR | ÀÔ·ÂÇÑ ³¯À» ¿¬ÁßÀÇ 1ºÎÅÍ 366±îÁöÀÇ ¼ýÀÚ·Î ¹Ýȯ |
DAYS | ³¯Â¥¸¦ Integer ÇüÅ·Π¹Ýȯ |
JULIAN_DAY | Julian ´Þ·ÂÀÇ Ã¹³¯ ( ±â¿øÀü 4712³â 1¿ù 1ÀÏ) ºÎÅÍ ÀÔ·ÂÇÑ ³¯±îÁöÀÇ ³¯¼ö¸¦ Integer ÇüÅ·Π¹Ýȯ |
MIDNIGHT_SECONDS | ÀÚÁ¤ºÎÅÍ ÀÔ·ÂÇÑ ½Ã°£±îÁöÀÇ Ãʸ¦ 0ºÎÅÍ 86400 ±îÁöÀÇ Integer ÇüÅ·Π¹Ýȯ |
MONTHNAME | ¿ù ¸íÀ» ¹Ýȯ (¿¹. January) |
TIMESTAMP_ISO | ³¯Â¥, ½Ã°£, ½Ã°£¼ÒÀÎÀ¸·Î ÀÔ·ÂµÈ °ªÀ» ½Ã°£¼ÒÀÎ °ªÀ¸·Î ¹Ýȯ |
TIMESTAMP_FORMAT | ¹®ÀÚ¿ Çü½ÄÀ¸·Î ÀÔ·ÂµÈ °ªÀ» ½Ã°£¼ÒÀÎ °ªÀ¸·Î ¹Ýȯ |
TIMESTAMPDIFF | µÎ ÀÔ·Â ½Ã°£¼ÒÀΰ£ Â÷ÀÌÀÇ ´ë·«°ªÀ» ¿øÇÏ´Â ÇüÅ·Π¹Ýȯ |
TO_CHAR | ¹®ÀÚ¿ ÇüÅ·ΠÀÔ·ÂµÈ ½Ã°£¼ÒÀÎÀ» ¹®ÀÚ¿·Î ¹Ýȯ, VARCHAR_FORMAT °ú À¯»ç |
TO_DATE | ¹®ÀÚ¿ ÇüÅ·ΠÀÔ·ÂµÈ ½Ã°£¼ÒÀÎÀ» ½Ã°£¼ÒÀÎÇüÅ·Π¹Ýȯ, TIMESTAMP_FORMAT °ú À¯»ç |
WEEK | ÀÔ·ÂÇÑ ³¯ÀÇ ÁÖ¸¦ 1ºÎÅÍ 54±îÁöÀÇ Integer ÇüÅ·Π¹Ýȯ, ÁÖ´Â ÀÏ¿äÀϺÎÅÍ ½ÃÀÛ |
WEEK_ISO | ÀÔ·ÂÇÑ ³¯ÀÇ ÁÖ¸¦ 1ºÎÅÍ 53±îÁöÀÇ Integer ÇüÅ·Π¹Ýȯ |
³¯Â¥/½Ã°£ Æ÷¸ËÀÇ Ä¿½ºÅ͸¶ÀÌ¡
À§ÀÇ Áö¿ªº° Æ÷¸Ë ÀÌ¿Ü¿¡ `yyyymmdd` ¿Í °°Àº ÀڽŸ¸ÀÇ Æ÷¸ËÀ¸·Î ³ªÅ¸³»°í ½ÍÀº °æ¿ì¿£
Æ÷¸Ë º¯È¯ ÇÔ¼ö¸¦ »ý¼ºÇÏ¿© »ç¿ëÇÏ¸é µË´Ï´Ù.
create function ts_fmt(TS timestamp, fmt varchar(20))
returns varchar(50)
return
with tmp (dd,mm,yyyy,hh,mi,ss,nnnnnn) as
(
select
substr( digits (day(TS)),9),
substr( digits (month(TS)),9) ,
rtrim(char(year(TS))) ,
substr( digits (hour(TS)),9),
substr( digits (minute(TS)),9),
substr( digits (second(TS)),9),
rtrim(char(microsecond(TS)))
from sysibm.sysdummy1 )
select
case fmt
when 'yyyymmdd' then yyyy || mm || dd
when 'mm/dd/yyyy' then mm || '/' || dd || '/' || yyyy
when 'yyyy/dd/mm hh:mi:ss'
then yyyy || '/' || mm || '/' || dd || ' ' || hh || ':' || mi || ':' || ss
when 'nnnnnn' then nnnnnn
else 'date format ' || coalesce(fmt,' ') || ' not recognized.'
end
from tmp
end
|
´Ù¼Ò º¹ÀâÇØ º¸À̱ä ÇÏÁö¸¸ ½ÇÁ¦·Î´Â ´Ü¼øÇÑ ÇÔ¼öÀÔ´Ï´Ù.
°øÅë Å×À̺í Ç¥Çö½Ä(CTE)À» »ç¿ëÇÏ¿© ÀÔ·ÂµÈ ½Ã°£¼ÒÀÎÀ» °¢°¢ÀÇ ¿ä¼Ò·Î ºÐÇØÇÏ°í,
µÎ ¹ø° ÀÔ·Â °ªÀ¸·Î ÁÖ¾îÁø Æ÷¸ËÀ¸·Î Àç Á¶ÇÕÇÏ¿© Ãâ·ÂÇÏ´Â ÇÔ¼öÀÔ´Ï´Ù.
À§ÀÇ ÇÔ¼ö¿¡ ´Ù¸¥ WHEN¿¡ ´Ù¸¥ Æ÷¸ËÀ» Ãß°¡ÇÏ¿© È®ÀåÇÒ ¼öµµ ÀÖ½À´Ï´Ù.
¿¹»óÇÏÁö ¸øÇÑ Æ÷¸ËÀÌ ¿äûµÇ¸é ¿¡·¯¸Þ½ÃÁö°¡ ¹ß»ýÇÏ°Ô µË´Ï´Ù.
XpertMon for DB2 UDB V2
DB »ç¾÷ºÎ
Tel : 02-2108-1458
Fax : 02-2108-1459
Mobile : 011-896-6545
E-mail : hjlee@iteg.co.kr
URL : http://iteg.co.kr