[ORACLE] 오라클 시간계산

시간단위 24
분단위 24 * 60
초단위 24 * 60 * 60

SELECT CURRENT_DATE, SYSDATE, FROM_TZ(timestamp_value,time_zone_value)
FROM DUAL

TO_DSINTERVAL
TO_DSINTERVAL(CHAR 'nlsparam')
SELECT *
FROM PLAN_TABLE

SELECT ER.REPAIR_TO_TIME, TO_DATE(ER.REPAIR_TO_TIME, 'HH24MISS'), ER.REPAIR_FROM_TIME, TO_DATE(ER.REPAIR_FROM_TIME, 'HH24MISS'),
TO_DATE(ER.REPAIR_TO_TIME, 'HH24MISS') - TO_DATE(ER.REPAIR_FROM_TIME, 'HH24MISS'),
FLOOR(TO_DATE(ER.REPAIR_TO_TIME, 'HH24MISS') - TO_DATE(ER.REPAIR_FROM_TIME, 'HH24MISS') ) "일",
MOD( TO_DATE(ER.REPAIR_TO_TIME, 'HH24MISS') - TO_DATE(ER.REPAIR_FROM_TIME, 'HH24MISS'), 1) * 24,
FLOOR( MOD( TO_DATE(ER.REPAIR_TO_TIME, 'HH24MISS') - TO_DATE(ER.REPAIR_FROM_TIME, 'HH24MISS'), 1) * 24 ) "시간",
MOD( MOD( TO_DATE(ER.REPAIR_TO_TIME, 'HH24MISS') - TO_DATE(ER.REPAIR_FROM_TIME, 'HH24MISS'), 1
) * 24, 1
) * 60,
FLOOR( ROUND(MOD( MOD( TO_DATE(ER.REPAIR_TO_TIME, 'HH24MISS') - TO_DATE(ER.REPAIR_FROM_TIME, 'HH24MISS'), 1
) * 24, 1
) * 60
)
) "분",
FLOOR( MOD( MOD( MOD( TO_DATE(ER.REPAIR_TO_TIME, 'HH24MISS') - TO_DATE(ER.REPAIR_FROM_TIME, 'HH24MISS'), 1
) * 24, 1
) * 60, 1
) * 60
) "초"
FROM L_EQUIP_REPAIR ER

WHERE SUBSTR(ER.REPAIR_FROM_TIME, 1, 2) >= '12'


(enddate-startdate)*24

SELECT FLOOR(CLOSE_TIME - BEGINNING_TIME) AS "일",
FLOOR( (MOD(CLOSE_TIME - BEGINNING_TIME, 1) * 24) ) AS "시",
FLOOR( (MOD( (MOD(CLOSE_TIME - BEGINNING_TIME, 1
) * 24
), 1
) * 60
)
) AS "분"
FROM TEST_REGISTER;


SELECT FLOOR(CLOSE_TIME - BEGINNING_TIME) AS "일",
FLOOR( (MOD(CLOSE_TIME - BEGINNING_TIME, 1) * 24) ) AS "시",
FLOOR( (MOD( (MOD(CLOSE_TIME - BEGINNING_TIME, 1) * 24), 1) * 60) ) AS "분"
FROM TEST_REGISTER;

SELECT ER.*, TRUNC(ROUND(MOD((TO_DATE(ER.REPAIR_TO_TIME, 'HH24MISS') - TO_DATE(ER.REPAIR_FROM_TIME, 'HH24MISS'))*24*60,1)*60)) "sec"
FROM L_EQUIP_REPAIR ER

SELECT TRUNC(ROUND(MOD((date1-date2)*24*60,1)*60)) "sec"
FROM dual;

SELECT FLOOR(29)
FROM DUAL

[출처] 오라클 시간 계산|작성자 ye5chang