Trabalhando com tempo Unix: EPOCH

Tempo Unix é só um jeito de registrar o tempo como um número de segundos (às vezes milissegundos) que passaram desde a meia-noite de 1º de janeiro de 1970 no UTC. Esse formato é super popular na programação: é fácil de guardar, transferir e comparar, porque é só um número.

Pra simplificar, tempo Unix é tipo um cronômetro que começou a contar em 1970 e nunca parou. Viu um número estranho tipo 1697222400? Relaxa — é só quantos segundos passaram desde o começo da contagem. Quer saber que data é essa? Já já a gente aprende como calcular isso!

Esse formato é muito usado pra sincronizar dados entre sistemas, guardar timestamps e quando você precisa comparar rapidinho o que rolou antes ou depois.

Convertendo pra tempo Unix

No PostgreSQL tem uma função especial EXTRACT(EPOCH FROM ...), que deixa você converter uma data ou timestamp pra tempo Unix.

Bora converter a data atual pra esse formato:

SELECT EXTRACT(EPOCH FROM NOW());

O resultado vai ser algo tipo:

1697222400

Esse resultado é só a data e hora atual em segundos desde 1970.

Agora, como converter uma data fixa:

SELECT EXTRACT(EPOCH FROM TIMESTAMP '2023-10-01 12:00:00');

Resultado:

1696152000

Agora a gente sabe que 1 de outubro de 2023 às 12:00 UTC corresponde a esse valor.

Convertendo de tempo Unix

Claro que também tem a função pra fazer o caminho inverso. Se você tem um tempo Unix e quer converter pra um formato mais amigável, usa a função TO_TIMESTAMP().

Bora converter um tempo Unix pra data e hora:

SELECT TO_TIMESTAMP(1697222400);

Resultado:

2023-10-13 00:00:00+00

Agora dá pra ver que essa é a data 13 de outubro de 2023 às 00:00 UTC.

Aplicação prática do tempo Unix

Imagina que a gente tem uma tabela events, onde guardamos informações sobre eventos, incluindo os timestamps no formato Unix.

CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    event_name TEXT,
    event_time BIGINT -- pra guardar tempo Unix
);

Dá pra inserir dados nessa tabela, colocando os timestamps no formato Unix:

INSERT INTO events (event_name, event_time)
VALUES
('Inicialização do servidor', 1697222400),
('Atualização do banco de dados', 1697308800);

Pra converter essa info pra um formato mais legível, é só usar TO_TIMESTAMP:

SELECT event_name, TO_TIMESTAMP(event_time) AS readable_time
FROM events;

Resultado:

Dicas e erros comuns

1. Escolha errada de precisão

Se o seu tempo Unix tem milissegundos (tipo 1697222400000), não dá pra passar direto pra função TO_TIMESTAMP ou usar no EXTRACT. Nesses casos, tem que dividir o valor por 1000:

SELECT TO_TIMESTAMP(1697222400000 / 1000);

Resultado:

2023-10-13 00:00:00+00

2. Ignorar fusos horários

Tempo Unix é sempre em UTC, então os resultados vão ser sempre sem considerar o fuso, a não ser que você converta. Por exemplo:

SELECT TO_TIMESTAMP(1697222400) AT TIME ZONE 'Europe/Moscow';

Resultado:

2023-10-13 03:00:00

3. Problemas com números grandes demais

Às vezes a galera passa sem querer um valor de tempo Unix muito grande (tipo milissegundos em vez de segundos). Isso dá resultado errado:

SELECT TO_TIMESTAMP(1697222400000); -- Vai dar erro!

Pra corrigir, tem que dividir o número por 1000 certinho.

Exemplo: calculando tempo de execução de uma tarefa

Vamos pegar um exemplo da vida real: temos uma tabela de tarefas tasks e queremos calcular quanto tempo passou desde a criação de cada tarefa.

Criando a tabela:

CREATE TABLE tasks (
    id SERIAL PRIMARY KEY,
    task_name TEXT,
    created_at TIMESTAMP DEFAULT NOW()
);

Adicionando algumas tarefas:

INSERT INTO tasks (task_name)
VALUES
('Tarefa 1'),
('Tarefa 2'),
('Tarefa 3');

Agora dá pra adicionar uma coluna com o tempo Unix:

SELECT id, task_name, EXTRACT(EPOCH FROM created_at) AS created_epoch
FROM tasks;

Resultado:

E vamos calcular quanto tempo passou desde a criação em segundos:

SELECT id, task_name, EXTRACT(EPOCH FROM NOW()) - EXTRACT(EPOCH FROM created_at) AS elapsed_seconds
FROM tasks;

Resultado:

Quando e por que usar tempo Unix?

Tempo Unix é perfeito pra guardar dados de tempo quando você precisa transferir valores entre sistemas, comparar timestamps e outras tarefas onde quer o mínimo de processamento. É muito usado em API, web, analytics, e também pra sincronizar apps de servidor e cliente. Mas sempre lembra das particularidades: é UTC, pode ser em segundos ou milissegundos, e o formato de armazenamento pode variar.

Se você precisar integrar com sistemas externos que "falam a língua do tempo Unix", agora já sabe como lidar com isso no PostgreSQL. Pode usar TO_TIMESTAMP e EXTRACT(EPOCH) sem medo pra converter de um lado pro outro, e todos os seus problemas de tempo vão estar resolvidos!