Postgresql 的日期時間資料型態

日期時間資料型態,是一件重要卻又蠻多繁複的知識(技巧)的玩意兒.
首先還是先介紹官網文件
https://docs.postgresql.tw/the-sql-language/data-types/date-time

有一些縮寫字,可以參考 NASA 這篇
https://solarsystem.nasa.gov/basics/chapter2-3/

首先來看一下範圍以及輸入方式

select date '4713-01-01 BC'
     , '5874897-12-31'::date;

+---------------+---------------+
|     date      |     date      |
+---------------+---------------+
| 4713-01-01 BC | 5874897-12-31 |
+---------------+---------------+

沒有 0 年

select '0001-01-01'::date - 1
     , '0001-12-31 BC'::date + 1;

+---------------+------------+
|   ?column?    |  ?column?  |
+---------------+------------+
| 0001-12-31 BC | 0001-01-01 |
+---------------+------------+

此處 1 是 integer , 結果是 date

也可以使用 interval, 得到的結果是 timestamp.

select '0001-01-01'::date - interval '1 day'
     , '0001-12-31 BC'::date + interval '1 day';

+------------------------+---------------------+
|        ?column?        |      ?column?       |
+------------------------+---------------------+
| 0001-12-31 00:00:00 BC | 0001-01-01 00:00:00 |
+------------------------+---------------------+

來看 date 相關運算子

select oprleft::regtype
     , oprname
     , oprright::regtype
     , oprresult::regtype
     , oprcode::regproc
  from pg_catalog.pg_operator
 where oprname in ('+', '-')
   and oprleft::regtype = 'date'::regtype
 order by oprname, oprright;

+---------+---------+------------------------+-----------------------------+------------------+
| oprleft | oprname |        oprright        |          oprresult          |     oprcode      |
+---------+---------+------------------------+-----------------------------+------------------+
| date    | +       | integer                | date                        | date_pli         |
| date    | +       | time without time zone | timestamp without time zone | datetime_pl      |
| date    | +       | interval               | timestamp without time zone | date_pl_interval |
| date    | +       | time with time zone    | timestamp with time zone    | datetimetz_pl    |
| date    | -       | integer                | date                        | date_mii         |
| date    | -       | date                   | integer                     | date_mi          |
| date    | -       | interval               | timestamp without time zone | date_mi_interval |
+---------+---------+------------------------+-----------------------------+------------------+
(7 rows)

在此說明一下 interval.一般大家會講時間,而比較嚴謹的說法應該說是時刻(timestamp),
兩個時刻的差異(間隔),才是時間(interval).
時刻是無法加總,平均,時間可以.
 
select age(idate)
  from (values ('2018-03-20'::date),('2019-08-22'::date)) as t(idate);

+----------------------+
|         age          |
+----------------------+
| 1 year 6 mons 5 days |
| 1 mon 3 days         |
+----------------------+
(2 rows)

with t1 (iage) as (
select age(idate)
  from (values ('2018-03-20'::date),('2019-08-22'::date)) as t(idate)
)
select sum(iage)
     , avg(iage)
  from t1;

+----------------------+----------------+
|         sum          |      avg       |
+----------------------+----------------+
| 1 year 7 mons 8 days | 9 mons 19 days |
+----------------------+----------------+

有一種計算方式,是挑一個基準時刻,例如常見的 epoch (1970-01-01 00:00:00 UTC),UNIX
的誕生年的初始時刻,計算秒差,再做加總或平均,然後再說後續加工.
使用 Postgresql interval ,就可以方便的加總,平均了.

接下來說一下 Postgresql 特殊的函數, clock_timestamp() 與 pg_sleep(seconds),
pg_sleep_for(interval), pg_sleep_until(timestamp with time zone)

一般常見到 now(), current_timestamp() 這兩個函數, Postgresql 因為有 time zone
的功能, current_timestamp() 會回報 timestamp with time zone,
所以對應有 local 系列函數, 會回報 timestamp without time zone.
那 clock_timestamp() 有何特殊之處呢?
先來看以下例子

select CURRENT_TIMESTAMP
     , clock_timestamp();

+-------------------------------+-------------------------------+
|       current_timestamp       |        clock_timestamp        |
+-------------------------------+-------------------------------+
| 2019-09-25 22:50:42.467908+08 | 2019-09-25 23:23:24.878593+08 |
+-------------------------------+-------------------------------+

commit; 

select CURRENT_TIMESTAMP
     , clock_timestamp();

+-------------------------------+-------------------------------+
|       current_timestamp       |        clock_timestamp        |
+-------------------------------+-------------------------------+
| 2019-09-25 23:24:09.639856+08 | 2019-09-25 23:24:09.640186+08 |
+-------------------------------+-------------------------------+

注意到 commit 後, current_timestamp 有了變化,不然是比較早之前.

再來看以下例子

with t1 as (
select statement_timestamp() as "statm"
     , clock_timestamp() as "clock"
     , pg_sleep(3)
  from generate_series(1, 3)
)
select statm
     , date_part('second', statm) as "statm_sec"
     , clock
     , date_part('second', clock) as "clock_sec"
  from t1
;

+------------------------------+-----------+-------------------------------+-----------+
|            statm             | statm_sec |             clock             | clock_sec |
+------------------------------+-----------+-------------------------------+-----------+
| 2019-09-26 00:12:11.64873+08 |  11.64873 | 2019-09-26 00:12:11.658443+08 | 11.658443 |
| 2019-09-26 00:12:11.64873+08 |  11.64873 | 2019-09-26 00:12:14.729182+08 | 14.729182 |
| 2019-09-26 00:12:11.64873+08 |  11.64873 | 2019-09-26 00:12:17.784561+08 | 17.784561 |
+------------------------------+-----------+-------------------------------+-----------+
(3 rows)

clock_timestamp() 是真實產生的時刻, statement_timestamp() 是 SQL Command 初始的時刻.

由上面的實例中,可以感受到各自的用途.
我個人偏好在 default value 使用 clock_timestamp(), 這樣當一道指令處理產生大量資料時,
可以較為精確的儲存每一筆紀錄的時刻.