Lavorare con Unix-time: EPOCH

Unix-time è semplicemente un modo per rappresentare il tempo come il numero di secondi (a volte millisecondi) passati dalla mezzanotte del 1 gennaio 1970 in UTC. Questo formato è super popolare nello sviluppo: è facile da salvare, trasmettere e confrontare, perché è solo un numero.

Per dirla semplice, Unix-time è come un timer che ha iniziato a ticchettare nel 1970 e non si è mai fermato. Vedi un numero strano tipo 1697222400? Non ti spaventare — è solo quanti secondi sono passati dall'inizio del conteggio. Vuoi capire che data è? Tra poco impariamo a calcolarlo!

Questo formato viene spesso usato per sincronizzare dati tra sistemi, per salvare timestamp e quando serve confrontare velocemente cosa è successo prima e cosa dopo.

Conversione in Unix-time

In PostgreSQL c'è una funzione speciale EXTRACT(EPOCH FROM ...) che ti permette di convertire una data o un timestamp in Unix-time.

Convertiamo la data attuale in questo formato:

SELECT EXTRACT(EPOCH FROM NOW());

Il risultato sarà qualcosa tipo:

1697222400

Questo risultato è semplicemente la data e ora attuale in secondi dal 1970.

Ecco come puoi convertire una data fissa:

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

Risultato:

1696152000

Ora sappiamo che il 1 ottobre 2023 alle 12:00 UTC corrisponde a questo valore.

Conversione da Unix-time

Ovviamente, c'è anche la funzione inversa. Se hai un Unix-time e vuoi convertirlo in un formato leggibile, ti aiuta la funzione TO_TIMESTAMP().

Convertiamo Unix-time in data e ora

SELECT TO_TIMESTAMP(1697222400);

Risultato:

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

Ora vediamo che questa è la data 13 ottobre 2023 alle 00:00 UTC.

Uso pratico di Unix-time

Supponiamo di avere una tabella events dove salviamo info sugli eventi, incluse le loro timestamp in formato Unix-time.

CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    event_name TEXT,
    event_time BIGINT -- per salvare Unix-time
);

Possiamo inserire dati in questa tabella specificando i timestamp in formato Unix-time:

INSERT INTO events (event_name, event_time)
VALUES
('Avvio server', 1697222400),
('Aggiornamento database', 1697308800);

Per convertire queste info in formato leggibile, basta usare TO_TIMESTAMP:

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

Risultato:

Consigli ed errori tipici

1. Scelta sbagliata della precisione

Se il tuo formato Unix-time ha i millisecondi (tipo 1697222400000), non puoi semplicemente passarlo a TO_TIMESTAMP o usare EXTRACT. In questi casi devi dividere il valore per 1000:

SELECT TO_TIMESTAMP(1697222400000 / 1000);

Risultato:

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

2. Ignorare i fusi orari

Unix-time è in UTC, quindi i risultati saranno sempre senza considerare il fuso orario, a meno che non converti esplicitamente l'orario. Per esempio:

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

Risultato:

2023-10-13 03:00:00

3. Problemi con numeri troppo grandi

A volte capita che per sbaglio passi un valore Unix-time troppo grande (tipo millisecondi invece di secondi). Questo porta a risultati sbagliati:

SELECT TO_TIMESTAMP(1697222400000); -- Sarà errore!

Per sistemare, devi dividere il numero per 1000.

Esempio: calcolo del tempo di esecuzione di un task

Prendiamo un esempio reale: abbiamo una tabella di task tasks e vogliamo calcolare quanto tempo è passato dalla creazione di ogni task.

Creiamo la tabella:

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

Aggiungiamo qualche task:

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

Ora possiamo aggiungere una colonna con Unix-time:

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

Risultato:

E calcoliamo quanto tempo è passato dalla creazione in secondi:

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

Risultato:

Quando e perché usare Unix-time?

Unix-time è perfetto per salvare dati temporali quando devi trasferire valori tra sistemi, confrontare timestamp e in altri casi dove serve il minimo carico sulla CPU. È usatissimo in API, sviluppo web, analytics, e anche per sincronizzare app server e client. Però occhio alle sue particolarità: è UTC-centrico, può essere in secondi o millisecondi, e il formato di salvataggio può cambiare.

Se ti capita di dover integrare sistemi esterni che "parlano il linguaggio Unix-time", ora sai come gestirlo in PostgreSQL. Usa senza paura TO_TIMESTAMP e EXTRACT(EPOCH) per convertire avanti e indietro, e tutti i tuoi problemi temporali saranno risolti!