pg_nanotime 使用指南

依情境一步步說明。每個情境的完整執行結果請見 EXAMPLES.md；所有函式與語意細節請見 TECHNICAL.md。

1. 建立有奈秒時間的表

CREATE EXTENSION pg_nanotime;

CREATE TABLE measurements (
  id     bigserial PRIMARY KEY,
  ts     nanotimestamptz NOT NULL,
  value  double precision
);
INSERT INTO measurements(ts, value)
VALUES ('2026-10-23 09:00:00.123456789+08', 1.5);
  • 文字格式與 PostgreSQL 的 timestamptz 相同，小數點後最多 9 位；超過 9 位會報錯，不會偷偷捨去。
  • 輸出永遠是 ISO 格式、9 位小數，並依 session 的 TimeZone 換算。
  • 型別選擇見 README。不確定時就用 nanotimestamptz。

2. 自動填入時間：clock_ns() 還是 now_ns()？

函式 意義 何時用
clock_ns() 每次呼叫都讀取當下時鐘 想知道「這一列實際寫入的時刻」，例如收到訊息的時間
now_ns() 交易開始時的時間，同一交易內固定不變 想讓同一筆交易寫入的所有列有相同時間
statement_ns() 這一條 SQL 開始的時間 同一條 SQL 的列共用時間，但不同 SQL 不同 
CREATE TABLE inbox (
  id         bigserial PRIMARY KEY,
  received   nanotimestamptz NOT NULL DEFAULT clock_ns(),
  batch_time nanotimestamptz NOT NULL DEFAULT now_ns()
);

8 bytes 型別使用對應的 clock_ns8()、now_ns8()、statement_ns8()。

3. 嚴格遞增、不重複的時間

一般時鐘在極短時間內可能讀到相同的值，系統校時（NTP）時甚至可能倒退。需要「絕對不重複、一定越來越大」時：

函式 保證範圍 需求
clock_ns_monotonic() / clock_ns8_monotonic() 同一個連線內嚴格遞增 無
clock_ns_global() / clock_ns8_global() 所有連線之間嚴格遞增、不重複 shared_preload_libraries

使用 global 版本前，在 postgresql.conf 加入以下設定並重新啟動 PostgreSQL：

shared_preload_libraries = 'pg_nanotime'   # 已使用 TimescaleDB 時：'timescaledb, pg_nanotime'

CREATE TABLE orders (
  seq_ts nanotimestamptz8 PRIMARY KEY DEFAULT clock_ns8_global(),
  side   char(1), price numeric, qty int
);
  • 時鐘沒有前進時，函式會回傳上一個值加 1 ns，所以值會稍微領先真實時間。SELECT * FROM nanotime_clock_status(); 可以看到領先多少，以及時鐘倒退過幾次。
  • 保證只在 PostgreSQL 運行期間有效，重新啟動後從當下時鐘重新開始。
  • 實際效果：EXAMPLES §2。

4. 計算延遲：ts - ts

兩個時間相減得到 nanointerval：

SELECT recv_ts - exch_ts AS latency FROM ticks;            -- 00:00:00.000041250
SELECT (recv_ts - exch_ts)::bigint AS latency_ns FROM ticks; -- 41250

0.2.0 起可以直接對 nanointerval 用 sum()、avg()（精確累加；avg 四捨五入到奈秒，剛好一半時遠離 0）。percentile_disc(0.5) WITHIN GROUP (ORDER BY recv_ts - exch_ts) 可以直接用在 nanointerval 上（它只需要排序）；只有 percentile_cont 要內插、不支援 nanointerval，需要時先轉成 bigint 奈秒，算完再用 nanointerval_from_ns(...) 轉回來（沒有 numeric::nanointerval 轉型）。0.2.0 起也有 abs(nanointerval)，以及 generate_series(起, 迄, nanointerval)（三種時間戳型別都有）可以產生奈秒時間格線，例如找出缺漏的取樣點。比較門檻時寫 > '50 microseconds'::nanointerval。完整例子見 EXAMPLES §1。

5. 依時間分桶統計：time_bucket_ns

SELECT time_bucket_ns('1 second'::nanointerval, ts) AS bucket, count(*), avg(value)
FROM readings GROUP BY 1 ORDER BY 1;

第三個參數可以指定起點（預設 1970-01-01 UTC）。1970 年以前的時間也會正確分桶。若要依日曆單位（日、月）截斷，可以用 date_trunc('day', ts)。

6. 與 timestamptz 互轉

SELECT '2026-10-23 09:00:00+08'::timestamptz::nanotimestamptz;  -- 2026-10-23 09:00:00.000000000+08
SELECT '2026-10-23 09:00:00.123456789+08'::nanotimestamptz::timestamptz;  -- 2026-10-23 09:00:00.123456+08

轉成 timestamptz 時，微秒以下會無條件捨去（往過去取整），奈秒資訊會消失。只有在要交給只認識內建型別的工具時才轉換。

7. 從 timestamp9 或 bigint 遷移

CALL nanotime_migrate_column('events', 't', dry_run => true);  -- 只顯示要執行的 SQL
CALL nanotime_migrate_column('events', 't');                   -- 正式轉成 nanotimestamptz（預設目標，bigint 來源也一樣）
CALL nanotime_migrate_column('events', 't', 'nanotimestamptz8'); -- 或指定目標型別
  • 只執行一個 ALTER TABLE，索引會自動重建；失敗時整個回滾。
  • 如果舊的預設值無法轉換，加上 new_default => 'clock_ns()'。
  • bigint 奈秒欄位也可以直接寫 ALTER TABLE ... TYPE nanotimestamptz8 USING col::nanotimestamptz8。
  • timestamp9 超出範圍時會悄悄「繞回」成錯誤但看似合理的時間，這種資料無法可靠地自動偵測。nanotime_suspect_count(表, 欄位, 下限, 上限) 可以統計落在預期時間範圍外的列數，協助人工檢查。

8. TimescaleDB 基本用法

CREATE TABLE ticks (ts nanotimestamptz8 NOT NULL, price numeric);
SELECT create_hypertable('ticks', by_range('ts', ns('1 day')));
  • nanotimestamptz8 可以直接當 hypertable 的時間欄位。ns('1 day') 會把時間長度換成 TimescaleDB 需要的奈秒整數。
  • nanotimestamptz 需要分區函式 nanotime_part。
  • 有設 partition_func（nanotime_part、nanotime_part8）時：
    • TimescaleDB 的預設索引建在分區運算式上，直接用 ts 查詢用不到，請補 CREATE INDEX ON ticks (ts DESC);。
    • drop_chunks 的 older_than 要給 bigint，例如 '2026-10-01+00'::nanotimestamptz8::bigint。沒設分區函式時可以直接給 nanotimestamptz8。
  • 連續聚合（continuous aggregate）目前不支援這兩種型別。

詳細步驟與限制見 TIMESCALEDB.md。

9. 常見陷阱

  1. 沒標型別的 '1 day' 是 PostgreSQL 內建 interval。ts + '1 day' 是日曆上的「明天同一時刻」：遇到日光節約時間切換，實際長度會是 23 或 25 小時，而且精度只到微秒。需要固定長度或奈秒精度時，請寫 '1 day'::nanointerval。例如在 America/New_York 時區：
   SELECT '2026-10-31 12:00:00-04'::nanotimestamptz + '1 day';                -- 2026-11-01 12:00:00.000000000-05
   SELECT '2026-10-31 12:00:00-04'::nanotimestamptz + '1 day'::nanointerval;  -- 2026-11-01 11:00:00.000000000-05
  1. 不要混用 now() 與 now_ns()。now_ns() 在交易的第一個查詢開始時才讀取時鐘，會比 now() 晚幾微秒。擴充套件不會阻止混用（timestamptz 會隱式轉成 nanotimestamptz，INSERT ... VALUES (now())、clock_ns() - now() 都不會報錯），所以這是要自己遵守的慣例。兩者混在同一欄位或互相比較時，可能出現「先發生的反而比較晚」。奈秒欄位請一律使用 pg_nanotime 的函式。
  2. clock_ns() 不保證不重複。需要唯一、遞增的值時，請用 §3 的 monotonic 或 global 版本。
  3. EXTRACT(...) 和 AT TIME ZONE 語法無法擴充：請改用 extract_ns('epoch', ts) 與 timezone('Asia/Taipei', ts)。注意 extract_ns('nanosecond', ts) 和 PG 的 microsecond 欄位一樣包含整數秒：...01.000000500 會得到 1000000500；只要秒以下部分時用 % 1000000000。
  4. 8 bytes 型別只到 2262 年。超出範圍時會報錯，不會繞回。需要更大範圍時請改用 nanotimestamptz。