database - Postgres : How to insert only year in type date? 中的 ISO-8601(不完整的日期时间值)

Question

Postgres数据库 claims it supports ISO-8601 standard .在 ISO-8601 中，日期格式“yyyy”，即仅包含年份，是可以接受的。但是我找不到一种方法可以只将一年添加到“日期”类型的 Postgres 数据库字段中。知道我做错了什么或者 Postgres 中是否缺少此功能？

我看到其他帖子建议将日期设置为“yyyy-01-01”，但这不是我想要和需要的(因为它标志着一年中某个月的特定日期)。

场景

场景如下。我们正在收集关于人的信息。对于许多人来说，我们确实有确切的日期。但有些没有日期或只有年份，或年月无日。我们确实必须能够找到出生在某年之前或其他年份之后的人。如果您有完整的约会，这很容易。我希望在 Postgres 中实现一些功能来处理不完整日期的情况。

Answer 1

获取 date 数据类型的年份:

select extract(year from '2014-01-01'::date) as the_year;
 the_year 
----------
     2014

如果您只需要年份，那么使用带有检查约束的 smallint

create table t (
    the_year smallint check(
        the_year between 0 and extract(year from current_date)
    )
);

insert into t (the_year) values (2030);
ERROR:  new row for relation "t" violates check constraint "t_the_year_check"
DETAIL:  Failing row contains (2030).

insert into t (the_year) values (2014);
INSERT 0 1

但是如果您要存储整个日期，那么将其分成 3 列就没有意义了。

请注意，列的语义取决于应用程序。如果列是 date 类型，但应用程序只考虑年份，则该列表示年份。

检查 Date/Time Functions and Operators

---

@a_horse 在评论中指出的部分日期问题的一个解决方案是创建一个列来指示该日期的精度

create table t (
    the_date date,
    the_date_precision varchar(5)
);

insert into t (the_date, the_date_precision) values
(current_date, 'year'),
(current_date, 'month'),
(current_date, 'day')
;

select
    case the_date_precision
        when 'year' then to_char(the_date, 'YYYY')
        when 'month' then to_char(the_date, 'YYYY-MM')
        else to_char(the_date, 'YYYY-MM-DD')
    end as the_date
from t
;
  the_date  
------------
 2014
 2014-02
 2014-02-06

上面是 KISS 方法，但我认为下一个实现更优雅

create table t (
    the_date date,
    the_date_precision smallint
);

insert into t (the_date, the_date_precision) values
(current_date, 1),
(current_date, 2),
(current_date, 3)
;

select
    array_to_string(
        (
            string_to_array(to_char(the_date, 'YYYY-MM-DD'), '-')
        )[1:the_date_precision]
        , '-'
    ) as the_date
from t
;
  the_date  
------------
 2014
 2014-02
 2014-02-06

该选择表达式可以变成一个函数以便更容易重现。或者只是一个 View

create view view_t as 
select *,
    array_to_string(
        (
            string_to_array(to_char(the_date, 'YYYY-MM-DD'), '-')
        )[1:the_date_precision]
        , '-'
    ) as the_date_output
from t
;
select * from view_t;
  the_date  | the_date_precision | the_date_output 
------------+--------------------+-----------------
 2014-02-06 |                  1 | 2014
 2014-02-06 |                  2 | 2014-02
 2014-02-06 |                  3 | 2014-02-06