Início / dba / Perguntas / 345521
Accepted
muttBunch
Asked: 2025-03-02 05:45:07 +0800 CST

Como recuperar dados com base no acumulado do ano no Postgres?

  • 772

Gostaria de recuperar dados de "year to date" na minha consulta abaixo. Eu esperava algo simples no 'INTERVAL', mas não consegui encontrar nada.

Eu tenho o seguinte:

SELECT
    "status_id", date_part('epoch', time) * 1000 as time,
    COUNT(time) as "count"
FROM evStatus
WHERE time >= NOW() - Interval '{1}'
GROUP BY "status_id", time

Gostaria de obter dados de janeiro de qualquer ano até a data atual.

Testei isso abaixo e parece funcionar, mas quer ter certeza de que essa é a maneira correta?

SELECT
    "status_id", date_part('epoch', time) * 1000 as time,
    COUNT(time) as "count"
WHERE time BETWEEN date_trunc('year', now()) AND CURRENT_TIMESTAMP
postgresql

2 respostas

  1. Best Answer

    Há mais nessa pergunta aparentemente simples do que você imagina.

    Supondo que a coluna seja do tipo timestamp with time zone(e não timeor timestamp without time zone) - caso contrário, haveria mais problemas - prossigo com o nome da coluna "tstz". "time" seria enganoso e também é uma palavra reservada no SQL padrão.

    SELECT status_id
     , extract(epoch FROM tstz) * 1000 AS tstz_epoch  -- ①  
     , count(*) AS row_count                          -- ②
FROM   evStatus                                       -- ③
WHERE  tstz >= date_trunc('year', now(), 'UTC')       -- ④
-- AND tstz <= now()                                  -- ⑤
GROUP  BY status_id, tstz;

    ① Em vez disso, não repita o nome de entrada ("time" no seu original) como nome de saída. Isso pode causar confusão. Sua referência posterior GROUP BYé redirecionada para o nome da coluna de saída (ocultando o mesmo nome de entrada), mas seria substancialmente mais barato com base na entrada. Veja:

    ② Depois WHERE tstz >= <anything>, o conjunto de resultados não pode conter tstz IS NULL, então count(*)é 100% equivalente e um pouco mais simples e rápido que count(tstz).

    ③ Estranhamente, sua consulta original cita "status_id", onde as aspas são apenas ruído, mas não evStatus, que é efetivamente tratado como evstatus(tudo em minúsculas). Não tenho certeza se isso é intencional. Veja:

    ④ date_tunc(field, timestamptz)(sem fuso horário como 3º parâmetro) não é uma expressão imutável. Ela assume como padrão a timezoneconfiguração da sessão em execução para determinar o início exato do ano. Algumas horas geralmente não importam muito. Mas pode introduzir erros de caso extremo que são muito difíceis de definir se você não estiver ciente. Você provavelmente realmente quer date_tunc(field, timestamptz, time_zone). Eu uso 'UTC' no exemplo. Substitua pelo nome do seu fuso horário. Relacionado:

    ⑤ Normalmente, entradas futuras não são possíveis por design. Então você pode simplesmente usar WHERE tstz >= date_trunc('year', now()). Um pouco mais simples e rápido. (Caso contrário, inclua o segundo filtro.)

    Noções básicas sobre tipos de registro de data e hora no Postgres:

    • 4 
  2. WHERE "time" >= NOW() - INTERVAL '1 YEAR'

    Não use palavras-chave como "time"nomes de tabelas ou colunas!

    • 3

relate perguntas