主页 / dba / 问题 / 6042
Accepted
bignose
Asked: 2011-09-22 17:01:49 +0800 CST

ISO 8601 允许的不精确日期值的单一数据类型

  • 772

如何在 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。所以这些都应该比较相等。

postgresql datatypes

2 个回答

  1. 我过去使用过 CHAR 和 VARCHAR，用问号或破折号替换缺失的部分。问号表示“未知”，破折号表示“不适用”。事实证明，这对于用户（复杂诉讼中的秘书和律师助理）来说足够直观，对于律师来说足够灵活，并且分类合理。

    "1964------"
"1964-??-??"
"1964-05---"
"1964-05-??"
"1964-05-02"
"1964-06---"
"1964-06-??"

    将您的声明和检查约束包装在 CREATE DOMAIN 或 CREATE TYPE 中，以使维护更容易。CREATE DOMAIN 不需要额外的编码。CREATE TYPE需要用低级语言编写的支持函数。

    • 6
  2. Best Answer

    不，间隔类型支持降低精度，但其他日期/时间类型都不支持。

    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
    • 5

相关问题