ISO 8601 允許的不精確日期值的單一數據類型

如何在 PostgreSQL 類型中以降低的精度儲存日期和時間值，並讓它們表現為日期和/或時間值？

ISO 8601 允許降低精度的日期值。‘1964’, ‘1964-05’, ‘1964-05-02’ 都是值的有效表示，提高精度。Python ‘datetime’ 類型也允許以這種方式降低精度的值。

PostgreSQL 本機時間類型不允許降低精度

在本機日期類型中，日期的每個元素都必須存在，否則該值將被拒絕。將低於所需精度級別的元素設置為“00”也會失敗。

=> SELECT CAST('1964-05-02' AS DATE);
   date    
------------
1964-05-02
(1 row)

=> SELECT CAST('1964-05' AS DATE);
ERROR:  invalid input syntax for type date: "1964-05"
LINE 1: SELECT CAST('1964-05' AS DATE);
                   ^
=> SELECT CAST('1964' AS DATE);
ERROR:  invalid input syntax for type date: "1964"
LINE 1: SELECT CAST('1964' AS DATE);
                   ^
=> SELECT CAST('1964-00-00' AS DATE);
ERROR:  date/time field value out of range: "1964-00-00"
LINE 1: SELECT CAST('1964-00-00' AS DATE);
                   ^
HINT:  Perhaps you need a different "datestyle" setting.

精度降低的日期和/或時間類型的預期行為

是否有一種簡單、標準的方法來支持將精度降低的 ISO 8601 日期值輸入 PostgreSQL 日期和/或時間類型？

為此創建一個類型是可能的，但我不知道如何。當然，我需要對值進行範圍檢查並處理時區，並與其他時間值進行明智的比較，這是內置類型所做的所有其他有用的事情。

我期望的是，正如值 ‘1964-05-02’ 是指當天 00:00:00 到第二天 00:00:00 之間的整個間隔，降低精度的值將僅表示較大的間隔：“1962-05”是指從 1962 年 5 月初的 00:00:00 到 1962 年 6 月的第一天 00:00:00 之間的整個間隔。

我想看到的一個例子：

=> SELECT CAST('1964-05-02 00:00' AS TIMESTAMP) = CAST('1964-05-02 00:00:00' AS TIMESTAMP);
?column? 
----------
t
(1 row)

=> SELECT CAST('1964-05' AS TIMESTAMP) = CAST('1964-05-02' AS TIMESTAMP);
?column? 
----------
t
(1 row)

目前前者的行為如上；後者抱怨“類型時間戳的輸入語法無效”。在我看來，與更精細的值相比，它們都是降低精度值的表現。

1964-05ISO 8601 中的含義包括更精確的值1964-05-02和1964-05-02 18:27和1964-05-23。所以這些都應該比較相等。

不，間隔類型支持降低精度，但其他日期/時間類型都不支持。

Postgres 允許您自己滾動，create type但不幸的是不允許將約束添加到限制它在這種情況下的有用性的類型。我能想到的最好的方法是要求您對fuzzy使用該類型的每個欄位重複檢查約束：

create type preciseness as enum('day', 'month', 'year');
create type fuzzytimestamptz as (ts timestamptz, p preciseness);
create table t( id serial primary key,
               fuzzy fuzzytimestamptz
                   check( (fuzzy).ts is not null 
                          or ((fuzzy).ts is null and (fuzzy).p is not null) ),
                   check((fuzzy).ts=date_trunc('year', (fuzzy).ts) or (fuzzy).p<'year'),
                   check((fuzzy).ts=date_trunc('month', (fuzzy).ts) or (fuzzy).p<'month'),
                   check((fuzzy).ts=date_trunc('day', (fuzzy).ts) or (fuzzy).p<'day') );

insert into t(fuzzy) values (row(date_trunc('year', current_timestamp), 'year'));
insert into t(fuzzy) values (row(date_trunc('month', current_timestamp), 'month'));
insert into t(fuzzy) values (row(date_trunc('day', current_timestamp), 'day'));

select * from t;

id |              fuzzy
----+----------------------------------
 1 | ("2011-01-01 00:00:00+00",year)
 2 | ("2011-09-01 00:00:00+01",month)
 3 | ("2011-09-23 00:00:00+01",day)

–edit - 一個範例相等運算符：

create function fuzzytimestamptz_equality(fuzzytimestamptz, fuzzytimestamptz)
               returns boolean language plpgsql immutable as $$
begin
 return ($1.ts, $1.ts+coalesce('1 '||$1.p, '0')::interval)
        overlaps ($2.ts, $2.ts+coalesce('1 '||$2.p, '0')::interval);
end;$$;
--
create operator = ( procedure=fuzzytimestamptz_equality, 
                   leftarg=fuzzytimestamptz, 
                   rightarg=fuzzytimestamptz );

範例查詢：

select *, fuzzy=row(statement_timestamp(), null)::fuzzytimestamptz as equals_now,
         fuzzy=row(statement_timestamp()+'1 day'::interval, null)::fuzzytimestamptz as equals_tomorrow,
         fuzzy=row(date_trunc('month', statement_timestamp()), 'month')::fuzzytimestamptz as equals_fuzzymonth,
         fuzzy=row(date_trunc('month', statement_timestamp()+'1 month'::interval), 'month')::fuzzytimestamptz as equals_fuzzynextmonth
from t;
id |               fuzzy                | equals_now | equals_tomorrow | equals_fuzzymonth | equals_fuzzynextmonth
----+------------------------------------+------------+-----------------+-------------------+-----------------------
 1 | ("2011-01-01 00:00:00+00",year)    | t          | t               | t                 | t
 2 | ("2011-09-01 00:00:00+01",month)   | t          | t               | t                 | f
 3 | ("2011-09-24 00:00:00+01",day)     | t          | f               | t                 | f
 4 | ("2011-09-24 11:45:23.810589+01",) | f          | f               | t                 | f

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