ワイの中で今サマータイムがアツい彡(ﾟ)(ﾟ) サマータイムに絡んで、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セッションのデフォルトのローカル・タイムゾーン・オフセットまたは地域名を指定します。