SYSDATE 関数を Oracle から PostgreSQL に変換する

AWS クラウドで Oracle から PostgreSQL に移行するプロセスは何段階もあって複雑になりがちです。評価ステージから切り替えステージまで、さまざまなテクノロジーとスキルが必要になります。移行プロセスの詳細については、「データベースの移行 — 開始する前に知っておくべきこと」、「移行プロセスとインフラストラクチャの考慮事項」、「ソースデータベースの考慮事項」と「PostgreSQL 環境でのターゲットデータベースの考慮事項」を参照してください。

Oracle から Amazon RDS または Amazon Aurora PostgreSQL に移行する際に最もよくある問題の 1 つが、SYSDATE 関数にまつわる問題です。SYSDATE は、アプリケーションまたはストアドプロシージャとトリガーで最も一般的に使用される日付/時刻関数です。creation_date、last_updated_date、approved_date のような列を持つテーブルは、列のデフォルト値を SYSDATE にすることにより、または、列の値を挿入または更新するトリガーを SYSDATE として定義することにより、SYSDATE 値として更新されます。

この記事は、PostgreSQL データベースの Oracle SYSDATE 関数の代替案を示します。

Oracle および PostgreSQL の日付/時刻関数を評価する際に考慮すべき 3 つの異なる側面があります。それは、次の側面です。

ステートメントレベルまたはトランザクションレベルの時刻
時刻関数におけるクライアント側設定の影響
夏時間 (DST) に関する考慮事項

PostgreSQL 日付/時刻関数の概要

PostgreSQL には、現在の日時に関連する値を返す関数がいくつか用意されています。これらの関数の一部は SQL 標準であり、その他は非 SQL 標準関数です。

サポートされる SQL 標準関数

サポートされている SQL 標準関数は次のとおりです。

CURRENT_DATE
CURRENT_TIMESTAMP/CURRENT_TIMESTAMP(precision)
CURRENT_TIME/CURRENT_TIME(precision)
LOCALTIME/LOCALTIME(precision)
LOCALTIMESTAMP/LOCALTIMESTAMP(precision)

これらの関数は、現在のトランザクションの開始時刻を返します。同じトランザクションでこれらの関数を複数回実行しても、値に変化はありません。この動作は意図的なもので、機能と見なされます。トランザクション全体で一貫したタイムスタンプが必要な場合は、これらの関数を使用できます。

CURRENT_DATE

CURRENT_DATE 関数は、現在の日付を yyyy-mm-dd 形式で表示します。次のコードを参照してください。

postgres=> select CURRENT_DATE;
 current_date
--------------
 2020-01-03
(1 row)

CURRENT_TIMESTAMP/CURRENT_TIMESTAMP(precision) and CURRENT_TIME/CURRENT_TIME(precision)

関数 CURRENT_TIMESTAMP/CURRENT_TIMESTAMP (precision) と CURRENT_TIME/CURRENT_TIME (precision) は、タイムゾーンとともに現在の日付と時刻を返します。オプションで、この関数の精度パラメータを使用できます。この精度は、小数桁 (ミリ秒) を四捨五入します。次のコードを参照してください。

postgres=> select CURRENT_TIMESTAMP;
       current_timestamp      
-------------------------------
 2020-01-03 04:38:15.662514+00
(1 row)
 
postgres=> select CURRENT_TIMESTAMP(2);
     current_timestamp    
---------------------------
 2020-01-03 04:38:19.75+00
(1 row)
 
postgres=> select CURRENT_TIME;
    current_time   
--------------------
 04:40:29.409115+00
(1 row)
 
postgres=> select CURRENT_TIME(2);
  current_time 
----------------
 04:40:38.01+00
(1 row)

LOCALTIME/LOCALTIME(precision) and LOCALTIMESTAMP/LOCALTIMESTAMP(precision)

関数 LOCALTIME/LOCALTIME (precision) と LOCALTIMESTAMP/LOCALTIMESTAMP (precision) は、タイムゾーンなしで現在の日付と時刻を返します。オプションで、この関数の精度パラメータを使用できます。この精度は、小数桁 (ミリ秒) を四捨五入します。次のコードを参照してください。

postgres=> select LOCALTIMESTAMP;
       localtimestamp      
----------------------------
 2020-01-03 04:42:39.405423
(1 row)
 
postgres=> select LOCALTIMESTAMP(2);
     localtimestamp    
------------------------
 2020-01-03 04:42:41.97
(1 row)
 
postgres=> select LOCALTIME;
    localtime   
-----------------
 04:42:24.022253
(1 row)
 
postgres=> select LOCALTIME(2);
  localtime 
-------------
 04:42:32.01
(1 row)

サポートされる非 SQL 標準関数

サポートされている非 SQL 標準関数は次のとおりです。

transaction_timestamp()
statement_timestamp()
clock_timestamp()
timeofday()
now()

PostgreSQL は、現在のステートメントの開始時刻と、関数が呼び出された時の実際の現在時刻を返す関数も用意しています。

transaction_timestamp() と statement_timestamp()

transaction_timestamp 関数は、current_timestamp と同じように動作します。ただし、名前が示すように、トランザクションの開始時刻を返し、トランザクション全体で一貫しています。statement_timestamp 関数は、トランザクションに関係なく、ステートメントの開始時刻を返します。

statement_timestamp() と transaction_timestamp() は、トランザクションの最初のコマンド中に同じ値を返しますが、後続のコマンド中には異なる値を返すかもしれません。次のコードを参照してください。

postgres=> begin;
BEGIN
postgres=> select statement_timestamp(), transaction_timestamp();
      statement_timestamp      |     transaction_timestamp    
-------------------------------+-------------------------------
 2020-01-03 04:58:39.271915+00 | 2020-01-03 04:58:37.690723+00
(1 row)
 
postgres=> select pg_sleep(5);
 pg_sleep
----------
 
(1 row)
 
 
postgres=> select statement_timestamp(), transaction_timestamp();
      statement_timestamp      |     transaction_timestamp    
-------------------------------+-------------------------------
 2020-01-03 04:58:49.770003+00 | 2020-01-03 04:58:37.690723+00
(1 row)

clock_timestamp() と statement_timestamp() の比較

clock_timestamp() 関数は実際の現在時刻を返すため、単一の SQL コマンド内でその値が変更されます。

次のコード例は、clock_timestamp は同じコマンドで異なるタイムスタンプ値を返すが、statement_timestamp() は同じ値を返すことを示しています。

postgres=> WITH time_test
postgres->      AS (SELECT Statement_timestamp())
postgres-> SELECT *,
postgres->        Pg_sleep(3) AS "<- see the difference ->",
postgres->        Statement_timestamp()
postgres-> FROM   time_test;
      statement_timestamp      | <- see the difference -> |      statement_timestamp     
-------------------------------+--------------------------+-------------------------------
 2020-01-03 05:05:08.458192+00 |                          | 2020-01-03 05:05:08.458192+00
(1 row)
 
postgres=>
postgres=> WITH time_test
postgres->      AS (SELECT clock_timestamp())
postgres-> SELECT *,
postgres->        Pg_sleep(3) AS "<- see the difference ->",
postgres->        clock_timestamp()
postgres-> FROM   time_test;
        clock_timestamp        | <- see the difference -> |        clock_timestamp       
-------------------------------+--------------------------+-------------------------------
 2020-01-03 05:05:18.040189+00 |                          | 2020-01-03 05:05:21.042861+00
(1 row)

timeofday () と clock_timestamp () の比較

関数 timeofda() と clock_timestamp() はどちらも同じように動作します。唯一の違いは、timeofday() がテキストデータタイプを返し、clock_timestamp() がタイムゾーン付きのタイムスタンプを返すことです。次のコード例の pg_typeof 列では、clock_timestamp() 関数ではタイムゾーン付きのタイムスタンプを、timeofday ではテキストを示しています。

postgres=> select clock_timestamp(), pg_typeof(clock_timestamp()), timeofday(), pg_typeof(timeofday());
        clock_timestamp        |        pg_typeof         |              timeofday              | pg_typeof
-------------------------------+--------------------------+-------------------------------------+-----------
 2020-01-03 05:28:50.203961+00 | timestamp with time zone | Fri Jan 03 05:28:50.203961 2020 UTC | text
(1 row)

now()

now() 関数は、transaction_timestamp() と同等の従来の PostgreSQL です。次のコード例では、両方の関数が同じタイムスタンプを示しています。

postgres=> begin;
sBEGIN
postgres=> select now(), transaction_timestamp();
              now              |     transaction_timestamp    
-------------------------------+-------------------------------
 2020-01-03 05:29:25.805646+00 | 2020-01-03 05:29:25.805646+00
(1 row)
 
postgres=> select pg_sleep(3);
 pg_sleep
----------
 
(1 row)
 
postgres=> select now(), transaction_timestamp();
              now              |     transaction_timestamp    
-------------------------------+-------------------------------
 2020-01-03 05:29:25.805646+00 | 2020-01-03 05:29:25.805646+00
(1 row)

使用する PostgreSQL 関数を選択する

Oracle SYSDATE 関数は、ステートメントの実行時に DB サーバーの日付/時刻を返します。したがって、長期実行トランザクションでは、複数の SYSDATE 関数がある場合、ステートメントを実行するたびに異なる時刻が返されます。

次の Oracle コード例では、SYSDATE 関数実行の 2 つの異なる値を確認できます。Oracle SYSDATE 関数は、トランザクションがいつ開始されたかに関係なく、ステートメントの開始時刻を返します。

SET SERVEROUTPUT ON ;
BEGIN
DBMS_OUTPUT.PUT_LINE('Start : ' || to_char(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));
dbms_lock.sleep(30);
DBMS_OUTPUT.PUT_LINE('End : ' || to_char(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));
END;
/
Start : 2020-01-03 06:11:06
End : 2020-01-03 06:11:36

次の PostgreSQL コード例では、単一のトランザクションを使用して、さまざまな間隔でさまざまな PostgreSQL 日付および時刻関数による時刻の戻りをキャプチャできます。次のコードは 15 秒間スリープし、前後の結果を比較します。出力は、次のように、どの関数が SYSDATE と同じ動作をするかを判断するための評価を行います。

DO
$BODY$
BEGIN
RAISE NOTICE 'clock_timestamp()       : %', clock_timestamp();
RAISE NOTICE 'statement_timestamp()   : %', statement_timestamp();
RAISE NOTICE 'now()                   : %', now();
RAISE NOTICE 'current_timestamp       : %', current_timestamp;
RAISE NOTICE 'transaction_timestamp() : %', transaction_timestamp();
RAISE NOTICE '';
RAISE NOTICE 'sleep for 15 secs and see the difference below: %', pg_sleep(15);
RAISE NOTICE '';
RAISE NOTICE 'clock_timestamp()       : %', clock_timestamp();
RAISE NOTICE 'statement_timestamp()   : %', statement_timestamp();
RAISE NOTICE 'now()                   : %', now();
RAISE NOTICE 'current_timestamp       : %', current_timestamp;
RAISE NOTICE 'transaction_timestamp() : %', transaction_timestamp();
END;
$BODY$;

次のコードは結果を示しています。

NOTICE:  clock_timestamp()       : 2020-01-03 06:20:52.3715+00
NOTICE:  statement_timestamp()   : 2020-01-03 06:20:52.371345+00
NOTICE:  now()                   : 2020-01-03 06:20:52.371345+00
NOTICE:  current_timestamp       : 2020-01-03 06:20:52.371345+00
NOTICE:  transaction_timestamp() : 2020-01-03 06:20:52.371345+00
NOTICE: 
NOTICE:  sleep for 15 secs and see the difference below:
NOTICE: 
NOTICE:  clock_timestamp()       : 2020-01-03 06:21:07.438274+00
NOTICE:  statement_timestamp()   : 2020-01-03 06:20:52.371345+00
NOTICE:  now()                   : 2020-01-03 06:20:52.371345+00
NOTICE:  current_timestamp       : 2020-01-03 06:20:52.371345+00
NOTICE:  transaction_timestamp() : 2020-01-03 06:20:52.371345+00

clock_timestamp() 関数のみが、単一のトランザクションで異なるタイミング情報を返します。したがって、PostgreSQL の Oracle SYSDATE 関数の代わりになる最も近い一致は、clock_timestamp() です。しかし、これらの値は単なるタイムスタンプなので、これだけでは不十分です。異なるタイムゾーンには同じ時点で異なるタイムスタンプ値があるため、タイムゾーンと DST も考慮する必要があります。そうしないと、予想とは異なる値が表示される場合があります。

クライアント側のタイムゾーン設定の影響

SYSDATE() や SYSTIMESTAMP() などの Oracle の日付/時刻関数は、クライアントやセッションのタイムゾーン設定に関係なく、データベースサーバーのタイムゾーンの現在の日付と時刻を返します。ただし、PostgreSQL の日付/時刻関数は、クライアントまたはセッションのタイムゾーン設定に従って時刻を返します。PostgreSQL では、タイムゾーン値のタイムスタンプは UTC に内部的に保存され、クライアントに表示されるときにタイムゾーン設定パラメータで指定されたゾーンの現地時間に変換されます。

Oracle での影響

Oracle では、クライアントレベルまたはセッションレベルのタイムゾーン設定に関して、SYSDATE 関数への影響はありません。次のコード例では、タイムゾーン設定を変更しても、SYSDATE の結果には影響しませんでした。

SQL> (select ‘dbtimezone’ as config, dbtimezone as offset from dual) union
     (select ‘sessiontimezone’ as config, sessiontimezone as offset from dual);
 
CONFIG OFFSET
--------------- ------------------------------
dbtimezone      +00:00
sessiontimezone +05:30
 
SQL> select sysdate from dual;
 
SYSDATE

03/01/2020 09:56:53
 
SQL> alter session set time_zone = ‘-08:30’;
Session altered.
 
SQL> (select ‘dbtimezone’ as config, dbtimezone as offset from dual) union
     (select ‘sessiontimezone’ as config, sessiontimezone as offset from dual);
 
CONFIG OFFSET
--------------- ------------------------------
dbtimezone       +00:00
sessiontimezone. -08:30
 
SQL> select sysdate from dual;
 
SYSDATE

03/01/2020 09:57.34

セッションのタイムゾーンを変更した後も、クライアントまたはセッションのタイムゾーンの設定に関係なく、SYSDATE はサーバーのタイムスタンプを返します。

PostgreSQL での影響

PostgreSQL では、clock_timestamp() とその他の時刻関数は、クライアントセッションのタイムゾーンの現在の日付と時刻を返します。次のコード例を参照してください。

postgres=> show timezone;
 TimeZone

 UTC
(1 row)
 
postgres=> select clock_timestamp();
        clock_timestamp       

 2020-01-03 06:25:36.165378+00
(1 row)
 
postgres=> set timezone = ‘America/New_York’;
SET
postgres=> show timezone;
     TimeZone    

 America/New_York
(1 row)
 
postgres=> select clock_timestamp();
        clock_timestamp       

 2020-01-03 01:25:49.329555-05
(1 row)
 
postgres=>

これらのタイムスタンプが異なるタイムゾーンの異なるクライアントによって返され、TIMESTAMP WITHOUT TIME ZONE 列に格納されている場合、データは誤解を招く可能性があります。

DST に関する考慮事項

タイムゾーン名とオフセットの実装が、Oracle データベースと PostgreSQL データベースで一致していません。UTC から一致するタイムゾーン名またはタイムオフセットを取得できない場合があります。さらに、PostgreSQL のタイムゾーン設定の動作は、完全なタイムゾーン名を使用するか、タイムゾーン省略形を使用するかによって異なります。PostgreSQL のタイムゾーンの省略形は、UTC からの特定のオフセットを定義しますが、完全なタイムゾーン名は、一連の DST 日付規則を意味する場合があります。

pg_timezone_names カタログテーブルには完全なタイムゾーン名の詳細が含まれており、pg_timezone_abbrevs にはタイムゾーンの省略形があります。

例: Oracle データベースの移行

次の例では、Oracle データベース (DB タイムゾーン MET) を PostgreSQL データベースに移行する必要があります。その際、アプリケーションコードは、SYSDATE を使用します。PostgreSQL にも同様の時刻関数が必要です。

Oracle では、MET タイムゾーンは DST 対応であり、UTC オフセットは +02:00:00 です。PostgreSQL にはタイムゾーン名 MET (UTS オフセット +02:00:00 および DST 対応) があり、タイムゾーン省略形 MET (UTC オフセット +01:00:00 および DST 非対応) もあります。

PostgreSQL データベースでは、セッションレベルまたは AT TIME ZONE でタイムゾーンを設定するときに、一致するフルネームと省略形がある場合、その省略形を使用します。MET を timezone DB パラメータとして設定した場合、オフセットは+01:00:00 で、DST は有効ではありません。

次のコード例には、さまざまなオフセットの一致するタイムゾーン名と省略形があります。

postgres=> select n.name, n.abbrev N_abbrev,a.abbrev, n.utc_offset N_utc_offset ,
                 a.utc_offset, n.is_dst N_is_dst, a.is_dst
  from pg_timezone_names n, pg_timezone_abbrevs a
 where n.name = a.abbrev
   and n.utc_offset <> a.utc_offset
 order by 1;
 
name | n_abbrev | abbrev | n_utc_offset | utc_offset | n_is_dst | is_dst
------+----------+--------+--------------+------------+----------+--------
CET | CEST | CET | 02:00:00 | 01:00:00 | t | f
EET | EEST | EET | 03:00:00 | 02:00:00 | t | f
MET | MEST | MET | 02:00:00 | 01:00:00 | t | f
WET | WEST | WET | 01:00:00 | 00:00:00 | t | f
(4 rows)
 
postgres=> show timezone;
 TimeZone
----------
 UTC
(1 row)
 
postgres=> select clock_timestamp();
        clock_timestamp       
-------------------------------
 2020-01-03 06:29:09.672859+00
(1 row)
 
postgres=> set session time zone 'MET';
SET
postgres=> select clock_timestamp() AT TIME ZONE 'MET';
          timezone         
----------------------------
 2020-01-03 07:29:16.261098
(1 row)

セッションのタイムゾーンを MET に設定すると、UTC と MET の時差は 1 時間になります。これは、タイムゾーンの省略形に関連するオフセットです。また、このタイムゾーンは DST に対応していません。次のコードを使用して手動で間隔を追加することで確認できます。

postgres=> select clock_timestamp() AT TIME ZONE 'UTC' + interval '02:00:00';
          ?column?         
----------------------------
 2020-01-03 08:29:19.732955
(1 row)

MET タイムゾーンの場合、正しいタイムゾーンはヨーロッパ/ベルリンであるはずです。次のコードを参照してください。

postgres=> select * from pg_timezone_names where lower(name) like '%berlin%';
 
 name          | abbrev | utc_offset | is_dst
---------------+--------+------------+--------
 Europe/Berlin | CEST   | 02:00:00   | t

DST がタイムゾーンに影響するかどうかを検証するには、次の手順を実行します。

過去または今後の DST の変更を見つけます。詳細については、「今後の夏時間の時計の変更」を参照してください。
Oracle で、DST 変更の直前と直後に UTC オフセットが変更されているかどうかを確認します。
PostgreSQL で、DST の変更の直前と直後に UTC オフセットが変更されているかどうかを確認します。

例: DST の変更

次の例では、MET (中央ヨーロッパ時間) の DST の変更は、現地時間が 1 時間戻った時、2018 年 10 月 28 日 03:00:00 AM に発生します。元の DST UTC オフセットは 02:00:00 で、新しい DST UTC オフセットは 01:00:00 であるはずです。

次の Oracle サンプルコードでは、この時計は、この DST の変更時に 1 時間前に設定されています。

SQL> ALTER SESSION SET TIME_ZONE='UTC';
Session altered.
 
-- Before DST, 28-OCT-2018 at 00:00:00 UTC equivalent to 28-OCT-2018 at 02:00:00 MET
 
SQL> select to_timestamp('2020-01-03 00:00:00','YYYY-MM-DD HH24:MI:SS') at time zone 'MET' from dual;
 
TO_TIMESTAMP('2018-10-2800:00:00','YYYY-MM-DDHH24:MI:SS')ATTIMEZONE'MET'
---------------------------------------------------------------------------
28-OCT-18 02.00.00.000000000 AM MET
 
-- Before DST, 28-OCT-2018 at 01:00:00 UTC equivalent to 28-OCT-2018 at 02:00:00 MET
 
SQL> select to_timestamp('2018-10-28 01:00:00','YYYY-MM-DD HH24:MI:SS') at time zone 'MET' from dual;
 
TO_TIMESTAMP('2018-10-2801:00:00','YYYY-MM-DDHH24:MI:SS')ATTIMEZONE'MET'
---------------------------------------------------------------------------
28-OCT-18 02.00.00.000000000 AM MET

次のコードは、PostgreSQL で同じ効果があることを示しています。

postgres=> show timezone;
TimeZone
----------
UTC
 
postgres => select '2018-10-28 00:00:00' AT TIME ZONE 'Europe/Berlin';
timezone
---------------------
2018-10-28 02:00:00
 
postgres => select '2018-10-28 01:00:00' AT TIME ZONE 'Europe/Berlin';
timezone
---------------------
2018-10-28 02:00:00

お勧め

PostgreSQL の日付と時刻関数のさまざまな側面 (クライアント側のタイムゾーン設定や DST の変更による影響など) を検討した後、次の関数を使用して、PostgreSQL の Oracle SYSDATE 関数をシミュレートできます。これはステートメントレベルのタイムスタンプを提供し、クライアント側の設定に副作用はありません。

CREATE OR REPLACE FUNCTION <<Your schema>>.sysdate()
RETURNS TIMESTAMP WITHOUT TIME ZONE
AS
$BODY$
    SELECT clock_timestamp() AT TIME ZONE '<<DB Timezone>>';
$BODY$
LANGUAGE sql;

次のコード例では、クライアント側のタイムゾーン設定を変更すると、一貫した結果が返されます。

CREATE OR REPLACE FUNCTION public.sysdate()
RETURNS TIMESTAMP WITHOUT TIME ZONE
AS
$BODY$
   SELECT clock_timestamp() AT TIME ZONE 'Europe/Berlin';
$BODY$ 
LANGUAGE sql;
 
postgres=> set session time zone 'UTC';
SET
postgres=>  select sysdate();
          sysdate          
----------------------------
 2020-01-03 07:34:54.441904
(1 row)
 
postgres=> set session time zone 'Asia/Kolkata';
SET
postgres=> select sysdate();
          sysdate          
----------------------------
 2020-01-03 07:35:02.392743
(1 row)

まとめ

この記事では、Amazon RDS と Aurora PostgreSQL で使用できる日付/時刻関連の関数について説明し (詳細については、「PostgreSQL の日付/時刻関数の概要」セクションを参照)、clock_timestamp() 関数が Oracle の SYSDATE 関数に最も近いと判断しました。「推奨事項」セクションで示したように、clock_timestamp に基づいて、Oracle データベースサーバーのタイムゾーンを使用して関数を作成できます。

著者について

Baji Shaik は、インドの AWS ProServe チームのコンサルタントです。彼のバックグラウンドは、SQL/NoSQL データベーステクノロジーの幅広い専門知識と経験に及びます。彼は、困難なビジネス要件に対応する多くのデータベースソリューションのアーキテクチャと設計を成功させてきました。レポート、ビジネスインテリジェンス、データウェアハウジング、アプリケーション、および開発サポートに PostgreSQL を使用したソリューションを提供しています。また、クラウド環境での自動化、オーケストレーション、DevOps についても十分な知識を持っています。

Sudip Acharya は、インドの AWS ProServe チームのコンサルタントです。彼は社内顧客と社外の Amazon のお客様と連携して、データベースプロジェクトに関するガイダンスと技術支援を提供し、AWS を使用する際のソリューションの価値を向上させるお手伝いをしています。

Cookie の設定を選択する

Amazon Web Services ブログ