Início / dba / Perguntas / 105820
Accepted
flyer88
Asked: 2015-07-03 13:16:20 +0800 CST

Como obter a diferença em dias entre 2 timestamps no PostgreSQL

  • 772

Eu tenho uma tabela chamada accounts. Estou interessado em recuperar as contas cujo status não foi atualizado por mais de 10 dias. Pensei nessa consulta e pareceu funcionar, mas não tenho certeza se é a melhor abordagem:

SELECT * from accounts
WHERE status = 'PENDING_PAYMENT'
AND (date_part('day', (now() - status_updated_at)) > 10);

Uma coisa que eu não gosto nisso é o now()papel. Seria ideal se now()não fosse calculado para cada registro e, em vez disso, fosse um único valor de carimbo de data/hora calculado no início. Além disso, gostaria que este exemplo retornasse 1:

Suponha que para um registro a status_updated_atcoluna tenha este valor: 2015-10-05 23:00:00. E now()retorna 2015-10-06 01:00:00. Apesar de ser apenas algumas horas de diferença, eu quero que o resultado seja 1.

postgresql timestamp

1 respostas

  1. Best Answer

    Você pode simplificar sua expressão com a função age()(retorna interval). Mas é muito mais eficiente usar uma expressão sargável para começar.

    Isso opera com a diferença de tempo exata (o tempo atual é relevante):

    SELECT *
FROM   accounts
WHERE  status = 'PENDING_PAYMENT'
AND    status_updated_at < now() - interval '10 days';

    Para operar com dias corridos inteiros (até, mas excluindo meia-noite, fuso horário local):

    ...
AND    status_updated_at < CURRENT_DATE - 10;

    Subtraindo integerde daterendimentos date, que podem ser comparados timestampdiretamente.

    Você parece estar pedindo o comportamento da segunda variante . A diferença é de mais de 10 dias.

    Como funciona a avaliação?

    Em resposta ao seu comentário.

    CURRENT_DATEé uma função especial que retorna a corrente date(implementada internamente ('now'::cstring)::datena página 9.4). Existem muitas variantes do operador -. Encontro 43 no catálogo do sistema pg_operatorem meu banco de dados de teste atual. O correto é escolhido para os tipos de dados dos operandos.

    Para date - integer, o integernúmero de dias é subtraído de date. O Postgres moderno armazena datas e timestamps como integerquantidades internamente. É muito barato transformar um no outro, somar/subtrair dias de/para um date, ou compará-los.

    O resultado da expressão é um date, while status_updated_até uma timestampcoluna. Eu estava assumindo uma atribuição implícita lançada no início, mas essa etapa nem é necessária datee timestampcompartilha um formato binário compatível e pode ser comparado diretamente. Existe uma variante do <operador registrado para timestamp < date. Os valores da coluna podem ser testados "como estão", a expressão pode ser sargável, um índice btree pode ser usado.

    SELECT oprname, oprleft::regtype, oprright::regtype
FROM   pg_operator
WHERE  oprname = '<'
AND    oprleft = 'timestamp'::regtype;

    Relacionado:

    Com timestamptz, a questão dos fusos horários seria adicionada à equação, que é complexa, mas normalmente se reduz a uma avaliação muito simples:

    Volatilidade denow()

    Seria ideal se now()não fosse calculado para cada registro e, em vez disso, fosse um único valor de carimbo de data/hora calculado no início.

    Seu desejo foi concedido . A now()família de funções (incluindo CURRENT_DATEid definido STABLE, ou seja, now()retorna o mesmo valor dentro da mesma transação.

    • 12

relate perguntas