標準はあるにはあるが癖の多いSQL 全部俺 #6 時間厳守！ Tweet

標準はあるにはあるが癖の多いSQL 全部俺w Advent Calendar 2020の6日目です。

日付、日時関連も非互換の多い部分ですね。

該当関数の挙動を見てみると違いと、以外と違いがあるので面白いですよ。日付、日時関数って多くて全てを取り上げてると大変なので、今回は、OracleのSYSDATEを取り上げて、PostgreSQLではどれ使うのがいいのかなぁ

と言うところを見ていきたいと思います。

まず、このエントリー内で利用している、関数の特性を示す単語の意味を定義しておきたいと思います。

・同一SQL文の上で何度callされても同じ日時を返す特性：文内同一
・同一SQL文の上でcallされる毎に異なる日時を返す特性：文内非同一
・同一トランザクション内で何度callしても同一日時を返す特性：トランザクション内同一
・同一トランザクション内でcallされる毎に異なる日時を返す特性：トランザクション内非同一

関数としては沢山ありすぎて個別に調査した内容を個別に書こうと思うと、アドベントカレンダーの一エントリーとして終わる気がししないのでw

軽めに書いてもこんな感じw

SYSDATEはタイムゾーンを持たないので、PostgreSQLのCURRENT_DATEかなー、と思ってしまうと大きな間違いで、もっとも挙動として近いのは、Oracleで利用していたタイムゾーンに合わせる形で利用するPostgreSQLのstatement_timestamp()と言うことになる。
有名なOrafceで提供されている oracle.sysdate()　も中を覗いてみると、statement_timestamp() が利用されている。

このエントリーの後半で挙動の確認方法と oracle.sysdate()　が　statement_timestamp()　を利用しているソースを記載しています。

Oracle Database

SYSDATE
https://docs.oracle.com/cd/E82638_01/sqlrf/SYSDATE.html#GUID-807F8FC5-D72D-4F4D-B66D-B0FE1A8FA7D2

年月日時分秒 タイムゾーンなし

特性
文内同一、トランザクション内非同一

SYSTIMESTAMP
https://docs.oracle.com/cd/E82638_01/sqlrf/SYSTIMESTAMP.html#GUID-FCED18CE-A875-4D5D-9178-3DE4FA956516

年月日時分秒.秒未満精度(9) タイムゾーンあり

特性
文内同一、ランザクション内非同一

CURRENT_DATE
https://docs.oracle.com/cd/E82638_01/sqlrf/CURRENT_DATE.html#GUID-96795097-D6F0-4288-90E7-9D7C49B4F6E5

年月日時分秒 タイムゾーンなし

特性
文内同一、トランザクション内非同一

CURRENT_TIMESTAMP
https://docs.oracle.com/cd/E82638_01/sqlrf/CURRENT_TIMESTAMP.html#GUID-CBD42B84-869D-45C7-9FFC-001DD7712097

年月日時分秒.秒未満精度(9) タイムゾーンあり

特性
文内同一、トランザクション内非同一

LOCALTIMESTAMP
https://docs.oracle.com/cd/E82638_01/sqlrf/LOCALTIMESTAMP.html#GUID-3C3D1F29-5F53-41F2-B2D6-A3767DFB22CA

年月日時分秒.秒未満精度(9) タイムゾーンなし

特性
文内同一、トランザクション内非同一

---

PostgreSQL
PostgreSQLのtimestamp型の秒未満精度は最大6桁であるため、Oracleのtimestamp型の最大精度より低いことに注意
https://www.postgresql.jp/document/12/html/functions-datetime.html

CURRENT_DATE
年月日（PostgreSQLのDATE型で返す）タイムゾーンなし

特性
文内同一、トランザクション内同一

CURRENT_TIMESTAMP
年月日時分秒.秒未満精度(6) タイムゾーンあり

特性
文内同一、トランザクション内同一

LOCALTIMESTAMP
年月日時分秒.秒未満精度(6) タイムゾーンなし

特性
文内同一、トランザクション内同一

clock_timestamp()
年月日時分秒.秒未満精度(6) タイムゾーンあり

特性
文内非同一、トランザクション内非同一

transaction_timestamp()
CURRENT_TIMESTAMPに同じ
関数名が具体的に何を返すか明確になっている点の違いのみ

特性
文内同一、トランザクション内同一

statement_timestamp()
年月日時分秒.秒未満精度(6) タイムゾーンあり

特性
文内同一、トランザクション内非同一
statement_timestamp()が、もっともOracleのSYSDATE/SYSTIMESTAMPに近い挙動を示す

now()
transaction_timestamp()の別名
利用することは推奨されていない（何を返すかわかりにくい関数名である影響と思われる)

特性
文内同一、トランザクション内同一

---

---

例

Oracle Database

トランザクション内同一性の確認（1秒間隔で3回実行）
Oracle Databaseの日付時刻関数は、トランザクション内非同一。

SYSDATE              SYSTIMESTAMP
-------------------- ----------------------------------------
2020/11/08 16:12:03  2020/11/08 16:12:03.345275 +00:00
2020/11/08 16:12:04  2020/11/08 16:12:04.397601 +00:00
2020/11/08 16:12:05  2020/11/08 16:12:05.475719 +00:00

LOCALTIMESTAMP                 CURRENT_DATE         CURRENT_TIMESTAMP
------------------------------ -------------------- ----------------------------------------
2020/11/09 01:12:03.345277     2020/11/09 01:12:03  2020/11/09 01:12:03.345277 +09:00
2020/11/09 01:12:04.397603     2020/11/09 01:12:04  2020/11/09 01:12:04.397603 +09:00
2020/11/09 01:12:05.475721     2020/11/09 01:12:05  2020/11/09 01:12:05.475721 +09:00

文内同一性の確認（最後のカラムは、1秒待機後に返すようにして実行）
(ore_sleep()と言うUDFを作成し、内部で（Oracle 18cまで）dbms_lock.sleep(1) or （Oracle 19c以降）dbms_session.sleep(1) を実行)

以下の結果から、Oracle Databaseの日付時刻関数は、すべて文内同一。

SQL> SELECT
  sysdate, sysdate, ORE_SLEEP(1), sysdate
FROM
  dual;

SYSDATE             SYSDATE             ORE_SLEEP(1)   SYSDATE
------------------- ------------------- -------------- -------------------
2020/11/21 05:49:46 2020/11/21 05:49:46              0 2020/11/21 05:49:46

SQL> r
  1  SELECT
  2    systimestamp, systimestamp, ORE_SLEEP(1), systimestamp
  3  FROM
  4*   dual

SYSTIMESTAMP                             SYSTIMESTAMP                             OREO_SLEEP(1)  SYSTIMESTAMP
---------------------------------------- ---------------------------------------- -------------- ----------------------------------------
2020/11/21/05:48:19.459180 +00:00        2020/11/21/05:48:19.459180 +00:00                     0 2020/11/21/05:48:19.459180 +00:00

SQL> r
  1  SELECT
  2    localtimestamp,localtimestamp,ORE_SLEEP(1),localtimestamp
  3  FROM
  4*   dual

LOCALTIMESTAMP                           LOCALTIMESTAMP                           ORE_SLEEP(1)   LOCALTIMESTAMP
---------------------------------------- ---------------------------------------- -------------- ----------------------------------------
2020/11/21 14:58:13.350655               2020/11/21 14:58:13.350655                            0 2020/11/21 14:58:13.350655

SQL> r
  1  SELECT
  2    current_date,current_date,ORE_SLEEP(1),current_date
  3  FROM
  4*   dual

CURRENT_DATE        CURRENT_DATE        ORE_SLEEP(1)   CURRENT_DATE
------------------- ------------------- -------------- -------------------
2020/11/21 14:59:47 2020/11/21 14:59:47              0 2020/11/21 14:59:47

SQL> r
  1  SELECT
  2    current_timestamp,current_timestamp,ORE_SLEEP(1),current_timestamp
  3  FROM
  4*   dual

CURRENT_TIMESTAMP                        CURRENT_TIMESTAMP                        ORE_SLEEP(1)   CURRENT_TIMESTAMP
---------------------------------------- ---------------------------------------- -------------- ----------------------------------------
2020/11/21/15:00:41.661495 +09:00        2020/11/21/15:00:41.661495 +09:00                     0 2020/11/21/15:00:41.661495 +09:00

---

---

PostgreSQL

トランザクション内同一性の確認（1秒間隔で3回実行）
PostgreSQLの日付時刻関数は、トランザクション内同一と非同一が混在

CURRENT_DATEは時刻を持たないためこの方法では検証不能だが、
マニュアルではトランザクション内で同一と記載されている。
clock_timestamp(),statement_timestamp()はトランザクション内非同一、それ以外は、トランザクション内同一。

 current_date |       current_timestamp       |
--------------+-------------------------------+
 2020-11-08   | 2020-11-08 16:12:05.897343+00 |
 2020-11-08   | 2020-11-08 16:12:05.897343+00 |
 2020-11-08   | 2020-11-08 16:12:05.897343+00 |

       clock_timestamp        |       localtimestamp       |
------------------------------+----------------------------+
2020-11-08 16:12:05.936468+00 | 2020-11-08 16:12:05.897343 |
2020-11-08 16:12:07.304506+00 | 2020-11-08 16:12:05.897343 |
2020-11-08 16:12:08.532788+00 | 2020-11-08 16:12:05.897343 |

             now              |      statement_timestamp      |     transaction_timestamp
------------------------------+-------------------------------+-------------------------------
2020-11-08 16:12:05.897343+00 | 2020-11-08 16:12:05.936423+00 | 2020-11-08 16:12:05.897343+00
2020-11-08 16:12:05.897343+00 | 2020-11-08 16:12:07.304408+00 | 2020-11-08 16:12:05.897343+00
2020-11-08 16:12:05.897343+00 | 2020-11-08 16:12:08.532704+00 | 2020-11-08 16:12:05.897343+00

文内同一性の確認（最後のカラムは、1秒待機後に返すようにして実行）
以下の結果から、PostgreSQL日付時刻関数は、文内同一/非同一混在。

current_dateについてはこの方法では検証できないが、ドキュメントより文内同一であると判断。

sql=> SELECT
sql->   current_date, current_date, pg_sleep(1), current_date;

 current_date | current_date | pg_sleep | current_date
--------------+--------------+----------+--------------
 2020-11-21   | 2020-11-21   |          | 2020-11-21

文内同一

sql=> SELECT
sql->   current_timestamp,current_timestamp,pg_sleep(1),current_timestamp;

       current_timestamp       |       current_timestamp       | pg_sleep |       current_timestamp
-------------------------------+-------------------------------+----------+-------------------------------
 2020-11-21 06:05:48.930432+00 | 2020-11-21 06:05:48.930432+00 |          | 2020-11-21 06:05:48.930432+00

文内非同一

sql=> SELECT
sql->   clock_timestamp(),clock_timestamp(),pg_sleep(1),clock_timestamp();

        clock_timestamp        |        clock_timestamp        | pg_sleep |        clock_timestamp
-------------------------------+-------------------------------+----------+-------------------------------
 2020-11-21 06:08:48.920466+00 | 2020-11-21 06:08:48.920466+00 |          | 2020-11-21 06:08:49.925492+00

文内同一

sql=> SELECT
sql->   localtimestamp,localtimestamp,pg_sleep(1),localtimestamp;

       localtimestamp       |       localtimestamp       | pg_sleep |       localtimestamp
----------------------------+----------------------------+----------+----------------------------
 2020-11-21 06:19:19.547055 | 2020-11-21 06:19:19.547055 |          | 2020-11-21 06:19:19.547055

文内同一

sql=> SELECT
sql->   now(),now(),pg_sleep(1),now();

              now              |              now              | pg_sleep |              now
-------------------------------+-------------------------------+----------+-------------------------------
 2020-11-21 06:20:07.457373+00 | 2020-11-21 06:20:07.457373+00 |          | 2020-11-21 06:20:07.457373+00

文内同一

sql=> SELECT
sql->   statement_timestamp(),statement_timestamp(),pg_sleep(1),statement_timestamp();

      statement_timestamp      |      statement_timestamp      | pg_sleep |      statement_timestamp
-------------------------------+-------------------------------+----------+-------------------------------
 2020-11-21 06:20:52.502137+00 | 2020-11-21 06:20:52.502137+00 |          | 2020-11-21 06:20:52.502137+00

文内同一

sql=> SELECT
sql->   transaction_timestamp(),transaction_timestamp(),pg_sleep(1),transaction_timestamp();

     transaction_timestamp     |     transaction_timestamp     | pg_sleep |     transaction_timestamp
-------------------------------+-------------------------------+----------+-------------------------------
 2020-11-21 06:21:24.562833+00 | 2020-11-21 06:21:24.562833+00 |          | 2020-11-21 06:21:24.562833+00

---

---

---

orafce によるエミュレーション関数（参考)
https://github.com/orafce/orafce/blob/master/README.asciidoc

oracle.sysdate()
PostgreSQLのstatement_timestamp()をラップしているため属性はstatement_timestamp()と同じ
sysdate関数のエミュレーションとしては最も近い属性を持っていると見られる。
OracleのSYSDATE代替関数とされている。
特性としては、問題ないと考えられ、文内同一 (OracleのSYSDATEと同じ挙動）,トランザクション内非同一（OracleのSYSDATEと同じ挙動）

実装を見てみると、

https://github.com/orafce/orafce/blob/master/orafce--3.14.sql
の関数定義を見るとC言語の関数であり、statement_timestampをと言うコメントがある.　
statement_timestamp()がOracleのSYSDATEの挙動に近いと言う理由からなのだろうと想像する。納得感あり！！！！

CREATE FUNCTION oracle.sysdate()
RETURNS oracle.date
AS 'MODULE_PATHNAME','orafce_sysdate'
LANGUAGE C STABLE STRICT;
COMMENT ON FUNCTION oracle.sysdate() IS 'Ruturns statement timestamp at server time zone';

さらに、orafceのoracle.sysdate()のC言語のソースを追ってみる。。。
datefce.cのorafce_sysdateが本体であることがわかる
https://github.com/orafce/orafce/blob/master/builtins.h

extern PGDLLEXPORT Datum orafce_sysdate(PG_FUNCTION_ARGS);

https://github.com/orafce/orafce/blob/master/datefce.c

/********************************************************************
*
* ora_sysdate - sysdate
*
* Syntax:
*
* timestamp sysdate()
*
* Purpose:
*
* Returns statement_timestamp in server time zone
*   Note - server time zone doesn't exists on PostgreSQL - emulated
*   by orafce_timezone
*
********************************************************************/

Datum
orafce_sysdate(PG_FUNCTION_ARGS)
{
	Datum sysdate;
	Datum sysdate_scaled;

	sysdate = DirectFunctionCall2(timestamptz_zone,
					CStringGetTextDatum(orafce_timezone),
					TimestampTzGetDatum(GetCurrentStatementStartTimestamp()));

	/* necessary to cast to timestamp(0) to emulate Oracle's date */
	sysdate_scaled = DirectFunctionCall2(timestamp_scale,
						sysdate,
						Int32GetDatum(0));

	PG_RETURN_DATUM(sysdate_scaled);
}

および、GetCurrentStatementStartTimestamp()よりstatement_timestamp()であることがわかる
https://docs.huihoo.com/doxygen/postgresql/backend_2utils_2adt_2timestamp_8c_source.html#l01239

01239 statement_timestamp(PG_FUNCTION_ARGS)
01240 {
01241     PG_RETURN_TIMESTAMPTZ(GetCurrentStatementStartTimestamp());
01242 }

---

---

---

Oracleの検証に利用したコード、昔は、DBMS_LOCK.SLEEP()ってなんでDBMS_LOCKパッケージにあるの？　と言う感じだったが、最近はわかりやすいDBMS_SESSIONパッケージが推奨で、DBMS_LOCK.SLEEP()は非推奨なのでご注意を

ore_seep(seonds) - UDF sample
CREATE OR REPLACE FUNCTION ore_sleep
(
 seconds NUMBER
)
RETURN NUMBER
AS
BEGIN
 $IF DBMS_DB_VERSION.VER_LE_12 $THEN
   DBMS_LOCK.SLEEP(seconds);
 $ELSE
   DBMS_SESSION.SLEEP(seconds);
 $END
 RETURN 0;
END;
/

---

実行計画は、SQL文のレントゲン写真だ！　Oracle Database編 (全部俺） Advent Calendar 2020

---

・標準はあるにはあるが癖の多いSQL 全部俺　#1 Pagination
・標準はあるにはあるが癖の多いSQL 全部俺　#2 関数名は同じでも引数が逆の罠！
・標準はあるにはあるが癖の多いSQL 全部俺　#3 データ型確認したい時あるんです
・標準はあるにはあるが癖の多いSQL 全部俺　#4 リテラル値での除算の内部精度も違うのよ!/a>
・標準はあるにはあるが癖の多いSQL 全部俺　#5 和暦変換機能ある方が少数派