Início / dba / Perguntas / 6042
Accepted
bignose
Asked: 2011-09-22 17:01:49 +0800 CST

Tipo de dados único para valores de data imprecisos, conforme permitido pela ISO 8601

  • 772

Como posso armazenar valores de data e hora com precisão reduzida em um tipo PostgreSQL e fazer com que eles se comportem como valores de data e/ou hora ?

A ISO 8601 permite valores de data com precisão reduzida. '1964', '1964-05', '1964-05-02' são todas representações válidas de um valor, em precisão crescente. Os tipos 'datetime' do Python também permitem valores com precisão reduzida dessa maneira.

Os tipos de tempo nativos do PostgreSQL não permitem precisão reduzida

No tipo de data nativa, todos os elementos de uma data devem estar presentes ou o valor será rejeitado. Definir os elementos abaixo do nível de precisão desejado como '00' também falha.

=> 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.

Comportamento esperado para um tipo de data e/ou hora de precisão reduzida

Existe uma maneira simples e padrão de oferecer suporte à entrada de valores de data ISO 8601 com precisão reduzida em um tipo de data e/ou hora do PostgreSQL?

Criar um tipo para isso é possível, mas não sei como. Claro, eu preciso que os valores sejam verificados no intervalo e lidem com fusos horários e comparem de forma sensata com outros valores de tempo, todas as outras coisas úteis que os tipos integrados fazem.

O que eu espero é que, assim como o valor '1964-05-02' se refere a todo o intervalo entre 00:00:00 daquele dia até 00:00:00 do dia seguinte, um valor de precisão reduzida simplesmente representaria um intervalo maior: '1962-05' refere-se a todo o intervalo entre 00:00:00 no início de maio de 1962 até 00:00:00 no primeiro dia de junho de 1962.

Um exemplo do que gostaria de ver:

=> 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)

Atualmente o primeiro se comporta como acima; o último reclama de “sintaxe de entrada inválida para o tipo carimbo de data/hora”. A meu ver, ambos são casos de valores de precisão reduzida se comportando de maneira sensata quando comparados a valores de precisão mais fina.

O significado de 1964-05na ISO 8601 inclui os valores mais precisos 1964-05-02e 1964-05-02 18:27e 1964-05-23. Portanto, todos devem ser comparados iguais.

postgresql datatypes

2 respostas

  1. Eu usei CHAR e VARCHAR no passado, substituindo as peças que faltam por pontos de interrogação ou travessões. Os pontos de interrogação significam "desconhecido" e os traços significam "não aplicável". Isso provou ser intuitivo o suficiente para os usuários (secretárias e paralegais em litígios complexos), flexível o suficiente para os advogados e organizado de maneira sensata.

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

    Envolva sua declaração e restrições CHECK em CREATE DOMAIN ou CREATE TYPE para facilitar a manutenção. CREATE DOMAIN não requer codificação adicional. CREATE TYPE requer funções de suporte escritas em uma linguagem de baixo nível.

    • 6
  2. Best Answer

    Não, o tipo de intervalo suporta precisão reduzida , mas nenhum dos outros tipos de data/hora suporta.

    O Postgres permite que você crie o seu próprio create type, mas infelizmente não permite que restrições sejam adicionadas ao tipo, o que limita sua utilidade nesse cenário. O melhor que posso sugerir exige que você repita as restrições de verificação em todos os campos em que o fuzzytipo é usado:

    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 - um exemplo de operador de igualdade:

    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 );

    consulta de amostra:

    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

relate perguntas