Travailler avec le temps Unix : EPOCH

Le temps Unix, c'est juste une façon de noter le temps comme un nombre de secondes (et parfois de millisecondes) écoulées depuis minuit le 1er janvier 1970 en UTC. Ce format est super populaire en dev : c'est facile à stocker, à transmettre et à comparer, vu que c'est juste un nombre.

Pour faire simple, le temps Unix, c'est comme un chrono qui a commencé à tourner en 1970 et qui s'arrête jamais. Tu vois un nombre chelou genre 1697222400 ? Panique pas — c'est juste le nombre de secondes depuis le début du compte à rebours. Tu veux savoir à quelle date ça correspond ? On va voir comment calculer ça !

Ce format est souvent utilisé pour synchroniser des données entre systèmes, pour stocker des timestamps et quand tu veux comparer vite fait ce qui s'est passé avant ou après.

Conversion vers le temps Unix

Dans PostgreSQL, il y a une fonction spéciale EXTRACT(EPOCH FROM ...) qui permet de convertir une date ou un timestamp en temps Unix.

On va convertir la date actuelle dans ce format :

SELECT EXTRACT(EPOCH FROM NOW());

Le résultat sera un truc du genre :

1697222400

Ce résultat — c'est juste la date et l'heure actuelles en secondes depuis 1970.

Et voilà comment convertir une date fixe :

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

Résultat :

1696152000

Maintenant on sait que le 1 octobre 2023 à 12:00 UTC correspond à cette valeur.

Conversion depuis le temps Unix

Bien sûr, il y a aussi la fonction inverse. Si t'as un temps Unix et que tu veux le convertir dans un format lisible pour les humains, la fonction TO_TIMESTAMP() va t'aider.

On va convertir un temps Unix en date et heure

SELECT TO_TIMESTAMP(1697222400);

Résultat :

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

Maintenant on voit que c'est la date du 13 octobre 2023 à 00:00 UTC.

Utilisation pratique du temps Unix

Imaginons qu'on a une table events où on stocke des infos sur des événements, y compris leurs timestamps au format Unix.

CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    event_name TEXT,
    event_time BIGINT -- pour stocker le temps Unix
);

On peut insérer des données dans cette table en mettant les timestamps au format Unix :

INSERT INTO events (event_name, event_time)
VALUES
('Lancement du serveur', 1697222400),
('Mise à jour de la base de données', 1697308800);

Pour convertir ces infos en format lisible, il suffit d'utiliser TO_TIMESTAMP :

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

Résultat :

Astuces et erreurs classiques

1. Mauvais choix de précision

Si ton format de temps Unix est en millisecondes (genre 1697222400000), tu peux pas juste le passer direct à TO_TIMESTAMP ou utiliser EXTRACT. Dans ce cas, il faut diviser la valeur par 1000 :

SELECT TO_TIMESTAMP(1697222400000 / 1000);

Résultat :

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

2. Ignorer les fuseaux horaires

Le temps Unix est en UTC, donc les résultats seront toujours sans tenir compte du fuseau horaire, sauf si tu convertis explicitement l'heure. Par exemple :

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

Résultat :

2023-10-13 03:00:00

3. Problèmes avec des nombres trop grands

Parfois, les devs passent par erreur une valeur de temps Unix trop grande (genre des millisecondes au lieu de secondes). Ça donne des résultats foireux :

SELECT TO_TIMESTAMP(1697222400000); -- Ça va planter !

Pour corriger ça, il faut bien diviser le nombre par 1000.

Exemple : calcul du temps d'exécution d'une tâche

Prenons un exemple de la vraie vie : on a une table de tâches tasks et on veut calculer combien de temps s'est écoulé depuis la création de chaque tâche.

On crée la table :

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

On ajoute quelques tâches :

INSERT INTO tasks (task_name)
VALUES
('Tâche 1'),
('Tâche 2'),
('Tâche 3');

Maintenant on peut ajouter une colonne avec le temps Unix :

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

Résultat :

Et on calcule combien de temps s'est écoulé depuis la création en secondes :

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

Résultat :

Quand et pourquoi utiliser le temps Unix ?

Le temps Unix est parfait pour stocker des données temporelles quand tu dois échanger des valeurs entre systèmes, comparer des timestamps et pour d'autres tâches où il faut que ce soit rapide et léger. C'est super utilisé dans les API, le dev web, l'analytics, et aussi pour synchroniser les applis côté serveur et client. Mais fais gaffe à ses particularités : c'est toujours en UTC, il peut être en secondes ou millisecondes, et le format de stockage peut varier.

Si tu dois intégrer des systèmes externes qui "parlent le langage du temps Unix", maintenant tu sais comment t'en servir dans PostgreSQL. Utilise sans hésiter TO_TIMESTAMP et EXTRACT(EPOCH) pour convertir dans un sens ou dans l'autre, et toutes tes galères de temps seront réglées !