Postgresql

如何將時間四捨五入到任意時間間隔的上倍數？

  • May 23, 2018

例子：

  • 如果目前時間是 2018-05-17 22:45:30 並且期望的時間間隔是INTERVAL '5 minute'，那麼期望的輸出是 2018-05-17 22:50:00。
  • 如果目前時間是 2018-05-17 22:45:30 並且期望的時間間隔是INTERVAL '10 minute'，那麼期望的輸出是 2018-05-17 22:50:00。
  • 如果目前時間是 2018-05-17 22:45:30 並且期望的時間間隔是INTERVAL '1 hour'，那麼期望的輸出是 2018-05-17 23:00:00。
  • 如果目前時間是 2018-05-17 22:45:30 並且期望的時間間隔是INTERVAL '1 day'，那麼期望的輸出是 2018-05-18 00:00:00。

假設數據類型**timestamp**. date或的某些細節不同timestamptz。

任何時間間隔的通用解決方案都可以根據紀元值和整數除法進行截斷。涵蓋了您的所有範例。

你的任務的特殊困難：你想要天花板，而不是地板（這更常見）。小心上下限以避免極端情況錯誤：您不想增加確切的底值。（或者我假設。）

對於內置的常見時間間隔date_trunc()（例如1 hour和1 day您的範例），您可以使用快捷方式。天數的定義取決於會話的時區設置 with timestamptz（但不是 with timestamp）。

一個自然的選擇是 with ceil()。在我的測試中有點慢，但**更乾淨。

簡短的展示

-- short demo
WITH t(ts) AS (SELECT timestamp '2018-05-17 22:45:30')  -- your input timestamp
SELECT t2.*
FROM  (SELECT *, ts - interval '1 microsecond' AS ts1 FROM t) t1 -- subtract min time interval 1 µs
     , LATERAL (
   VALUES
      ('input timestamp' , ts)
    , ('5 min' , to_timestamp(trunc(extract(epoch FROM ts1))::int / 300 * 300 + 300) AT TIME ZONE 'UTC')
    , ('10 min', to_timestamp(ceil (extract(epoch FROM ts)/ 600) * 600) AT TIME ZONE 'UTC') -- based on unaltered ts!
    , ('hour'  , date_trunc('hour', ts1) + interval '1 hour')
    , ('day'   , date_trunc('day' , ts1) + interval '1 day')
   ) t2(interval, ceil_ts);

間隔 | ceil_ts 
:-------------- | :------------------
輸入時間戳 | 2018-05-17 22:45:30
5 分鐘 | 2018-05-17 22:50:00
10 分鐘 | 2018-05-17 22:50:00
小時 | 2018-05-17 23:00:00
天 | 2018-05-18 00:00:00

‘5 min’計算的“技巧”是在截斷前減去1 µs的最小時間間隔，然後加上相應的時間間隔以有效地得到上限**。EXTRACT()返回時間戳中的秒數，一個double precision小數位到微秒的數字。我們需要trunc()，因為plain cast tointeger會四捨五入，而我們需要截斷。

這樣我們就可以避免增加恰好落在上界的時間戳。不過，它有點臟，因為最小時間間隔是目前 Postgres 版本的實現細節。不過改變的可能性很小*。*有關的：

*‘10 min’*計算更簡單ceil()，我們不需要通過減去 1 µs 來移動邊界。清潔器。但是ceil()在我的測試中稍微貴一點。

擴展測試案例

WITH t(id, ts) AS (
   VALUES
     (1, timestamp '2018-05-17 22:45:30')  -- your input timestamps here
   , (2, timestamp '2018-05-20 00:00:00')
   , (3, timestamp '2018-05-20 00:00:00.000001')
   )
SELECT *
FROM  (SELECT *, ts - interval '1 microsecond' AS ts1 FROM t) t1  -- subtract min time interval 1 µs
     , LATERAL (
   VALUES
      ('input timestamp' , ts)
    , ('5 min'  , to_timestamp(trunc(extract(epoch FROM ts1))::int / 300 * 300 + 300) AT TIME ZONE 'UTC')
    , ('10 min' , to_timestamp(ceil (extract(epoch FROM ts)/ 600) * 600) AT TIME ZONE 'UTC') -- based on unaltered ts!
    , ('hour'   , date_trunc('hour', ts1) + interval '1 hour')
    , ('day'    , date_trunc('day' , ts1) + interval '1 day')
    , ('alt_day', ts1::date + 1)
   ) t2(interval, ceil_ts)
ORDER  BY id;

編號 | ts | ts1 | 間隔 | ceil_ts 
-: | :------------------------- | :------------------------- | :-------------- | :-------------------------
 1 | 2018-05-17 22:45:30 | 2018-05-17 22:45:29.999999 | 輸入時間戳 | 2018-05-17 22:45:30 
 1 | 2018-05-17 22:45:30 | 2018-05-17 22:45:29.999999 | 5 分鐘 | 2018-05-17 22:50:00 
 1 | 2018-05-17 22:45:30 | 2018-05-17 22:45:29.999999 | 10 分鐘 | 2018-05-17 22:50:00 
 1 | 2018-05-17 22:45:30 | 2018-05-17 22:45:29.999999 | 小時 | 2018-05-17 23:00:00 
 1 | 2018-05-17 22:45:30 | 2018-05-17 22:45:29.999999 | 天 | 2018-05-18 00:00:00 
 1 | 2018-05-17 22:45:30 | 2018-05-17 22:45:29.999999 | alt_day | 2018-05-18 00:00:00 
 2 | 2018-05-20 00:00:00 | 2018-05-19 23:59:59.999999 | 輸入時間戳 | 2018-05-20 00:00:00 
 2 | 2018-05-20 00:00:00 | 2018-05-19 23:59:59.999999 | 5 分鐘 | 2018-05-20 00:00:00 
 2 | 2018-05-20 00:00:00 | 2018-05-19 23:59:59.999999 | 10 分鐘 | 2018-05-20 00:00:00 
 2 | 2018-05-20 00:00:00 | 2018-05-19 23:59:59.999999 | 小時 | 2018-05-20 00:00:00 
 2 | 2018-05-20 00:00:00 | 2018-05-19 23:59:59.999999 | 天 | 2018-05-20 00:00:00 
 2 | 2018-05-20 00:00:00 | 2018-05-19 23:59:59.999999 | alt_day | 2018-05-20 00:00:00 
 3 | 2018-05-20 00:00:00.000001 | 2018-05-20 00:00:00 | 輸入時間戳 | 2018-05-20 00:00:00.000001
 3 | 2018-05-20 00:00:00.000001 | 2018-05-20 00:00:00 | 5 分鐘 | 2018-05-20 00:05:00 
 3 | 2018-05-20 00:00:00.000001 | 2018-05-20 00:00:00 | 10 分鐘 | 2018-05-20 00:10:00 
 3 | 2018-05-20 00:00:00.000001 | 2018-05-20 00:00:00 | 小時 | 2018-05-20 01:00:00 
 3 | 2018-05-20 00:00:00.000001 | 2018-05-20 00:00:00 | 天 | 2018-05-21 00:00:00 
 3 | 2018-05-20 00:00:00.000001 | 2018-05-20 00:00:00 | alt_day | 2018-05-21 00:00:00

db<>在這裡擺弄

我為全天添加了一個替代快捷方式：ts1::date + 1. 演員表date截斷到一整天，我們可以加integer1 來增加一天。

函式包裝

您後來透露您與 合作，因此我們可以從表達式timestamptz中刪除。AT TIME ZONE

在我的測試中，聲明函式**STABLE**產生了最佳性能，因為它允許函式內聯。我本來希望IMMUTABLE是最好的，但是該聲明對內部允許內聯的內容更加挑剔。有關的：

在我的測試中快一點：

CREATE OR REPLACE FUNCTION f_tstz_interval_ceiling2(_tstz timestamptz, _int_seconds int)
 RETURNS timestamptz AS
$func$   
SELECT to_timestamp(trunc(extract(epoch FROM ($1 - interval '1 microsecond')))::int / $2 * $2 + $2)
$func$  LANGUAGE sql STABLE;

更清潔的國際海事組織：

CREATE OR REPLACE FUNCTION f_tstz_interval_ceiling1(_tstz timestamptz, _int_seconds int)
 RETURNS timestamptz AS
$func$   
SELECT to_timestamp(ceil(extract(epoch FROM $1) / $2) * $2)
$func$  LANGUAGE sql STABLE;

稱呼：

SELECT f_tstz_interval_ceiling1(my_tstz, 600);  -- 600 = seconds in 10 min

為方便起見，您可以使用替代方法重載每個函式intervalas $2：

CREATE OR REPLACE FUNCTION f_tstz_interval_ceiling1(_tstz timestamptz, _interval interval)
 RETURNS timestamptz LANGUAGE sql STABLE AS
'SELECT f_tstz_interval_ceiling1($1, extract(epoch FROM $2)::int)';

只需使用提取的秒數呼叫第一個版本。然後你也可以呼叫：

SELECT f_tstz_interval_ceiling1(my_tstz, interval '10 min');

引用自：https://dba.stackexchange.com/questions/207127

相關問答

Postgresql

在一天中的一個小時內獲得價值增量

  • January 21, 2019
檢視問答
Postgresql

在 postgres 中索引時間戳列的推薦方法？

  • June 20, 2022
檢視問答
Postgresql

如何查詢不同時區的日期？

  • May 23, 2022
檢視問答
Sql-Server

計算區間表的集差

  • January 9, 2022
檢視問答
Postgresql

在 PostgreSQL 中儲存時區 HH:MM

  • December 18, 2021
檢視問答
Postgresql

Postgres 發現一個日期範圍和其他日期範圍之間的差距

  • November 28, 2021
檢視問答