Operaciones de Fecha y Hora en PostgreSQL

Introducción

En este laboratorio, explorará las operaciones de fecha y hora de PostgreSQL, centrándose en el manejo de zonas horarias y la realización de cálculos. Comenzará insertando marcas de tiempo (timestamps) con información de zona horaria en una tabla de la base de datos, demostrando cómo almacenar eventos que ocurren en diferentes ubicaciones geográficas.

El laboratorio luego lo guiará a través del uso de funciones como EXTRACT e INTERVAL para manipular y consultar datos de fecha y hora. Aprenderá cómo consultar datos a través de zonas horarias y calcular las diferencias de tiempo entre eventos, proporcionando habilidades prácticas para administrar datos temporales en PostgreSQL.

Skills Graph

Conectarse a PostgreSQL y Crear la Tabla events

En este paso, se conectará a la base de datos PostgreSQL y creará la tabla events. Esta tabla almacenará los nombres de los eventos y sus correspondientes marcas de tiempo (timestamps) con información de zona horaria.

Primero, abra su terminal. Para conectarse a la base de datos PostgreSQL como el usuario postgres, ejecute el siguiente comando:

sudo -u postgres psql

Este comando abre la terminal interactiva de PostgreSQL, también conocida como el shell psql. Usará este shell para ejecutar comandos SQL.

Ahora, creemos la tabla events. Ejecute el siguiente comando SQL:

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 serial que se incrementa automáticamente para cada nuevo evento.
• event_name: Una cadena (string) que contiene el nombre del evento.
• event_time: Una marca de tiempo con información de zona horaria (TIMESTAMPTZ). Este tipo de datos es crucial para manejar eventos que ocurren en diferentes zonas horarias.

Para verificar la creación de la tabla, puede describir la estructura de la tabla usando el siguiente comando:

\d events

Esto mostrará el esquema de la tabla, confirmando la creación exitosa de la tabla events.

Insertar Marcas de Tiempo con Zonas Horarias

En este paso, insertará datos en la tabla events, especificando la zona horaria para cada evento. Esto demuestra cómo almacenar eventos que ocurren en diferentes ubicaciones geográficas.

Usando el shell psql (conectado en el paso anterior), inserte los siguientes datos en la tabla events:

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

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

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

En estas sentencias INSERT:

• '2024-01-20 10:00:00-08 America/Los_Angeles' especifica una marca de tiempo del 20 de enero de 2024 a las 10:00 AM en la zona horaria America/Los_Angeles (UTC-8).
• '2024-01-20 18:00:00+00 Europe/London' especifica una marca de tiempo del 20 de enero de 2024 a las 6:00 PM en la zona horaria Europe/London (UTC+0).

Para verificar la inserción de datos, consulte la tabla events:

SELECT * FROM events;

Debería ver una salida similar a esta:

 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 PostgreSQL almacena las marcas de tiempo en UTC (Tiempo Universal Coordinado). Cuando inserta una marca de tiempo con una zona horaria, PostgreSQL la convierte a UTC y la almacena internamente.

Usando EXTRACT y AT TIME ZONE

En este paso, aprenderá cómo usar la función EXTRACT para extraer partes específicas de una marca de tiempo (timestamp) (como año, mes, día) y la cláusula AT TIME ZONE para convertir marcas de tiempo a diferentes zonas horarias.

Primero, extraigamos el año de la columna event_time. Ejecute la siguiente consulta (query):

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

Esta consulta utiliza la función EXTRACT para obtener el año de la columna event_time y lo alias (aliases) como event_year. La salida será:

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

A continuación, veamos cómo convertir el event_time a una zona horaria específica utilizando la cláusula AT TIME ZONE. Para ver el event_time en la zona horaria America/Los_Angeles, use la siguiente consulta:

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

Esta consulta convierte el event_time a la zona horaria America/Los_Angeles y lo muestra como los_angeles_time. La salida será:

   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 04:00:00
(3 rows)

Puede combinar EXTRACT y AT TIME ZONE para extraer componentes específicos de la marca de tiempo en una zona horaria particular. Por ejemplo, para encontrar la hora de los eventos en la zona horaria America/Los_Angeles, use la siguiente consulta:

SELECT event_name, EXTRACT(HOUR FROM event_time AT TIME ZONE 'America/Los_Angeles') AS event_hour_la FROM events;

Esto mostrará la hora de cada evento en la zona horaria America/Los_Angeles.

Calculando Diferencias de Tiempo con INTERVAL

En este paso, aprenderá cómo calcular la diferencia de tiempo entre dos eventos utilizando el tipo de datos INTERVAL.

Para calcular la diferencia de tiempo, puede restar una marca de tiempo (timestamp) de otra. El resultado será un valor INTERVAL que representa la diferencia.

Calculemos la diferencia de tiempo entre 'Follow-up Meeting' y 'Conference Call'. Para hacer esto, puede usar una subconsulta (subquery) o unir (join) la tabla a sí misma. Aquí hay un ejemplo usando un join:

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 une la tabla events a sí misma, asignando alias a las tablas como e1 y e2. Luego selecciona el event_name de ambas tablas y calcula la diferencia de tiempo entre el event_time de 'Follow-up Meeting' y 'Conference Call'. La salida será:

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

La columna time_difference muestra el intervalo entre los dos eventos.

También puede extraer componentes específicos del valor INTERVAL utilizando la función EXTRACT. Por ejemplo, para extraer el número de días de la diferencia de tiempo, puede usar la siguiente consulta:

SELECT
    e1.event_name AS event1,
    e2.event_name AS event2,
    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';

Esta consulta extrae el número de días de la time_difference y lo muestra como days_difference.

Finalmente, salga del shell de PostgreSQL:

\q

Esto lo devolverá a la terminal.

Resumen

En este laboratorio, exploró las operaciones de fecha y hora de PostgreSQL. Aprendió cómo:

• Crear una tabla con una columna TIMESTAMPTZ para almacenar marcas de tiempo (timestamps) con información de zona horaria.
• Insertar datos en la tabla, especificando la zona horaria para cada evento.
• Usar la función EXTRACT para extraer partes específicas de una marca de tiempo, como el año, el mes o el día.
• Usar la cláusula AT TIME ZONE para convertir marcas de tiempo a diferentes zonas horarias.
• Calcular la diferencia de tiempo entre dos marcas de tiempo utilizando el tipo de datos INTERVAL.

Estas habilidades son esenciales para administrar datos temporales en PostgreSQL, especialmente cuando se trata de eventos que ocurren en diferentes ubicaciones geográficas.