SYSTIMESTAMP/CURRENT_TIMESTAMP/LOCALTIMESTAMPのメモ

oracle db 
Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production
に接続されました。
SQL> select systimestamp, current_timestamp, localtimestamp from dual;

SYSTIMESTAMP                        CURRENT_TIMESTAMP                   LOCALTIMESTAMP
----------------------------------- ----------------------------------- ------------------------------
13-07-28 16:58:07.458000 +09:00     13-07-28 16:58:07.458000 +09:00     13-07-28 16:58:07.458000

SQL> select
  2    extract(hour from systimestamp) as systimestamp_h
  3    , extract(hour from current_timestamp) as current_timestamp_h
  4    , extract(hour from localtimestamp) as localtimestamp_h
  5  from dual;

SYSTIMESTAMP_H CURRENT_TIMESTAMP_H LOCALTIMESTAMP_H
-------------- ------------------- ----------------
             7                   7               16

SQL> alter session set time_zone = 'UTC';

セッションが変更されました。

SQL> select systimestamp, current_timestamp, localtimestamp from dual;

SYSTIMESTAMP                        CURRENT_TIMESTAMP                   LOCALTIMESTAMP
----------------------------------- ----------------------------------- ------------------------------
13-07-28 16:59:30.740000 +09:00     13-07-28 07:59:30.740000 UTC        13-07-28 07:59:30.740000

SQL> select
  2    extract(hour from systimestamp) as systimestamp_h
  3    , extract(hour from current_timestamp) as current_timestamp_h
  4    , extract(hour from localtimestamp) as localtimestamp_h
  5  from dual;

SYSTIMESTAMP_H CURRENT_TIMESTAMP_H LOCALTIMESTAMP_H
-------------- ------------------- ----------------
             7                   7                7

EXTRACTする時に注意なんですね。localtimestampを使ってEXTRACTしたい。

参考

EXTRACT - オラクル・Oracle SQL 関数リファレンス