Estatísticas e agregações por tempo no PostgreSQL

Se você quer fazer estatísticas de dados por fatias de tempo (semana, mês, ano) mas você tem valores de tempo brutos como TIMESTAMP e TIMESTAMPTZ, você pode transformá-los com o date_trunc.

SELECT '2020-01-05 00:00:01'::TIMESTAMPTZ;-- '2020-01-05 00:00:01+00'SELECT date_trunc('month','2020-01-05 00:00:01'::TIMESTAMPTZ);-- '2020-01-01 00:00:00+00'SELECT date_trunc('century','2020-01-05 00:00:01'::TIMESTAMPTZ);-- '2001-01-01 00:00:00+00'

Podemos fatiar o tempo em:

• microseconds
• milliseconds
• second
• minute
• hour
• day
• week
• month
• quarter
• year
• decade
• century
• millennium

Um uso muito bom do date_trunc é usá-lo com agregações.

SET LOCAL idle_in_transaction_session_timeout = '2 min';--SET LOCAL TIME ZONE time_zone; SELECTdate_trunc('month', event_time) AS event_time_by_month,SUM(value) AS value_sum,AVG(value) AS value_avgGROUP BY (date_trunc('month', event_time))ORDER BY (date_trunc('month', event_time)) ASC;

Lidando com fusos horários no date_trunc

A explicação abaixo serve apenas se você estiver usando uma coluna TIMESTAMPTZ.

Um detalhe importante ao usar o date_trunc é prestar atenção ao fuso horário em que a transformação é feita. Para mudar o fuso horário você pode usar:

-- Inicie uma transação, isso é importante.BEGIN;-- Mude o fuso horário da transação. Tudo que ocorrer fora da transação não terá o fuso alterado.SET LOCAL TIME ZONE 'America/Sao_Paulo';SELECTdate_trunc('month', event_time) AS event_time_by_month,SUM(value) AS value_sum,AVG(value) AS value_avgGROUP BY (date_trunc('month', event_time))ORDER BY (date_trunc('month', event_time)) ASC;COMMIT;