【Oracle】INTERVAL データ型完全ガイド｜INTERVAL YEAR TO MONTH・INTERVAL DAY TO SECOND による期間計算まで解説

-- INTERVAL リテラルの書き方: INTERVAL '年-月' YEAR TO MONTH
SELECT INTERVAL '3-6' YEAR TO MONTH FROM DUAL;   -- 3年6ヶ月
SELECT INTERVAL '0-9' YEAR TO MONTH FROM DUAL;   -- 9ヶ月
SELECT INTERVAL '10' YEAR          FROM DUAL;    -- 10年（YEAR のみ）
SELECT INTERVAL '6'  MONTH         FROM DUAL;    -- 6ヶ月（MONTH のみ）

-- 列定義（契約期間テーブルの例）
CREATE TABLE contracts (
    contract_id     NUMBER PRIMARY KEY,
    start_date      DATE,
    duration        INTERVAL YEAR(3) TO MONTH,   -- 最大 999年
    contract_name   VARCHAR2(100)
);

INSERT INTO contracts VALUES (1, DATE '2024-04-01', INTERVAL '2-6' YEAR TO MONTH, '2年6ヶ月契約');
INSERT INTO contracts VALUES (2, DATE '2024-04-01', INTERVAL '1'   YEAR,          '1年契約');
INSERT INTO contracts VALUES (3, DATE '2024-04-01', INTERVAL '6'   MONTH,         '6ヶ月契約');

-- DATE + INTERVAL YEAR TO MONTH = DATE
SELECT
    contract_name,
    start_date,
    duration,
    start_date + duration AS end_date   -- 契約終了日
FROM contracts;

-- NUMTOYMINTERVAL で数値を INTERVAL に変換する
SELECT NUMTOYMINTERVAL(18, 'MONTH') FROM DUAL;  -- 18ヶ月 → INTERVAL '1-6'
SELECT NUMTOYMINTERVAL(2,  'YEAR')  FROM DUAL;  -- 2年 → INTERVAL '2-0'

-- ADD_MONTHS との比較: INTERVAL は日付の意味を持つ
SELECT SYSDATE + NUMTOYMINTERVAL(3, 'MONTH') FROM DUAL;   -- 3ヶ月後
SELECT ADD_MONTHS(SYSDATE, 3)                 FROM DUAL;   -- 同等

-- INTERVAL リテラルの書き方: INTERVAL '日 時:分:秒.小数秒' DAY TO SECOND
SELECT INTERVAL '2 10:30:00' DAY TO SECOND   FROM DUAL;   -- 2日10時間30分
SELECT INTERVAL '0 08:00:00' DAY TO SECOND   FROM DUAL;   -- 8時間
SELECT INTERVAL '0 00:01:30' DAY TO SECOND   FROM DUAL;   -- 1分30秒
SELECT INTERVAL '1.5'        DAY             FROM DUAL;   -- 1.5日（= 36時間）
SELECT INTERVAL '120'        MINUTE          FROM DUAL;   -- 120分
SELECT INTERVAL '3600'       SECOND          FROM DUAL;   -- 3600秒

-- DAY(3) TO SECOND(6): 最大 999日・マイクロ秒精度（デフォルト DAY(2) TO SECOND(6)）
CREATE TABLE task_logs (
    task_id       NUMBER PRIMARY KEY,
    task_name     VARCHAR2(100),
    start_ts      TIMESTAMP,
    finish_ts     TIMESTAMP,
    elapsed_time  INTERVAL DAY(3) TO SECOND(3)   -- 最大 999日・ミリ秒精度
);

-- TIMESTAMP 同士の引き算は INTERVAL DAY TO SECOND になる
INSERT INTO task_logs (task_id, task_name, start_ts, finish_ts, elapsed_time)
VALUES (
    1, 'nightly_batch',
    TIMESTAMP '2024-04-09 02:00:00',
    TIMESTAMP '2024-04-09 03:45:30',
    TIMESTAMP '2024-04-09 03:45:30' - TIMESTAMP '2024-04-09 02:00:00'
    -- → INTERVAL '0 01:45:30.000000' DAY TO SECOND
);

-- NUMTODSINTERVAL で数値を変換する
SELECT NUMTODSINTERVAL(90,   'MINUTE') FROM DUAL;  -- 90分 → INTERVAL '0 01:30:00'
SELECT NUMTODSINTERVAL(3600, 'SECOND') FROM DUAL;  -- 3600秒 → INTERVAL '0 01:00:00'
SELECT NUMTODSINTERVAL(1.5,  'DAY')   FROM DUAL;  -- 1.5日 → INTERVAL '1 12:00:00'

-- INTERVAL 同士の加算・減算
SELECT INTERVAL '1' HOUR + INTERVAL '30' MINUTE FROM DUAL;  -- 1時間30分
SELECT INTERVAL '2'  DAY - INTERVAL '12' HOUR   FROM DUAL;  -- 1日12時間
-- 注意: YEAR TO MONTH と DAY TO SECOND は直接加算できない（型が異なる）

-- スカラー倍: INTERVAL * 数値
SELECT INTERVAL '1' HOUR * 8 FROM DUAL;     -- 8時間
SELECT INTERVAL '30' DAY / 2 FROM DUAL;     -- 15日

-- EXTRACT で INTERVAL から特定の要素を取り出す
SELECT EXTRACT(HOUR   FROM INTERVAL '3 10:30:45' DAY TO SECOND) FROM DUAL;  -- 10
SELECT EXTRACT(MINUTE FROM INTERVAL '3 10:30:45' DAY TO SECOND) FROM DUAL;  -- 30
SELECT EXTRACT(SECOND FROM INTERVAL '3 10:30:45' DAY TO SECOND) FROM DUAL;  -- 45
SELECT EXTRACT(DAY    FROM INTERVAL '3 10:30:45' DAY TO SECOND) FROM DUAL;  -- 3
-- 注意: EXTRACT(DAY FROM INTERVAL '3 10:30:45') = 3（時・分・秒は別に EXTRACT する）

SELECT EXTRACT(YEAR  FROM INTERVAL '2-6' YEAR TO MONTH) FROM DUAL;  -- 2
SELECT EXTRACT(MONTH FROM INTERVAL '2-6' YEAR TO MONTH) FROM DUAL;  -- 6

-- INTERVAL を秒数に変換する（DAY TO SECOND の場合）
SELECT
    elapsed_time,
    EXTRACT(DAY    FROM elapsed_time) * 86400 +
    EXTRACT(HOUR   FROM elapsed_time) * 3600  +
    EXTRACT(MINUTE FROM elapsed_time) * 60    +
    EXTRACT(SECOND FROM elapsed_time)            AS total_seconds
FROM task_logs;
-- INTERVAL を数値（秒数）に変換したい場合はこの方法を使う
-- （直接 TO_NUMBER(elapsed_time) はできない）

-- SLA: 注文から出荷まで 24時間以内かどうかを確認する
SELECT
    order_id,
    ordered_at,
    shipped_at,
    shipped_at - ordered_at                               AS actual_time,
    INTERVAL '24' HOUR                                   AS sla_limit,
    CASE WHEN shipped_at - ordered_at > INTERVAL '24' HOUR
         THEN '遅延'
         ELSE 'OK'
    END AS sla_status
FROM   shipments
WHERE  shipped_at IS NOT NULL;

-- 有効期限チェック: 契約終了まで 30日以内のレコードを抽出する
SELECT
    contract_id,
    contract_name,
    start_date,
    start_date + duration AS end_date,
    (start_date + duration) - SYSDATE AS days_remaining
FROM   contracts
WHERE  (start_date + duration) - SYSDATE <= INTERVAL '30' DAY
  AND  (start_date + duration) >= SYSDATE;

-- 業務時間内の経過時間計算（平日9時〜18時の合計稼働時間）
-- INTERVAL を合計する集計: SUM は INTERVAL に使えないため、秒数に変換して集計する
SELECT
    task_name,
    SUM(
        EXTRACT(DAY    FROM elapsed_time) * 86400 +
        EXTRACT(HOUR   FROM elapsed_time) * 3600  +
        EXTRACT(MINUTE FROM elapsed_time) * 60    +
        EXTRACT(SECOND FROM elapsed_time)
    ) / 3600 AS total_hours    -- 合計秒数 → 時間に変換
FROM   task_logs
GROUP  BY task_name;
-- SUM(INTERVAL) は直接使えない → 秒数に変換してから集計する