Postgresql 日期時間資料型態的interval
在前兩天的探討中,有出現 interval.
在 Postgresql interval 輸出有4種格式.
來看以下範例
SET intervalstyle = 'sql_standard';
SELECT INTERVAL '7 years 6 months 5 days 4 hours 3 minutes 21 second';
+------------------+
| interval |
+------------------+
| +7-6 +5 +4:03:21 |
+------------------+
SET intervalstyle = 'postgres';
SELECT INTERVAL '+7-6 +5 +4:03:21';
+--------------------------------+
| interval |
+--------------------------------+
| 7 years 6 mons 5 days 04:03:21 |
+--------------------------------+
SET intervalstyle = 'postgres_verbose';
SELECT INTERVAL '7 years 6 mons 5 days 04:03:21';
+------------------------------------------------+
| interval |
+------------------------------------------------+
| @ 7 years 6 mons 5 days 4 hours 3 mins 21 secs |
+------------------------------------------------+
SET intervalstyle = 'iso_8601';
SELECT INTERVAL '@ 7 years 6 mons 5 days 4 hours 3 mins 21 secs';
+-----------------+
| interval |
+-----------------+
| P7Y6M5DT4H3M21S |
+-----------------+
來看相關運算子及運算結果
select oprleft::regtype
, oprname
, oprright::regtype
, oprresult::regtype
, oprcode::regproc
from pg_catalog.pg_operator
where oprname in ('+', '-', '*', '/')
and oprleft::regtype = 'interval'::regtype
union all
select oprleft::regtype
, oprname
, oprright::regtype
, oprresult::regtype
, oprcode::regproc
from pg_catalog.pg_operator
where oprname in ('+', '-', '*', '/')
and oprresult::regtype = 'interval'::regtype
order by oprleft, oprname, oprright;
+-----------------------------+---------+-----------------------------+-----------------------------+-------------------------+
| oprleft | oprname | oprright | oprresult | oprcode |
+-----------------------------+---------+-----------------------------+-----------------------------+-------------------------+
| - | - | interval | interval | interval_um |
| double precision | * | interval | interval | mul_d_interval |
| time without time zone | - | time without time zone | interval | time_mi_time |
| timestamp without time zone | - | timestamp without time zone | interval | timestamp_mi |
| timestamp with time zone | - | timestamp with time zone | interval | timestamptz_mi |
| interval | * | double precision | interval | interval_mul |
| interval | * | double precision | interval | interval_mul |
| interval | + | date | timestamp without time zone | interval_pl_date |
| interval | + | time without time zone | time without time zone | interval_pl_time |
| interval | + | timestamp without time zone | timestamp without time zone | interval_pl_timestamp |
| interval | + | timestamp with time zone | timestamp with time zone | interval_pl_timestamptz |
| interval | + | interval | interval | interval_pl |
| interval | + | interval | interval | interval_pl |
| interval | + | time with time zone | time with time zone | interval_pl_timetz |
| interval | - | interval | interval | interval_mi |
| interval | - | interval | interval | interval_mi |
| interval | / | double precision | interval | interval_div |
| interval | / | double precision | interval | interval_div |
+-----------------------------+---------+-----------------------------+-----------------------------+-------------------------+
可以自行根據上面的結果,來做些測試觀察.
來看些野生的 interval 測試吧.
select '2000-03-01'::timestamp - '2000-02-28'::timestamp;
+----------+
| ?column? |
+----------+
| P2D |
+----------+
iso_8601 格式, 2天. 因為2000年是閏年,有2000-02-29.
select '2000-02-28'::timestamp + interval 'P1D';
+---------------------+
| ?column? |
+---------------------+
| 2000-02-29 00:00:00 |
+---------------------+
select interval '1 minutes' * 1440
, interval '1 minutes' * 1441
, interval '1 minutes' * 1440 + interval '1 second';
+----------+----------+----------+
| ?column? | ?column? | ?column? |
+----------+----------+----------+
| PT24H | PT24H1M | PT24H1S |
+----------+----------+----------+
透過乘法運算,會出現 時間是正確的,但是沒有修正成 n天m時.
不過別擔心, Postgresql 有提供校正函數 justify_interval() 系列.
with t1(c1, c2, c3) as (
select interval '1 minutes' * 1440
, interval '1 minutes' * 1441
, interval '1 minutes' * 1440 + interval '1 second'
)
select justify_interval(c1)
, justify_interval(c2)
, justify_interval(c3)
from t1;
+------------------+------------------+------------------+
| justify_interval | justify_interval | justify_interval |
+------------------+------------------+------------------+
| P1D | P1DT1M | P1DT1S |
+------------------+------------------+------------------+
這樣就校正了.還有 justify_days(), justify_hours(). 可以自行測試之.
interval 還時常運用在搭配 generate_series() 產生大量測試用資料,或計算時.
select generate_series(timestamp '2000-02-01', '2000-03-01', '1 day');
+---------------------+
| generate_series |
+---------------------+
| 2000-02-01 00:00:00 |
| 2000-02-02 00:00:00 |
...
| 2000-02-29 00:00:00 |
| 2000-03-01 00:00:00 |
+---------------------+
(30 rows)
select timestamp '2000-02-01' + interval '1 day' * n
from generate_series(0, 29) as gs(n);
+---------------------+
| ?column? |
+---------------------+
| 2000-02-01 00:00:00 |
| 2000-02-02 00:00:00 |
...
| 2000-02-29 00:00:00 |
| 2000-03-01 00:00:00 |
+---------------------+
(30 rows)
使用上相當靈活,可以多加利用.