Opérations de date et d'heure dans PostgreSQL

Introduction

Dans ce laboratoire, vous explorerez les puissantes opérations de date et d'heure de PostgreSQL. Vous apprendrez à travailler avec des horodatages (timestamps) incluant des informations de fuseau horaire, une compétence cruciale pour les applications qui traitent des données provenant de différentes localisations géographiques.

Vous commencerez par créer une table conçue pour stocker des données d'événements, en utilisant le type de données TIMESTAMPTZ pour garantir la prise en compte des fuseaux horaires. Vous insérerez ensuite des données pour des événements se produisant dans différents fuseaux horaires et apprendrez comment PostgreSQL normalise ces données. Enfin, vous vous entraînerez à interroger et manipuler ces données temporelles en utilisant des fonctions telles que EXTRACT, la clause AT TIME ZONE, et en calculant des différences de temps avec INTERVAL.

Créer une table pour les événements basés sur le temps

Dans cette étape, vous vous connecterez à la base de données PostgreSQL et créerez une table pour stocker les informations sur les événements. Cette table utilisera le type de données TIMESTAMPTZ, essentiel pour stocker avec précision les horodatages provenant de divers fuseaux horaires.

Comprendre TIMESTAMPTZ

Le type de données TIMESTAMPTZ (timestamp with time zone) dans PostgreSQL est conçu pour gérer les dates et heures du monde entier. Lorsque vous insérez une valeur avec un fuseau horaire spécifique, PostgreSQL la convertit en temps universel coordonné (UTC) pour le stockage. Cette standardisation facilite la comparaison et le calcul des heures, quel que soit leur fuseau horaire d'origine.

Étape 1 : Connexion à PostgreSQL

Tout d'abord, ouvrez un terminal. Connectez-vous à la base de données PostgreSQL en utilisant le shell interactif psql. Vous exécuterez toutes les commandes SQL suivantes dans ce shell.

sudo -u postgres psql

Vous verrez maintenant l'invite PostgreSQL, qui ressemble à postgres=#.

Étape 2 : Créer la table events

Maintenant, exécutez la commande SQL suivante pour créer la table events. Cette table stockera un ID, un nom et une heure pour chaque événement.

CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    event_name VARCHAR(255),
    event_time TIMESTAMPTZ
);

Cette commande crée une table nommée events avec trois colonnes :

• id : Une clé primaire auto-incrémentée.
• event_name : Un champ texte pour le nom de l'événement.
• event_time : Un horodatage avec prise en charge des fuseaux horaires.

Étape 3 : Vérifier la création de la table

Pour confirmer que la table a été créée correctement, vous pouvez inspecter sa structure avec la commande \d.

\d events

Vous devriez voir le schéma de la table events, confirmant ses colonnes et ses types de données.

                                     Table "public.events"
   Column   |          Type          | Collation | Nullable |              Default
------------+------------------------+-----------+----------+-----------------------------------
 id         | integer                |           | not null | nextval('events_id_seq'::regclass)
 event_name | character varying(255) |           |          |
 event_time | timestamp with time zone |           |          |
Indexes:
    "events_pkey" PRIMARY KEY, btree (id)

Vous avez configuré avec succès la table pour stocker des données basées sur le temps.

Insérer des données avec des informations de fuseau horaire

Dans cette étape, vous insérerez des enregistrements dans la table events. Chaque enregistrement aura un horodatage associé à un fuseau horaire spécifique, démontrant comment PostgreSQL gère les données provenant de différentes localisations géographiques.

Étape 1 : Insérer les données d'événements

Pendant que vous êtes dans le shell psql, exécutez les instructions INSERT suivantes pour ajouter trois événements à votre table. Notez comment chaque event_time inclut un identifiant de fuseau horaire.

Insérez un événement de conférence téléphonique depuis Los Angeles (UTC-8) :

INSERT INTO events (event_name, event_time) VALUES
('Conference Call', '2024-01-20 10:00:00 America/Los_Angeles');

Insérez une réunion de projet depuis Londres (UTC+0) :

INSERT INTO events (event_name, event_time) VALUES
('Project Meeting', '2024-01-20 18:00:00 Europe/London');

Insérez une réunion de suivi, également depuis Los Angeles :

INSERT INTO events (event_name, event_time) VALUES
('Follow-up Meeting', '2024-01-21 12:00:00 America/Los_Angeles');

Étape 2 : Vérifier l'insertion des données

Pour voir comment PostgreSQL a stocké ces données, interrogez la table events.

SELECT * FROM events;

Vous devriez voir la sortie suivante.

 id |    event_name     |        event_time
----+-------------------+------------------------
  1 | Conference Call   | 2024-01-20 18:00:00+00
  2 | Project Meeting   | 2024-01-20 18:00:00+00
  3 | Follow-up Meeting | 2024-01-21 20:00:00+00
(3 rows)

Notez que toutes les valeurs event_time sont affichées en UTC (indiqué par le décalage +00). Par exemple, 10:00:00 à Los Angeles (UTC-8) a été converti en 18:00:00 UTC. Cette conversion interne permet des comparaisons cohérentes basées sur le temps.

Interroger et formater les horodatages

Maintenant que vous avez des données stockées en UTC, vous pouvez les interroger et les afficher dans le fuseau horaire de votre choix. Dans cette étape, vous utiliserez la fonction EXTRACT et la clause AT TIME ZONE pour manipuler et formater vos horodatages.

Étape 1 : Extraire des parties d'un horodatage avec EXTRACT

La fonction EXTRACT vous permet d'extraire des composants spécifiques (comme l'année, le mois, l'heure) d'un horodatage. Extraions l'année de chaque événement.

SELECT event_name, EXTRACT(YEAR FROM event_time) AS event_year FROM events;

La requête extrait l'année de l'horodatage UTC stocké.

    event_name     | event_year
-------------------+------------
 Conference Call   |       2024
 Project Meeting   |       2024
 Follow-up Meeting |       2024
(3 rows)

Étape 2 : Convertir les horodatages avec AT TIME ZONE

Pour visualiser l'heure UTC stockée dans un fuseau horaire local spécifique, utilisez la clause AT TIME ZONE. Convertissons toutes les heures d'événements au fuseau horaire America/Los_Angeles.

SELECT event_name, event_time AT TIME ZONE 'America/Los_Angeles' AS los_angeles_time FROM events;

Cette requête prend l'heure UTC (event_time) et affiche son équivalent en heure locale à Los Angeles.

    event_name     |   los_angeles_time
-------------------+---------------------
 Conference Call   | 2024-01-20 10:00:00
 Project Meeting   | 2024-01-20 10:00:00
 Follow-up Meeting | 2024-01-21 12:00:00
(3 rows)

Comme vous pouvez le constater, la 'Project Meeting' qui a eu lieu à 18:00:00 UTC est correctement affichée comme 10:00:00 en heure de Los Angeles.

Étape 3 : Combiner EXTRACT et AT TIME ZONE

Vous pouvez combiner ces fonctionnalités pour des requêtes plus puissantes. Par exemple, pour trouver l'heure de chaque événement tel qu'il s'est produit dans le fuseau horaire Europe/London :

SELECT event_name, EXTRACT(HOUR FROM event_time AT TIME ZONE 'Europe/London') AS event_hour_london FROM events;

Cette requête convertit d'abord l'heure au fuseau horaire de Londres, puis extrait l'heure.

    event_name     | event_hour_london
-------------------+-------------------
 Conference Call   |                18
 Project Meeting   |                18
 Follow-up Meeting |                20
(3 rows)

Calculer les différences de temps

Une exigence courante est de calculer la durée entre deux événements. Dans PostgreSQL, la soustraction d'un horodatage d'un autre donne un type de données INTERVAL, qui représente une durée.

Étape 1 : Calculer la différence entre deux événements

Calculons le temps écoulé entre la 'Conference Call' et la 'Follow-up Meeting'. Nous pouvons y parvenir en joignant la table events à elle-même.

SELECT
    e1.event_name AS event1,
    e2.event_name AS event2,
    e2.event_time - e1.event_time AS time_difference
FROM
    events e1
JOIN
    events e2 ON e1.event_name = 'Conference Call' AND e2.event_name = 'Follow-up Meeting';

Cette requête fonctionne en :

• Créant deux instances virtuelles de la table events, e1 et e2.
• Filtrant e1 pour ne conserver que la ligne 'Conference Call'.
• Filtrant e2 pour ne conserver que la ligne 'Follow-up Meeting'.
• Soustrayant l'event_time de e1 de celui de e2.

Le résultat est une valeur de type INTERVAL.

     event1      |      event2       | time_difference
-----------------+-------------------+-----------------
 Conference Call | Follow-up Meeting | 1 day 02:00:00
(1 row)

La différence de temps est de 1 jour et 2 heures.

Étape 2 : Extraire des composants d'un INTERVAL

Vous pouvez également utiliser EXTRACT sur un INTERVAL pour obtenir des parties spécifiques de la durée, comme le nombre de jours ou d'heures. Extraions uniquement le nombre de jours du calcul précédent.

SELECT
    EXTRACT(DAY FROM (e2.event_time - e1.event_time)) AS days_difference
FROM
    events e1
JOIN
    events e2 ON e1.event_name = 'Conference Call' AND e2.event_name = 'Follow-up Meeting';

Ceci vous donne une valeur numérique pour les jours.

 days_difference
-----------------
               1
(1 row)

Étape 3 : Quitter PostgreSQL

Vous avez terminé les exercices de ce laboratoire. Vous pouvez maintenant quitter le shell psql et revenir au terminal principal.

\q

Résumé

Dans ce laboratoire, vous avez exploré les opérations essentielles de date et d'heure dans PostgreSQL. Vous avez appris à :

• Créer une table en utilisant le type de données TIMESTAMPTZ pour stocker correctement les informations conscientes du fuseau horaire.
• Insérer des horodatages avec des fuseaux horaires spécifiés et observer comment PostgreSQL les convertit en UTC pour le stockage.
• Utiliser la fonction EXTRACT pour récupérer des composants spécifiques tels que l'année ou l'heure d'un horodatage.
• Utiliser la clause AT TIME ZONE pour afficher les heures UTC stockées dans n'importe quel fuseau horaire local souhaité.
• Calculer la durée entre deux événements, ce qui résulte en un INTERVAL, et extraire des composants de cet intervalle.

Ces compétences sont fondamentales pour construire des applications robustes qui gèrent les données temporelles avec précision à travers différentes régions.