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)

使用上相當靈活,可以多加利用.