關於在PostgreSQL中使用extract函式以及epoch

明月镇魂發表於2024-04-25
SQL函式

起因是看不懂以下SQL

 1 SELECT
 2             province_org_id,
 3             1.0 AS bl,
 4             outage_respon_code AS tdzrm,
 5             round(
 6                 EXTRACT (
 7                     epoch 
 8                 FROM
 9                     (
10                     CASE
11                             
12                             WHEN to_timestamp ( '', 'YYYY-MM-DD HH24:MI:SS' ) > outage_e_time THEN
13                             outage_e_time ELSE to_timestamp ( '', 'YYYY-MM-DD HH24:MI:SS' ) 
14                         END -
15                     CASE
16                             
17                             WHEN outage_s_time > to_timestamp ( '', 'YYYY-MM-DD HH24:MI:SS' ) THEN
18                             outage_s_time ELSE to_timestamp ( '', 'YYYY-MM-DD HH24:MI:SS' ) 
19                         END 
20                         ) 
21                     ) :: NUMERIC 
22                 ) / 60 / 60 AS CXSJ 
23             FROM
24                 pdr_outage_mvcons 
--空字串是傳參，我給轉成空字串了，要不不好看

extract （field from source）
extract函式是從日期或者時間數值裡面抽取子域，比如年、月、日等。source必須是timestamp、time、interval型別的值表示式。field是一個識別符號或字串，其具有很多的列舉值。

新紀元時間 Epoch也是field的一種， 是以 1970-01-01 00:00:00 UTC 為標準的時間，將目標時間與 1970-01-01 00:00:00
時間的差值以秒來計算 ，單位是秒，可以是負值; 有些應用會將時間儲存成epoch 時間形式，以提高讀取效率。官網解釋為：

對於日期和時間戳型別的值，會獲取到從1970-01-01 00:00:00 UTC這個Linux紀元年的開始時間到給定的日期或者時間戳引數的時間之間相隔的秒數。

而對於interval這種時間間隔型別，這會獲取到這個時間間隔對應的秒數目。

所以以上SQL的含義不言而喻，就是想獲取這段時間來作為停電的持續時間；但是不明白為什麼加了一個轉成numric型別的函式

以下是幾個用法：

保留原來的毫秒值



select extract(epoch from '03:21:06.678'::time);
這個extract(epoch from )函式得到的是時間是秒單位，如果需要毫秒值就直接乘以1000：
select extract(epoch from now())*1000;




去掉原來的毫秒值




1 向下取整函式floor()
2 select floor(extract(epoch from '03:21:06.678'::time));
3 向上取整函式ceil()或ceiling()，這兩個一樣的
4 select ceil(extract(epoch from '03:21:06.678'::time));
5 select ceiling(extract(epoch from '03:21:06.678'::time));
6 四捨五入函式round()
7 select round(extract(epoch from '03:21:06.678'::time));




將兩個日期間的時間轉換為秒值




1 select extract(epoch from(('2018-12-18 00:00:10'::timestamp - '2018-12-18 00:00:00')));
2 select extract(epoch from(('2018-12-18 00:00:10' - '2018-12-18 00:00:00'::timestamp)));
3 select extract(epoch from(('2018-12-18 00:00:10' - timestamp'2018-12-18 00:00:00')));
4 select extract(epoch from((timestamp'2018-12-18 00:00:10' - '2018-12-18 00:00:00')));




特殊：




上述只能做時分秒天，並不能做年月，因為不確定性，如有相關業務需要使用特定的方式
SELECT date_part('year', age(TIMESTAMP '2023-02-17 20:00:00' , TIMESTAMP '2021-08-17 20:00:00'))*12 + date_part('month', age(TIMESTAMP '2023-02-17 20:00:00' , TIMESTAMP '2021-08-17 20:00:00')) 月;
--結果：18
SELECT date_part('year', age(TIMESTAMP '2023-02-17 20:00:00' , TIMESTAMP '2021-08-17 20:00:00')) + date_part('month', age(TIMESTAMP '2023-02-17 20:00:00' , TIMESTAMP '2021-08-17 20:00:00'))/12 年;
--結果：1.5




參考來源：


PostgreSQL - 怎麼將時間轉換成秒 - 雨臨Lewis - 部落格園 (cnblogs.com)


PostgreSQL中epoch的用法 - 問答 - 億速雲 (yisu.com)


PostgreSQL 關於時間複雜函式詳解（長期更）_postgresql 時間一刻鐘-CSDN部落格


pgsql中epoch用法_pgsql epoch-CSDN部落格


PostgreSQL中epoch的用法 - 空谷幽蘭~泊逸 - 部落格園 (cnblogs.com)


 






相關文章