PostgreSQL の CURRENT_TIMESTAMP は、トランザクションを始めた時刻を返します。COMMIT するまでは、何度呼んでも同じ値です。Oracle・MySQL・Db2 では「SQL文を実行した時刻」が返るので、PostgreSQL だけ動きが違います。トランザクションの中で処理時間を測ろうとしても、CURRENT_TIMESTAMP を使うと差が0になってしまいます。そういうときは clock_timestamp() を使います、、というメモです。
時刻を返す関数の一覧
| 関数 | 返る時刻 | 返る型 |
|---|---|---|
| CURRENT_TIMESTAMP | トランザクション開始時刻 | timestamp with time zone |
| now() transaction_timestamp() | 〃 | timestamp with time zone |
| CURRENT_TIME | 〃 | time with time zone |
| CURRENT_DATE | 〃 | date |
| LOCALTIMESTAMP | 〃 | timestamp without time zone |
| LOCALTIME | 〃 | time without time zone |
| statement_timestamp() | SQL文の開始時刻 | timestamp with time zone |
| clock_timestamp() | 呼んだ瞬間の時刻（行ごとに変わる） | timestamp with time zone |
| timeofday() | 呼んだ瞬間の時刻 | text（古い関数。計算に使うなら clock_timestamp() のほうがよい） |
※ CURRENT_〜 と LOCAL〜 はカッコを付けずに書きます（current_timestamp() は構文エラー）。now() と CURRENT_TIMESTAMP の違いは「PostgreSQL now() と CURRENT_TIMESTAMP の違い（値は同じ、でも意味はある）」にまとめています。
試してみる
トランザクションの中で比べる
BEGIN;
SELECT now(), current_timestamp, current_time, localtime,
statement_timestamp(), clock_timestamp();
SELECT pg_sleep(1.5);
SELECT now(), current_timestamp, current_time, localtime,
statement_timestamp(), clock_timestamp();
COMMIT;
結果（PostgreSQL 15。見やすいように列を縦に並べています）
1回目 2回目（1.5秒後） now() 02:29:32.5652+09 02:29:32.5652+09 ← 同じ current_timestamp 02:29:32.5652+09 02:29:32.5652+09 ← 同じ current_time 02:29:32.5652+09 02:29:32.5652+09 ← 同じ localtime 02:29:32.5652 02:29:32.5652 ← 同じ statement_timestamp() 02:29:32.565876+09 02:29:34.069516+09 ← 進む clock_timestamp() 02:29:32.566808+09 02:29:34.069595+09 ← 進む
2回目の SELECT では、now()・CURRENT_TIMESTAMP・CURRENT_TIME・LOCALTIME が1回目と同じ値になります。statement_timestamp() と clock_timestamp() は1.5秒ほど進みます。
1つのSQL文の中で比べる
SELECT g, statement_timestamp(), clock_timestamp() FROM generate_series(1, 5) g;
statement_timestamp() は5行とも同じ値になり、clock_timestamp() は行ごとに少しずつ進みます。
ほかのRDBMSとの対応
| PostgreSQL | Oracle | MySQL | Db2 for i |
|---|---|---|---|
| now() （トランザクション開始時刻） | なし | なし | なし |
| statement_timestamp() | SYSTIMESTAMP SYSDATE | NOW() | CURRENT TIMESTAMP |
| clock_timestamp() | （なし） | SYSDATE() | （なし） |
ほかのDBと同じ動きにしたいなら statement_timestamp() を使います。
ストアドの中で処理時間を測る
DO $$ DECLARE t0 timestamptz := clock_timestamp(); t1 timestamptz; BEGIN PERFORM pg_sleep(1); -- 処理A t1 := clock_timestamp(); RAISE NOTICE '処理A: % ms', round(extract(epoch FROM t1 - t0) * 1000, 3); PERFORM pg_sleep(0.5); -- 処理B RAISE NOTICE '処理B: % ms', round(extract(epoch FROM clock_timestamp() - t1) * 1000, 3); END $$;
RAISE NOTICEの出力は、ロールバックしても消えません。測った結果をテーブルに INSERT すると、ロールバックのときに一緒に消えてしまいます。- サーバーログに残したいときは
RAISE LOGを使います。JDBC からはStatement.getWarnings()で受け取れます。
注意すること
1. 列の DEFAULT やトリガーの now() に気をつける
created_at timestamp DEFAULT now() -- トランザクション開始時刻が入る -- SQL文の時刻にしたいなら ALTER TABLE t ALTER COLUMN created_at SET DEFAULT statement_timestamp();
アプリのSQLを直しても、テーブル定義やトリガーの中に now() が残っていると、そこでトランザクション開始時刻が入ります。
また、DEFAULT に文字列の 'now' を書くのは間違いです。
CREATE TABLE d ( a timestamptz DEFAULT 'now', -- × CREATE TABLE した時刻で固定される b timestamptz DEFAULT now() -- ○ INSERT したトランザクションの開始時刻 );
'now' は文字列として扱われるので、テーブルを作った時点の時刻がデフォルト値として固定されてしまいます。
2. WHERE 句の clock_timestamp() ではインデックスが使われない
clock_timestamp() は VOLATILE（呼ぶたびに値が変わる）な関数なので、インデックスを使った検索の条件になりません。now() と statement_timestamp() は STABLE（1つのSQL文の中では値が変わらない）なので、インデックスが使えます。
-- インデックスが使われない WHERE updated_at > clock_timestamp() - interval '1 hour' -- インデックスが使える WHERE updated_at > statement_timestamp() - interval '1 hour'
3. clock_timestamp() の値は、重ならないとも順番どおりとも限らない
1マイクロ秒の間に何行も処理されると、同じ値になります。また NTP でサーバーの時計が合わせ直されると、時刻が戻ることがあります。一意キーや並び順には使わず、順番が必要ならシーケンスと組み合わせます。
4. JDBC の「トランザクション開始」は、最初のSQLを送ったとき
JDBC で setAutoCommit(false) を呼んだり、Spring の @Transactional に入ったりしただけでは、DBには何も送られません。PostgreSQL の JDBC ドライバーは、最初のSQLを送るときにまとめて BEGIN を送ります。ですから now() が返すのは、最初のSQLがDBに届いた時刻です。
なお autocommit=true のときは、SQL文1つが1つのトランザクションになるので、now() は実際には statement_timestamp() と同じ値になります。