LOCALTIMESTAMP, CURRENT_TIMESTAMP, SYSTIMESTAMP の各組み込み関数の挙動を検証してみる。(Oracle Database)

ワイの中で今サマータイムがアツい彡(ﾟ)(ﾟ) サマータイムに絡んで、Oracle Database の組み込み関数の LOCALTIMESTAMP, CURRENT_TIMESTAMP, SYSTIMESTAMP の挙動を検証してみる。

1. Case1. OSタイムゾーンが日本(JST)の状態で検証

まずOSのタイムゾーンが日本(JST)の状態で検証彡(ﾟ)(ﾟ)

date
Thu Aug 16 14:40:09 JST 2018

-- Initialize
SET LINESIZE 300;
SET PAGESIZE 100;
COLUMN SESSIONTIMEZONE   FORMAT A15;
COLUMN LOCALTIMESTAMP    FORMAT A30;
COLUMN CURRENT_TIMESTAMP FORMAT A42;
COLUMN SYSTIMESTAMP      FORMAT A42;
ALTER SESSION SET NLS_TIMESTAMP_FORMAT    = 'YYYY/MM/DD HH24:MI:SS.FF';
ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT = 'YYYY/MM/DD HH24:MI:SS.FF TZR TZD';

ALTER SESSION SET TIME_ZONE = 'UCT';
SELECT SESSIONTIMEZONE, LOCALTIMESTAMP, CURRENT_TIMESTAMP, SYSTIMESTAMP FROM DUAL;

ALTER SESSION SET TIME_ZONE = 'Asia/Tokyo';
SELECT SESSIONTIMEZONE, LOCALTIMESTAMP, CURRENT_TIMESTAMP, SYSTIMESTAMP FROM DUAL;

ALTER SESSION SET TIME_ZONE = 'US/Pacific';
SELECT SESSIONTIMEZONE, LOCALTIMESTAMP, CURRENT_TIMESTAMP, SYSTIMESTAMP FROM DUAL;

SESSIONTIMEZONE LOCALTIMESTAMP                 CURRENT_TIMESTAMP                          SYSTIMESTAMP
--------------- ------------------------------ ------------------------------------------ ------------------------------------------
UCT             2018/08/16 05:43:35.927468     2018/08/16 05:43:35.927468 UCT UCT         2018/08/16 14:43:35.927370 +09:00

SESSIONTIMEZONE LOCALTIMESTAMP                 CURRENT_TIMESTAMP                          SYSTIMESTAMP
--------------- ------------------------------ ------------------------------------------ ------------------------------------------
Asia/Tokyo      2018/08/16 14:43:35.973241     2018/08/16 14:43:35.973241 ASIA/TOKYO JST  2018/08/16 14:43:35.973227 +09:00

SESSIONTIMEZONE LOCALTIMESTAMP                 CURRENT_TIMESTAMP                          SYSTIMESTAMP
--------------- ------------------------------ ------------------------------------------ ------------------------------------------
US/Pacific      2018/08/15 22:43:36.026801     2018/08/15 22:43:36.026801 US/PACIFIC PDT  2018/08/16 14:43:36.026789 +09:00

LOCALTIMESTAMP と CURRENT_TIMESTAMP は SESSION の TIME_ZONE の影響を受けてるけど、SYSTIMESTAMPは無影響やね彡(ﾟ)(ﾟ)。

2. Case2. OSタイムゾーンをロサンゼルスに変更して検証

次にOSタイムゾーンをロサンゼルスに変更して検証してみる。
ロスは2018/8/16現在、太平洋夏時間(PDT)やね彡(ﾟ)(ﾟ)

cp -p /usr/share/zoneinfo/America/Los_Angeles /etc/localtime
date
Thu Aug 16 23:04:25 PDT 2018

-- Initialize
SET LINESIZE 300;
SET PAGESIZE 100;
COLUMN SESSIONTIMEZONE   FORMAT A15;
COLUMN LOCALTIMESTAMP    FORMAT A30;
COLUMN CURRENT_TIMESTAMP FORMAT A42;
COLUMN SYSTIMESTAMP      FORMAT A42;
ALTER SESSION SET NLS_TIMESTAMP_FORMAT    = 'YYYY/MM/DD HH24:MI:SS.FF';
ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT = 'YYYY/MM/DD HH24:MI:SS.FF TZR TZD';

ALTER SESSION SET TIME_ZONE = 'UCT';
SELECT SESSIONTIMEZONE, LOCALTIMESTAMP, CURRENT_TIMESTAMP, SYSTIMESTAMP FROM DUAL;

ALTER SESSION SET TIME_ZONE = 'Asia/Tokyo';
SELECT SESSIONTIMEZONE, LOCALTIMESTAMP, CURRENT_TIMESTAMP, SYSTIMESTAMP FROM DUAL;

ALTER SESSION SET TIME_ZONE = 'US/Pacific';
SELECT SESSIONTIMEZONE, LOCALTIMESTAMP, CURRENT_TIMESTAMP, SYSTIMESTAMP FROM DUAL;

SESSIONTIMEZONE LOCALTIMESTAMP                 CURRENT_TIMESTAMP                          SYSTIMESTAMP
--------------- ------------------------------ ------------------------------------------ ------------------------------------------
UCT             2018/08/16 05:45:19.484222     2018/08/16 05:45:19.484222 UCT UCT         2018/08/15 22:45:19.484217 -07:00

SESSIONTIMEZONE LOCALTIMESTAMP                 CURRENT_TIMESTAMP                          SYSTIMESTAMP
--------------- ------------------------------ ------------------------------------------ ------------------------------------------
Asia/Tokyo      2018/08/16 14:45:19.526919     2018/08/16 14:45:19.526919 ASIA/TOKYO JST  2018/08/15 22:45:19.526914 -07:00

SESSIONTIMEZONE LOCALTIMESTAMP                 CURRENT_TIMESTAMP                          SYSTIMESTAMP
--------------- ------------------------------ ------------------------------------------ ------------------------------------------
US/Pacific      2018/08/15 22:45:19.572191     2018/08/15 22:45:19.572191 US/PACIFIC PDT  2018/08/15 22:45:19.572185 -07:00

LOCALTIMESTAMP と CURRENT_TIMESTAMP は Case 1 と同じ動作やけど、SYSTIMESTAMPの値が変わってるやね彡(ﾟ)(ﾟ)

3. まとめ

上記の挙動をまとめると、以下の通りとなります。

組み込み関数	データ型	DB SESSIONのTIME_ZONE値	OSのtimezone
LOCALTIMESTAMP	TIMESTAMP	影響する	影響しない
CURRENT_TIMESTAMP	TIMESTAMP WITH TIME ZONE	影響する	影響しない
SYSTIMESTAMP	TIMESTAMP WITH TIME ZONE	影響しない	影響する

「OSのtimezone」と「DB SESSIONのTIME_ZONE値」を一致させられる環境なら、
各組み込み関数で取れてくる値に違いは無いので影響はほぼ無いと思うんやけど、
それが異なるケースではその違いを意識して実装する必要が有りそうやね彡(ﾟ)(ﾟ)

SYSTIMESTAMPの返り値をAT TIME ZONE書式で変換する、SESSIONTIMEZONEを任意のTIME_ZONEに変更してCURRENT_TIMESTAMPを使う、など

SELECT SYSTIMESTAMP, SYSTIMESTAMP AT TIME ZONE 'Japan' FROM DUAL;

SYSTIMESTAMP                               SYSTIMESTAMPATTIMEZONE'JAPAN'
------------------------------------------ ---------------------------------------------------------------------------
2018/08/16 01:14:47.217209 -07:00          2018/08/16 17:14:47.217209 JAPAN JST

ALTER SESSION SET TIME_ZONE = 'Japan';
SELECT SESSIONTIMEZONE, CURRENT_TIMESTAMP FROM DUAL;

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

SESSIONTIMEZONE CURRENT_TIMESTAMP
--------------- ------------------------------------------
Japan           2018/08/16 17:15:18.966717 JAPAN JST

4. マニュアル

マニュアルも参考や！彡(ﾟ)(ﾟ)

LOCALTIMESTAMP
https://docs.oracle.com/cd/E96517_01/sqlrf/LOCALTIMESTAMP.html#GUID-3C3D1F29-5F53-41F2-B2D6-A3767DFB22CA
LOCALTIMESTAMPは、セッションのタイムゾーンの現在の日付および時刻をTIMESTAMPデータ型の値で戻します。

CURRENT_TIMESTAMP
https://docs.oracle.com/cd/E96517_01/sqlrf/CURRENT_TIMESTAMP.html#GUID-CBD42B84-869D-45C7-9FFC-001DD7712097
CURRENT_TIMESTAMPは、セッション・タイムゾーンの現在の日付および時刻をTIMESTAMP WITH TIME ZONEデータ型の値で戻します。

SYSTIMESTAMP
https://docs.oracle.com/cd/E96517_01/sqlrf/SYSTIMESTAMP.html#GUID-FCED18CE-A875-4D5D-9178-3DE4FA956516
SYSTIMESTAMPは、データベースが存在するシステムの、秒の小数部とタイムゾーンを含む日付を戻します。戻り値の型は、TIMESTAMP WITH TIME ZONEです。

ALTER SESSION
https://docs.oracle.com/cd/E96517_01/sqlrf/ALTER-SESSION.html#GUID-27186B28-7EFC-4998-B1ED-2B905CC0211B
TIME_ZONEパラメータには、現行のSQLセッションのデフォルトのローカル・タイムゾーン・オフセットまたは地域名を指定します。