Operaciones de Fecha y Hora en PostgreSQL

Introducción

En este laboratorio, explorará las potentes operaciones de fecha y hora de PostgreSQL. Aprenderá a trabajar con marcas de tiempo (timestamps) que incluyen información de zona horaria, una habilidad crucial para aplicaciones que manejan datos de diferentes ubicaciones geográficas.

Comenzará creando una tabla diseñada para almacenar datos de eventos, utilizando el tipo de dato TIMESTAMPTZ para garantizar la conciencia de la zona horaria. Luego insertará datos para eventos que ocurren en diferentes zonas horarias y aprenderá cómo PostgreSQL normaliza estos datos. Finalmente, practicará la consulta y manipulación de estos datos temporales utilizando funciones como EXTRACT, la cláusula AT TIME ZONE y el cálculo de diferencias de tiempo con INTERVAL.

Crear una Tabla para Eventos Basados en Tiempo

En este paso, se conectará a la base de datos PostgreSQL y creará una tabla para almacenar información de eventos. Esta tabla utilizará el tipo de dato TIMESTAMPTZ, que es esencial para almacenar con precisión marcas de tiempo de diversas zonas horarias.

Comprendiendo TIMESTAMPTZ

El tipo de dato TIMESTAMPTZ (timestamp with time zone) en PostgreSQL está diseñado para manejar fechas y horas de todo el mundo. Cuando inserta un valor con una zona horaria específica, PostgreSQL lo convierte a Tiempo Universal Coordinado (UTC) para su almacenamiento. Esta estandarización facilita la comparación y el cálculo de horas independientemente de su zona horaria original.

Paso 1: Conectarse a PostgreSQL

Primero, abra una terminal. Conéctese a la base de datos PostgreSQL utilizando el shell interactivo psql. Ejecutará todos los comandos SQL subsiguientes en este shell.

sudo -u postgres psql

Ahora verá el prompt de PostgreSQL, que se ve así: postgres=#.

Paso 2: Crear la Tabla events

Ahora, ejecute el siguiente comando SQL para crear la tabla events. Esta tabla almacenará un ID, un nombre y una hora para cada evento.

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

Este comando crea una tabla llamada events con tres columnas:

id: Una clave primaria auto-incremental.
event_name: Un campo de texto para el nombre del evento.
event_time: Una marca de tiempo con soporte para zona horaria.

Paso 3: Verificar la Creación de la Tabla

Para confirmar que la tabla se creó correctamente, puede inspeccionar su estructura con el comando \d.

\d events

Debería ver el esquema de la tabla events, confirmando sus columnas y tipos de datos.

                                     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)

Ha configurado con éxito la tabla para almacenar datos basados en el tiempo.

Insertar Datos con Información de Zona Horaria

En este paso, insertará registros en la tabla events. Cada registro tendrá una marca de tiempo asociada a una zona horaria específica, demostrando cómo PostgreSQL maneja datos de diferentes ubicaciones geográficas.

Paso 1: Insertar Datos de Eventos

Mientras se encuentra en el shell psql, ejecute las siguientes sentencias INSERT para agregar tres eventos a su tabla. Observe cómo cada event_time incluye un identificador de zona horaria.

Inserte una llamada de conferencia desde Los Ángeles (UTC-8):

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

Inserte una reunión de proyecto desde Londres (UTC+0):

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

Inserte una reunión de seguimiento, también desde Los Ángeles:

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

Paso 2: Verificar la Inserción de Datos

Para ver cómo PostgreSQL almacenó estos datos, consulte la tabla events.

SELECT * FROM events;

Debería ver la siguiente salida.

 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)

Observe que todos los valores de event_time se muestran en UTC (indicado por el desplazamiento +00). Por ejemplo, las 10:00:00 en Los Ángeles (UTC-8) se convirtieron a 18:00:00 UTC. Esta conversión interna es lo que permite comparaciones consistentes basadas en el tiempo.

Consultar y Formatear Marcas de Tiempo

Ahora que tiene datos almacenados en UTC, puede consultarlos y mostrarlos en cualquier zona horaria que necesite. En este paso, utilizará la función EXTRACT y la cláusula AT TIME ZONE para manipular y formatear sus marcas de tiempo.

Paso 1: Extraer Partes de una Marca de Tiempo con EXTRACT

La función EXTRACT le permite extraer componentes específicos (como año, mes, hora) de una marca de tiempo. Obtengamos el año de cada evento.

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

La consulta extrae el año de la marca de tiempo UTC almacenada.

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

Paso 2: Convertir Marcas de Tiempo con AT TIME ZONE

Para ver la hora UTC almacenada en una zona horaria local específica, utilice la cláusula AT TIME ZONE. Convirtamos todas las horas de los eventos de nuevo a la zona horaria America/Los_Angeles.

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

Esta consulta toma la event_time UTC y muestra su hora local equivalente en Los Ángeles.

    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)

Como puede ver, la 'Project Meeting' que ocurrió a las 18:00:00 UTC se muestra correctamente como 10:00:00 en la hora de Los Ángeles.

Paso 3: Combinar EXTRACT y AT TIME ZONE

Puede combinar estas características para consultas más potentes. Por ejemplo, para encontrar la hora de cada evento tal como ocurrió en la zona horaria Europe/London:

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

Esta consulta primero convierte la hora a la zona horaria de Londres y luego extrae la hora.

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

Calcular Diferencias de Tiempo

Un requisito común es calcular la duración entre dos eventos. En PostgreSQL, restar una marca de tiempo de otra da como resultado un tipo de dato INTERVAL, que representa una duración de tiempo.

Paso 1: Calcular la Diferencia Entre Dos Eventos

Calculemos el tiempo transcurrido entre la 'Conference Call' y la 'Follow-up Meeting'. Podemos lograr esto uniendo la tabla events consigo misma.

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';

Esta consulta funciona de la siguiente manera:

Crea dos instancias virtuales de la tabla events, e1 y e2.
Filtra e1 para incluir solo la fila 'Conference Call'.
Filtra e2 para incluir solo la fila 'Follow-up Meeting'.
Resta la event_time de e1 de la de e2.

El resultado es un valor INTERVAL.

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

La diferencia de tiempo es de 1 día y 2 horas.

Paso 2: Extraer Componentes de un INTERVAL

También puede usar EXTRACT en un INTERVAL para obtener partes específicas de la duración, como el número de días u horas. Extraigamos solo el número de días del cálculo anterior.

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';

Esto le da un valor numérico para los días.

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

Paso 3: Salir de PostgreSQL

Ha completado los ejercicios de este laboratorio. Ahora puede salir del shell psql y regresar a la terminal principal.

\q

Resumen

En este laboratorio, ha explorado operaciones esenciales de fecha y hora en PostgreSQL. Ha aprendido a:

Crear una tabla utilizando el tipo de dato TIMESTAMPTZ para almacenar correctamente información consciente de la zona horaria.
Insertar marcas de tiempo con zonas horarias especificadas y observar cómo PostgreSQL las convierte a UTC para su almacenamiento.
Utilizar la función EXTRACT para recuperar componentes específicos como el año o la hora de una marca de tiempo.
Utilizar la cláusula AT TIME ZONE para mostrar las horas UTC almacenadas en cualquier zona horaria local deseada.
Calcular la duración entre dos eventos, lo que resulta en un INTERVAL, y extraer componentes de ese intervalo.

Estas habilidades son fundamentales para construir aplicaciones robustas que gestionen datos temporales con precisión en diferentes regiones.