날짜와 날짜 사이 경과 시 분 초 구하는 예제.

select TRUNC((to_date('20111010100203','yyyymmddhh24miss')-to_date('20111009090000', 'yyyymmddhh24miss'))*24) || ' hour ' || 

select (to_date('1800', 'hh24mi') - to_date('0900', 'hh24mi'))*(24*60*60 )
 from dual;

to_date 로 계산후

(24 -- 시간표시

*60 -- 분표시

*60 ) -- 초표시

- 현재시간에 시간 추가하기

select sysdate,sysdate+1/(24*60)*10 from dual

sysdate+1은 1일 이후입니다.

그래서 1/(24*60)은 1분입니다.

10을 마지막에 곱하면 10분이죠 ^^

일 가(감) 산 SYSDATE + 1

시간 가(감) 산 SYSDATE + 1/24

분 가(감) 산 SYSDATE + 1/24/60

초 가(감) 산 SYSDATE + 1/24/60/60

-- 날자와 날짜사이의 시간 구하기
select to_date('2005050309','yyyymmddhh')-to_date('2005050409', 'yyyymmddhh'))*24
from dual;
;
                               
-- 날짜에 시간더하기
select to_char(to_date('200305021120', 'yyyymmddhh24mi'),  'yyyymmddhh24mi'),
to_char(to_date('200305021120', 'yyyymmddhh24mi') + (115/1440), 'yyyymmddhhmi') from dual;

-- 날짜수 구하기
select
  to_date('20050301', 'yyyymmdd')+1 - to_date('20050225', 'yyyymmdd')
from dual;
->여기서 하루치를 더한거는 그마지막 날을 포함하기 위해 하루를 더해야한다

-- 개월수 구하기
select months_between(to_date('20050131', 'yyyymmdd'), to_date('20010201', 'yyyymmdd')) from dual;

-- 그달의 마지막 날짜 구하기
select to_char(last_day(to_date('20040201','yyyymmdd')), 'yyyymmdd')
from dual;

select to_char(last_day(to_date('20040201','yyyymmdd')), 'mm')
from dual;

select to_char(last_day(to_date('20040201','yyyymmdd')), 'dd')
from dual;

-- 하루를 더추가하고 2달 뒤의 일자구하기
select to_char(add_months(to_date('20050201','yyyymmdd')+1, 2), 'yyyymmdd')
from dual;

-- 하루를 더추가하고 2달 앞의 일자구하기
select to_char(add_months(to_date('20050201','yyyymmdd')+1, 2), 'yyyymmdd')
from dual;

-- 해달일부터 2달 뒤의 일자구하기
select to_char(add_months(to_date('20050201','yyyymmdd'),2), 'yyyymmdd')
from dual;

- months_between : 두날짜간의 달수 구하기

select  months_between (to_date('20050201','yyyymmdd'), to_date('20050101', 'yyyymmdd'))
from dual;

- next_day 특정날자에서 가장가까운 요일의 날자 찾기

select next_day(to_date('20050101','yyyymmdd'), '일') from dual

TRUNC 함수의 제2 인수에'day'를 이용하면…….

이러한 방법으로 간단하게 주의 처음의 날을 취득할 수 있습니다. 물론 SYSDATE 함수 대신에, SYSTIMESTAMP 함수를 사용할 수도 있습니다.다만, DATE형으로 변환되는 것은 기억해 둘 필요가 있습니다.

날짜 데이터로 TRUNC 함수를 사용하는 경우, 제2 인수에는'day'외에도 몇개인가 지정할 수 있습니다.메뉴얼을 확인하삼!!

TRUNC 함수를 사용한 샘플 SQL를  1개들어 둡니다.제목은 「이번 달의 제n○요일을 취득한다」

- sysdate = 19950725

-  round(sysdate, 'month') -> 19950801

-  round(sysdate,'year') -> 19960101

-  trunc(sysdate, 'month') -> 1950701

-  trunc(sysdate, 'year') -> 19950101

- 형식

 scc , cc : 세기 표현

 year : 년도를 영어로 표현

yyyy, yyy, yy, y : 년도를 자릿수로 자른다

bc, ad : 서기 등으로 표시

q : 분기 표시

mm : 두자리로 월표시

month: 영어로 표시

mon: 영어로 3자리로 월표시

rm: 로마자로 표시 i, ii. xi

ww: 1년기준 몇째주 표시

w:한달기준 몇째주 표시

ddd: 365(1년기준 ) 의 몇째 일

dd: 날짜를 두자리로 표시

d: 요일을 숫자로 표시

dy : 요일 한자리로 표시

day: 요일 표시

am, pm, a.m. , p.m. : 오전오후 표시

hh, hh12 : 12시 기준으로 표시

hh24 : 24시 기준으로 표시

/, "of" : 날짜의 중간에 문자 표시 -> to_char(to_date('19951201', 'yyyymmdd'),'yyyy "of" mm/dd')

spth : 날짜를 영문 서수로 표시

sp : 날짜를 영문 숫자로 표시

-- 두 날짜(date type)사이의 시분초 계산하기

-- 한 레코드에 속하지 않은 각각의 Attribute인 경우를 가정함

-- 간단히 시간계산은

-- 첫째 방법
select (A.endtime - B.starttime) * (24*60*60)
from (select endtime
        from iptimetable where marketclass = 1
        ) A
     , (select starttime
        from iptimetable where marketclass = 2
        ) B     ;

-- 두번째 방법
select to_char((A.endtime - B.starttime) * (24*60*60),'099999')
from (select endtime
        from iptimetable where marketclass = 1
        ) A
     , (select starttime
        from iptimetable where marketclass = 2
        ) B     ;

-- 시분초로 변환
select trunc(mod((A.endtime - B.starttime)*24,1)*60) || ' 분 ' ||
trunc(round(mod((A.endtime - B.starttime)*24*60,1)*60)) || ' 초 '
from (select endtime
        from iptimetable where marketclass = 1
        ) A
     , (select starttime
        from iptimetable where marketclass = 2
        ) B     ;

-- 일,시간,분,초로 표현
select trunc(A.endtime - B.starttime) || ' day ' ||
trunc(mod((A.endtime - B.starttime),1)*24) || ' hour ' ||
trunc(mod((A.endtime - B.starttime)*24,1)*60) || ' minute ' ||
trunc(round(mod((A.endtime - B.starttime)*24*60,1)*60)) || ' second '
from (select endtime
        from iptimetable where marketclass = 1
        ) A
     , (select starttime
        from iptimetable where marketclass = 2
        ) B     ;

-- 또 다른 표현

SELECT FLOOR((to_time - from_time)*24) hh, 
( (to_time - from_time)*24 - FLOOR((to_time - from_time)*24) )*60 mm 
FROM (SELECT to_date('17:00','hh24:mi') to_Time, to_date('8:30','hh24:mi') from_Time FROM dual)

SELECT GROUP_CODE, TASK_CODE, UPPER_TASK_CODE, TASK_NAME, ORDER_DESC, LEVEL AS LEV
  FROM BPM_TEMPLETE
 WHERE GROUP_CODE=2
CONNECT BY PRIOR TASK_CODE = UPPER_TASK_CODE
START WITH UPPER_TASK_CODE = 0
order by order_desc

위와 같은 방식으로 출력하기 위해서는 CTE와 집계함수를 이용하면 된다.

처음 CTE의 구조를 몰랐을 때는 왜 이렇게 나오는 몰라서 참 고민을 많이 했었다.

하지만 구조를 이해하고 나면 정말 간편하게 출력할 수 있게 되었다.

처음으로 CTE의 기본 문법만 사용하면 아래와 같이 쿼리를 작성할 수 있다.

WITH BPM_CTE(GROUP_CODE, TASK_CODE, UPPER_TASK_CODE, TASK_ORDER, TASK_NAME, TASK_DESC, TASK_ROLL, ORDER_DESC, LEVEL)
AS
(
    SELECT GROUP_CODE, TASK_CODE, UPPER_TASK_CODE, TASK_ORDER, TASK_NAME, TASK_DESC, TASK_ROLL, ORDER_DESC, 1 AS LEVEL
 FROM BPM_TEMPLETE T
 WHERE GROUP_CODE=2

    UNION ALL
   SELECT C.GROUP_CODE, C.TASK_CODE, C.UPPER_TASK_CODE, C.TASK_ORDER, C.TASK_NAME, C.TASK_DESC, C.TASK_ROLL, C.ORDER_DESC, LEVEL+1
  FROM BPM_TEMPLETE C
 INNER JOIN BPM_CTE P
 ON C.UPPER_TASK_CODE = P.TASK_CODE
 WHERE C.GROUP_CODE=2
)
SELECT GROUP_CODE, TASK_CODE, UPPER_TASK_CODE, TASK_ORDER, TASK_NAME, TASK_DESC, TASK_ROLL, ORDER_DESC, LEVEL
FROM BPM_CTE
WHERE GROUP_CODE=2

ORDER BY ORDER_DESC ASC

결과는 위와같이 같은 정보에 LEVEL이 중복되어서 출력된다.

이와같이 나온 이유는 UNION ALL 이 일반적으로 적용되는 방식과 달라서 헷갈릴 것이다.

이유는

부모 ROW에 자식 ROW가 LEVEL이 증가하면서 쌓이기 때문에 위와같은 결과가 나올 수 밖에 없다.

그래서 집계함수를 사용하여 아래와 같이 쿼리를 작성하였다.

WITH BPM_CTE(GROUP_CODE, TASK_CODE, UPPER_TASK_CODE, TASK_NAME, ORDER_DESC, LEVEL)
AS
(
    SELECT GROUP_CODE, TASK_CODE, UPPER_TASK_CODE, TASK_NAME, ORDER_DESC, 1 AS LEVEL
 FROM BPM_TEMPLETE T
 WHERE GROUP_CODE=2

    UNION ALL
   SELECT C.GROUP_CODE, C.TASK_CODE, C.UPPER_TASK_CODE, C.TASK_NAME, C.ORDER_DESC, LEVEL+1
  FROM BPM_TEMPLETE C
 INNER JOIN BPM_CTE P
 ON C.UPPER_TASK_CODE = P.TASK_CODE
 WHERE C.GROUP_CODE=2
)
SELECT GROUP_CODE, TASK_CODE, UPPER_TASK_CODE, TASK_NAME, ORDER_DESC, MAX(LEVEL) AS LEVEL
FROM BPM_CTE
WHERE GROUP_CODE=2
GROUP BY GROUP_CODE, TASK_CODE, UPPER_TASK_CODE, TASK_NAME, ORDER_DESC

ORDER BY ORDER_DESC ASC

결과는 아래와 같이 깔끔하게 출력된다.

계층도를 뽑을 때 상당히 유용하게 사용되니 필요하신 분은 퍼가시고 Comment도 남겨주세요