Oracle® 非公式 SQL, PL/SQL & DBA's リファレンス

指定した日時フィールドの値を取り出す

EXTRACT ( element FROM datetime )
return [ number | string ]

element	抽出する日付の要素
datetime	日付式、または、期間式

• 指定した日時要素、または、タイムゾーン名称

• SQL および PL/SQL の両方で使用可能。

抽出する日付の要素

EXTRACT 関数の内容

指定した日時フィールドの値を取り出す。
DATE 値からは YEAR、MONTH および DAY のみを抽出でき、TIMESTAMP 値からは SECOND まで取得が可能。(TIMEZONE_HOURとTIMEZONE_MINUTE は TIMESTAMP WITH TIME ZONE 値からのみ)

DATE 型から HOUR を抽出すると ORA-30076: 抽出ソースの抽出フィールドが無効です が発生する。
ANSI では DATE 値は日にちまでと定義されているために発生する現象である。(ANSI-SQL92 のデータタイプは DATE, TIME, TIMESTAMP)

EXTRACT 使用例

SQL> select dt, EXTRACT(YEAR FROM dt), EXTRACT(MONTH FROM dt), EXTRACT(DAY FROM dt)
  2   from date_sample;
 
DT                  EXTRACT(YEARFROMDT) EXTRACT(MONTHFROMDT) EXTRACT(DAYFROMDT)
------------------- ------------------- -------------------- ------------------
2000-08-07 04:27:56                2000                    8                  7
2001-01-28 05:12:47                2001                    1                 28
2002-12-01 08:01:10                2002                   12                  1
2003-02-14 11:45:56                2003                    2                 14

抽出フィールドが 時(HOUR)以下の場合、FROM には TIMESTAMP 型の入力が必要

SQL> select ts, EXTRACT(HOUR FROM ts), EXTRACT(MINUTE FROM ts), EXTRACT(SECOND FROM ts)
  2   from time_sample;
 
TS                   EXTRACT(HOURFROMTS) EXTRACT(MINUTEFROMTS) EXTRACT(SECONDFROMTS)
-------------------- ------------------- --------------------- ---------------------
2000-08-07 04:27:56                    4                    27                    56
2001-01-28 05:12:47                    5                    12                    47
2002-12-01 08:01:10                    8                     1                    10
2003-02-14 11:45:56                   11                    45                    56
2004-10-28 04:50:39                    4                    50                    39

TIMEZONE_REGION はタイムゾーン名、TIMEZONE_ABBR はその略称となる。デフォルトで日本では Asia/Tokyo と JST が期待されたのであるが UNKNOWN/UNK になる。（+09：00 のエリアが複数国あるからかもしれない）
デフォルトのタイムゾーン名は ORA_SDTZ 環境変数 で設定することができる。

SQL> select current_timestamp, EXTRACT(TIMEZONE_REGION FROM current_timestamp) region,
  2                            EXTRACT(TIMEZONE_ABBR FROM current_timestamp) abbr
  3  from dual;
 
CURRENT_TIMESTAMP                    REGION     ABBR
------------------------------------ ---------- ----------
07-07-07 12:41:21.406000 +09:00      UNKNOWN    UNK

SQL> alter session set time_zone='Japan';
SQL> select current_timestamp, EXTRACT(TIMEZONE_REGION FROM current_timestamp) region,
  2                            EXTRACT(TIMEZONE_ABBR FROM current_timestamp) abbr
  3  from dual;
 
CURRENT_TIMESTAMP                    REGION     ABBR
------------------------------------ ---------- ----------
07-07-07 12:41:21.421000 JAPAN       Japan      JST

SQL> alter session set time_zone='Asia/Tokyo';
SQL> select current_timestamp, EXTRACT(TIMEZONE_REGION FROM current_timestamp) region,
  2                            EXTRACT(TIMEZONE_ABBR FROM current_timestamp) abbr
  3  from dual;
 
CURRENT_TIMESTAMP                    REGION     ABBR
------------------------------------ ---------- ----------
07-07-07 12:41:21.437000 ASIA/TOKYO  Asia/Tokyo JST

日本とその周辺のタイムゾーンは +09:00

SQL> select ts, EXTRACT(TIMEZONE_HOUR FROM ts), EXTRACT(TIMEZONE_MINUTE FROM ts)
  2   from time_sample;
 
TS                   EXTRACT(TIMEZONE_HOURFROMTS) EXTRACT(TIMEZONE_MINUTEFROMTS)
-------------------- ---------------------------- ------------------------------
2002-12-01 08:01:10                             9                              0
2000-08-07 04:27:56                             9                              0
2001-01-28 05:12:47                             9                              0
2003-02-14 11:45:56                             9                              0
2004-10-28 04:50:39                             9                              0

EXTRACT と SYSTIMESTAMP,CURRENT_TIMESTAMP に関する豆知識

EXTRACT 関数は 内部形式 における指定時間フィールドを取り出す。
SYSTIMESTAMP,CURRENT_TIMESTAMP 関数は厳密には TIMESTAMP 型ではなくタイムゾーン付きの TIMESTAMP 型 である。 内部では UTC+0 で正規化されて保持されているため日本時間(UTC+9)で 9:00AM のときには

SQL> select extract(hour from systimestamp),
  2         extract(hour from localtimestamp) from dual;
 
EXTRACT(HOURFROMSYSTIMESTAMP) EXTRACT(HOURFROMLOCALTIMESTAMP)
----------------------------- -------------------------------
                            0                               9

のように「9時」を期待しているところを「0時」という標準時における結果を戻す。
この例のように EXTRACT と SYSTIMESTAMP, CURRENT_TIMESTAMP の組み合わせは初見では首をかしげる結果になるので注意が必要。もちろん時差があるので異なる日付になることもある。

もし SYSTIMESTAMP や CURRENT_TIMESTAMP を使って今の「〜時」を取得したいならば TIMESTAMP 型にキャストすると満足できる結果になる。

SQL> select extract(hour from cast(systimestamp as timestamp)) "FROM_SYSTIMESTAMP",
  2         extract(hour from localtimestamp) from dual;
 
FROM_SYSTIMESTAMP EXTRACT(HOURFROMLOCALTIMESTAMP)
----------------- -------------------------------
                9                               9

---

日付の操作に関連する内容

• データ型一覧
• 別のタイムゾーン日付をセッションタイムゾーン日付に変換
• ORA_TZFILE：タイムゾーン定義ファイルの場所を示す環境変数
   （$ORACLE_HOME/oracore/zoneinfo/timezone.dat）