?????. 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
|
|
|
