시점을 기준으로 하여, 종점이 되었을 때, 시, 분, 초가 얼마나 경과하였는지 경과시간을 알고 싶다는 문의가 왔습니다. 처음에는 대수롭지 않게 생각했는데, 시간을 계산하는 것이 생각보다 어렵더라고요.
버림, 반올림, 올림 등의 함수에 따라 결과가 달라질 수 있으니 사용하기 전에 꼭 점검해 주세요. 특히 초 단위에서 단위가 너무 작다보니 오차가 조금만 발생하여도 값이 이상하게 나오더라고요. 😂
자, 이제 시작하겠습니다. 두 날짜 간의 경과 시간을 계산하고 시, 분, 초로 변환하려고 합니다! 이를 위해 TRUNC와 함께 적절한 계산을 적용해야 하는데요, 각 단계에서 계산된 값들이 시, 분, 초로 분리될 수 있도록 식을 만들어 보았습니다. '일', 그리고 '시'에 대한 이견은 거의 없으리라 보는데요, '분'과 '초'는 입맛에 따라 변경하여 처리하세요.
아래는 두 날짜 간의 차이를 일, 시, 분, 초로 변환하는 예제입니다.
WITH TIME
AS (SELECT TO_DATE ('2024-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
AS BEGIN_TIME,
TO_DATE ('2024-01-31 23:59:59', 'YYYY-MM-DD HH24:MI:SS')
AS END_TIME
FROM DUAL),
TIME_SERIES
AS ( SELECT BEGIN_TIME + (LEVEL - 1) / (24 * 60 * 60) AS BEGIN_TIME,
END_TIME,
END_TIME - (BEGIN_TIME + (LEVEL - 1) / (24 * 60 * 60)) GAP
FROM TIME
CONNECT BY LEVEL <= (END_TIME - BEGIN_TIME) * 24 * 60 * 60 + 1)
SELECT BEGIN_TIME,
END_TIME,
GAP GAP,
GAP * 24,
GAP * 24 * 60,
GAP * 24 * 60 * 60,
TRUNC (GAP) AS 일,
TRUNC (GAP * 24) - TRUNC (GAP) * 24 AS 시,
TRUNC (GAP * 24 * 60) - TRUNC (GAP * 24) * 60 AS 분,
MOD (ROUND (GAP * 24 * 60 * 60) - TRUNC (GAP * 24 * 60) * 60, 60)
AS 초
FROM TIME_SERIES
ORDER BY BEGIN_TIME;예제 자료는 시작일자와 종료일자는 임의로 설정하였으니, 적당히 가공해 주세요. 2024년 1월 1일 0시 0분 0초부터 2024년 1월 31일 23시 59분 59초까지 경과시간에 따라 차이를 구해보도록 하였습니다. 우선 100개의 행을 예시로 보여드릴텐데요, 모든 자료를 조회하면 100만 건이 넘기 때문이에요.
BEGIN_TI END_TIME GAP GAP*24 GAP*24*60 GAP*24*60*60 일 시 분 초
-------- -------- ---------- ---------- ---------- ------------ ---------- ---------- ---------- ----------
24/01/01 24/01/31 30.9999884 743.999722 44639.9833 2678399 30 23 59 59
24/01/01 24/01/31 30.9999769 743.999444 44639.9667 2678398 30 23 59 58
24/01/01 24/01/31 30.9999653 743.999167 44639.95 2678397 30 23 59 57
24/01/01 24/01/31 30.9999537 743.998889 44639.9333 2678396 30 23 59 56
24/01/01 24/01/31 30.9999421 743.998611 44639.9167 2678395 30 23 59 55
24/01/01 24/01/31 30.9999306 743.998333 44639.9 2678394 30 23 59 54
24/01/01 24/01/31 30.999919 743.998056 44639.8833 2678393 30 23 59 53
24/01/01 24/01/31 30.9999074 743.997778 44639.8667 2678392 30 23 59 52
24/01/01 24/01/31 30.9998958 743.9975 44639.85 2678391 30 23 59 51
24/01/01 24/01/31 30.9998843 743.997222 44639.8333 2678390 30 23 59 50
24/01/01 24/01/31 30.9998727 743.996944 44639.8167 2678389 30 23 59 49
24/01/01 24/01/31 30.9998611 743.996667 44639.8 2678388 30 23 59 48
24/01/01 24/01/31 30.9998495 743.996389 44639.7833 2678387 30 23 59 47
24/01/01 24/01/31 30.999838 743.996111 44639.7667 2678386 30 23 59 46
24/01/01 24/01/31 30.9998264 743.995833 44639.75 2678385 30 23 59 45
24/01/01 24/01/31 30.9998148 743.995556 44639.7333 2678384 30 23 59 44
24/01/01 24/01/31 30.9998032 743.995278 44639.7167 2678383 30 23 59 43
24/01/01 24/01/31 30.9997917 743.995 44639.7 2678382 30 23 59 42
24/01/01 24/01/31 30.9997801 743.994722 44639.6833 2678381 30 23 59 41
24/01/01 24/01/31 30.9997685 743.994444 44639.6667 2678380 30 23 59 40
24/01/01 24/01/31 30.9997569 743.994167 44639.65 2678379 30 23 59 39
24/01/01 24/01/31 30.9997454 743.993889 44639.6333 2678378 30 23 59 38
24/01/01 24/01/31 30.9997338 743.993611 44639.6167 2678377 30 23 59 37
24/01/01 24/01/31 30.9997222 743.993333 44639.6 2678376 30 23 59 36
24/01/01 24/01/31 30.9997106 743.993056 44639.5833 2678375 30 23 59 35
24/01/01 24/01/31 30.9996991 743.992778 44639.5667 2678374 30 23 59 34
24/01/01 24/01/31 30.9996875 743.9925 44639.55 2678373 30 23 59 33
24/01/01 24/01/31 30.9996759 743.992222 44639.5333 2678372 30 23 59 32
24/01/01 24/01/31 30.9996644 743.991944 44639.5167 2678371 30 23 59 31
24/01/01 24/01/31 30.9996528 743.991667 44639.5 2678370 30 23 59 30
24/01/01 24/01/31 30.9996412 743.991389 44639.4833 2678369 30 23 59 29
24/01/01 24/01/31 30.9996296 743.991111 44639.4667 2678368 30 23 59 28
24/01/01 24/01/31 30.9996181 743.990833 44639.45 2678367 30 23 59 27
24/01/01 24/01/31 30.9996065 743.990556 44639.4333 2678366 30 23 59 26
24/01/01 24/01/31 30.9995949 743.990278 44639.4167 2678365 30 23 59 25
24/01/01 24/01/31 30.9995833 743.99 44639.4 2678364 30 23 59 24
24/01/01 24/01/31 30.9995718 743.989722 44639.3833 2678363 30 23 59 23
24/01/01 24/01/31 30.9995602 743.989444 44639.3667 2678362 30 23 59 22
24/01/01 24/01/31 30.9995486 743.989167 44639.35 2678361 30 23 59 21
24/01/01 24/01/31 30.999537 743.988889 44639.3333 2678360 30 23 59 20
24/01/01 24/01/31 30.9995255 743.988611 44639.3167 2678359 30 23 59 19
24/01/01 24/01/31 30.9995139 743.988333 44639.3 2678358 30 23 59 18
24/01/01 24/01/31 30.9995023 743.988056 44639.2833 2678357 30 23 59 17
24/01/01 24/01/31 30.9994907 743.987778 44639.2667 2678356 30 23 59 16
24/01/01 24/01/31 30.9994792 743.9875 44639.25 2678355 30 23 59 15
24/01/01 24/01/31 30.9994676 743.987222 44639.2333 2678354 30 23 59 14
24/01/01 24/01/31 30.999456 743.986944 44639.2167 2678353 30 23 59 13
24/01/01 24/01/31 30.9994444 743.986667 44639.2 2678352 30 23 59 12
24/01/01 24/01/31 30.9994329 743.986389 44639.1833 2678351 30 23 59 11
24/01/01 24/01/31 30.9994213 743.986111 44639.1667 2678350 30 23 59 10
24/01/01 24/01/31 30.9994097 743.985833 44639.15 2678349 30 23 59 9
24/01/01 24/01/31 30.9993981 743.985556 44639.1333 2678348 30 23 59 8
24/01/01 24/01/31 30.9993866 743.985278 44639.1167 2678347 30 23 59 7
24/01/01 24/01/31 30.999375 743.985 44639.1 2678346 30 23 59 6
24/01/01 24/01/31 30.9993634 743.984722 44639.0833 2678345 30 23 59 5
24/01/01 24/01/31 30.9993519 743.984444 44639.0667 2678344 30 23 59 4
24/01/01 24/01/31 30.9993403 743.984167 44639.05 2678343 30 23 59 3
24/01/01 24/01/31 30.9993287 743.983889 44639.0333 2678342 30 23 59 2
24/01/01 24/01/31 30.9993171 743.983611 44639.0167 2678341 30 23 59 1
24/01/01 24/01/31 30.9993056 743.983333 44639 2678340 30 23 59 0
24/01/01 24/01/31 30.999294 743.983056 44638.9833 2678339 30 23 58 59
24/01/01 24/01/31 30.9992824 743.982778 44638.9667 2678338 30 23 58 58
24/01/01 24/01/31 30.9992708 743.9825 44638.95 2678337 30 23 58 57
24/01/01 24/01/31 30.9992593 743.982222 44638.9333 2678336 30 23 58 56
24/01/01 24/01/31 30.9992477 743.981944 44638.9167 2678335 30 23 58 55
24/01/01 24/01/31 30.9992361 743.981667 44638.9 2678334 30 23 58 54
24/01/01 24/01/31 30.9992245 743.981389 44638.8833 2678333 30 23 58 53
24/01/01 24/01/31 30.999213 743.981111 44638.8667 2678332 30 23 58 52
24/01/01 24/01/31 30.9992014 743.980833 44638.85 2678331 30 23 58 51
24/01/01 24/01/31 30.9991898 743.980556 44638.8333 2678330 30 23 58 50
24/01/01 24/01/31 30.9991782 743.980278 44638.8167 2678329 30 23 58 49
24/01/01 24/01/31 30.9991667 743.98 44638.8 2678328 30 23 58 48
24/01/01 24/01/31 30.9991551 743.979722 44638.7833 2678327 30 23 58 47
24/01/01 24/01/31 30.9991435 743.979444 44638.7667 2678326 30 23 58 46
24/01/01 24/01/31 30.9991319 743.979167 44638.75 2678325 30 23 58 45
24/01/01 24/01/31 30.9991204 743.978889 44638.7333 2678324 30 23 58 44
24/01/01 24/01/31 30.9991088 743.978611 44638.7167 2678323 30 23 58 43
24/01/01 24/01/31 30.9990972 743.978333 44638.7 2678322 30 23 58 42
24/01/01 24/01/31 30.9990856 743.978056 44638.6833 2678321 30 23 58 41
24/01/01 24/01/31 30.9990741 743.977778 44638.6667 2678320 30 23 58 40
24/01/01 24/01/31 30.9990625 743.9775 44638.65 2678319 30 23 58 39
24/01/01 24/01/31 30.9990509 743.977222 44638.6333 2678318 30 23 58 38
24/01/01 24/01/31 30.9990394 743.976944 44638.6167 2678317 30 23 58 37
24/01/01 24/01/31 30.9990278 743.976667 44638.6 2678316 30 23 58 36
24/01/01 24/01/31 30.9990162 743.976389 44638.5833 2678315 30 23 58 35
24/01/01 24/01/31 30.9990046 743.976111 44638.5667 2678314 30 23 58 34
24/01/01 24/01/31 30.9989931 743.975833 44638.55 2678313 30 23 58 33
24/01/01 24/01/31 30.9989815 743.975556 44638.5333 2678312 30 23 58 32
24/01/01 24/01/31 30.9989699 743.975278 44638.5167 2678311 30 23 58 31
24/01/01 24/01/31 30.9989583 743.975 44638.5 2678310 30 23 58 30
24/01/01 24/01/31 30.9989468 743.974722 44638.4833 2678309 30 23 58 29
24/01/01 24/01/31 30.9989352 743.974444 44638.4667 2678308 30 23 58 28
24/01/01 24/01/31 30.9989236 743.974167 44638.45 2678307 30 23 58 27
24/01/01 24/01/31 30.998912 743.973889 44638.4333 2678306 30 23 58 26
24/01/01 24/01/31 30.9989005 743.973611 44638.4167 2678305 30 23 58 25
24/01/01 24/01/31 30.9988889 743.973333 44638.4 2678304 30 23 58 24
24/01/01 24/01/31 30.9988773 743.973056 44638.3833 2678303 30 23 58 23
24/01/01 24/01/31 30.9988657 743.972778 44638.3667 2678302 30 23 58 22
24/01/01 24/01/31 30.9988542 743.9725 44638.35 2678301 30 23 58 21
24/01/01 24/01/31 30.9988426 743.972222 44638.3333 2678300 30 23 58 20
100 rows selected.특히 초 단위는 조회하려는 경과시간에 얼마나 소수점이 포함되어 있느냐에 따라 가끔 60이 나오기도 하여, ROUND함수와 MOD함수를 사용하였습니다. 적어도 이 예제에서는 초 단위까지 잘 나오는 것 같기는 하네요.
- TRUNC (GAP) AS 일 --단위가 일이므로 버림 처리하면 바로 확인 가능
- TRUNC (GAP * 24) - TRUNC (GAP) * 24 AS 시 --시간은 비교적 소수점 앞자리에 있기에 정확도가 높습니다.
- TRUNC (GAP * 24 * 60) - TRUNC (GAP * 24) * 60 AS 분 --잘 안 맞으면 ROUND 등을 활용해 보려고 했는데, 몇 달을 조회해 보니 잘 나오는 것 같아서 우선 TRUNC 함수로 처리합니다.
- MOD (ROUND (GAP * 24 * 60 * 60) - TRUNC (GAP * 24 * 60) * 60, 60) --초 단위가 제일 문제인데, 오차를 보정하기 위해 일부분 가공이 필요할 수 있습니다. 확실한 것은 TRUNC로 처리하면 오차가 발생하니 적합하지 않는 것으로 보여요. 😂