Funciones de Fecha PostgreSQL

Entender las funciones de fecha y hora en su base de datos es esencial para el análisis y reporte efectivo de datos. Siga leyendo para aprender más sobre las funciones de fecha de PostgreSQL.

Este artículo cubre algunas de las funciones de fecha y hora más útiles de PostgreSQL y sus aplicaciones en el análisis de datos y la elaboración de informes. Las funciones de fecha SQL facilitan diferentes tareas de análisis de datos, incluyendo análisis de ventas, reportes financieros, análisis de sitios web y más.

Este artículo le presenta las herramientas, en forma de funciones de fecha y hora PostgreSQL, utilizadas para realizar estas tareas. Comenzaremos con una rápida visión general de los tipos de datos de fecha y hora disponibles en PostgreSQL antes de pasar a las funciones.

Revise nuestro Libro de Cocina SQL, que contiene una variedad de recetas SQL para PostgreSQL y otros dialectos. Son un recurso valioso que vale la pena marcar como referencia rápida cuando escriba sus consultas. Puede aprender más sobre el concepto de un libro de cocina SQL aquí.

Tipos de Datos de Fecha y Hora en PostgreSQL

En PostgreSQL, hay varios tipos de datos disponibles para almacenar valores de fecha y hora. A continuación se presenta una visión general de los tipos más utilizados:

Para análisis de datos que deben tomar en consideración diferentes zonas horarias, PostgreSQL ofrece tipos de datos como TIME WITH TIME ZONE (que almacena valores de hora con información de zona horaria) y TIMESTAMP WITH TIME ZONE ( que almacena valores de fecha y hora con información de zona horaria).

Lea nuestro artículo Una Visión General de los Tipos de Datos Post greSQL para aprender más acerca de los tipos de datos disponibles en PostgreSQL. Y si quieres practicar más, echa un vistazo a estos 19 ejercicios en PostgreSQL con soluciones detalladas.

Haremos uso de todos los tipos de datos anteriores mientras exploramos las funciones de fecha y hora de PostgreSQL. ¡Comencemos!

Una Visión General de las Funciones de Fecha y Hora en PostgreSQL

Todas las funciones de fecha y hora e en PostgreSQL pueden agruparse en las siguientes categorías:

• Comparación de Valores de Fecha y Hora
• Obtención de Valores Actuales de Fecha y Hora
• Realización de Operaciones Aritméticas en Valores de Fecha y Hora
• Manipulación de Valores de Fecha y Hora
• Formateo de Valores de Fecha y Hora

En las secciones siguientes se enumeran todas las funciones relevantes con ejemplos.

Esta es la sales que utilizaremos para nuestros ejemplos:

Comparación de Valores de Fecha y Hora

Para comparar valores de fecha y hora en PostgreSQL, usamos operadores de comparación estándar. Estos operadores incluyen =, !=, <>, >, >=, <, <=, BETWEENy NOT BETWEEN.

Puede ver ejemplos de uso para cada operador de comparación a continuación.

Operador igual (=)

Pregunta: ¿Cuántas ventas se produjeron el 1 de junio de 2024?

Respuesta:

SELECT COUNT(*) AS count
FROM sales
WHERE sale_date = '2024-06-01';

Operador no igual (= o <>)

Pregunta: ¿Cuántas ventas no se produjeron a las 10:00:00?

Respuesta:

SELECT COUNT(*) AS count
FROM sales
WHERE sale_time != '10:00:00';

Operador Mayor que (>)

Pregunta: ¿Cuántas ventas se produjeron después del 1 de junio de 2024, a las 10:00:00?

Respuesta:

SELECT COUNT(*) AS count
FROM sales
WHERE sale_timestamp > '2024-06-01 10:00:00';

Nota: Al comparar fechas en SQL, el operador > significa que si la fecha X es mayor que la fecha Y, la fecha X es más futura (posterior) que la fecha Y.

Operador mayor que o igual (>=)

Pregunta: ¿Cuántas ventas se produjeron a partir de las 10:00:00 del 1 de junio de 2024?

Respuesta:

SELECT COUNT(*) AS count
FROM sales
WHERE sale_timestamp >= '2024-06-01 10:00:00';

Nota: Al comparar marcas de tiempo en SQL, el operador >= significa que si la marca de tiempo X es mayor o igual que la marca de tiempo Y, la marca de tiempo X es más futura (posterior) o igual que la marca de tiempo Y.

Operador Menor que (<)

Pregunta: ¿Cuántas ventas se produjeron antes de las 12:00:00 del 1 de junio de 2024?

Respuesta:

SELECT COUNT(*) AS count
FROM sales
WHERE sale_timestamp < '2024-06-01 12:00:00';

Nota: Al comparar marcas de tiempo en SQL, el operador < significa que si la marca de tiempo X es menor que la marca de tiempo Y, X es anterior en el tiempo a Y.

Operador menor o igual (<=)

Pregunta: ¿Cuántas ventas se produjeron el 1 de junio de 2024 o antes, a las 12:00:00?

Respuesta:

SELECT COUNT(*) AS count
FROM sales
WHERE sale_timestamp <= '2024-06-01 12:00:00';

Nota: Al comparar fechas u horas en SQL, el operador <= significa que si la fecha X es menor o igual que la fecha Y, la fecha X es anterior en el tiempo o igual a la fecha Y.

Operador BETWEEN

Pregunta: ¿Cuántas ventas se produjeron entre las 10:00:00 y las 14:00:00?

Respuesta:

SELECT COUNT(*) AS count
FROM sales
WHERE sale_timestamp BETWEEN '2024-06-01 10:00:00'
AND '2024-06-01 14:00:00';

Operador NOT BETWEEN

Pregunta: ¿Cuántas ventas no se produjeron entre las 10:00:00 y las 14:00:00?

Respuesta:

SELECT COUNT(*) AS count
FROM sales
WHERE sale_timestamp NOT BETWEEN '2024-06-01 10:00:00'
AND '2024-06-01 14:00:00';

Es bueno saberlo: Puede ordenar la salida por fecha, hora o marca de tiempo simplemente incluyendo la columna correspondiente en la cláusula ORDER BY.

Consulte estos libros de recetas para obtener más información:

• Cómo comparar 2 fechas en la cláusula WHERE en SQL
• Cómo Comparar Valores de Fecha y Hora en SQL
• Cómo Comparar Valores de Fecha en SQL
• Cómo Ordenar por Fecha en PostgreSQL u Oracle

Obtención de Valores Actuales de Fecha y Hora

Cuando analizamos datos, a menudo queremos compararlos con la fecha y hora actual cada vez que se ejecuta la consulta. En PostgreSQL, esto se puede lograr con la función NOW() y las palabras clave CURRENT_DATE, CURRENT_TIME, y CURRENT_TIMESTAMP.

Importante: La función NOW() es específica de PostgreSQL, mientras que las funciones CURRENT_* se proporcionan en SQL estándar y se recomienda su uso sobre la función NOW().

AHORA()

Utilice esta función para seleccionar la fecha y hora actuales junto con la zona horaria.

SELECT NOW();

FECHA_ACTUAL

Utilice esta palabra clave para seleccionar la fecha actual.

SELECT CURRENT_DATE;

HORA_ACTUAL

Utilice esta palabra clave para seleccionar la hora actual junto con la zona horaria (pero sin la fecha).

SELECT CURRENT_TIME;

CURRENT_TIMESTAMP

Utilice esta función para seleccionar la fecha y hora actuales junto con la zona horaria.

SELECT CURRENT_TIMESTAMP;

Consulte estos manuales para obtener más información:

• Cómo Obtener la Fecha Actual en PostgreSQL
• Cómo obtener la hora actual en PostgreSQL
• Cómo obtener la fecha y hora actuales (sin zona horaria) en PostgreSQL
• Cómo Obtener la Fecha y Hora Actuales con Desplazamiento de Zona Horaria en PostgreSQL
• Cómo Obtener la Hora Actual (Sin Zona Horaria) en PostgreSQL

Operaciones Aritméticas en Valores de Fecha y Hora

Las operaciones aritméticas en valores de fecha y hora en PostgreSQL incluyen calcular la diferencia entre dos fechas y sumar o restar INTERVALs a o desde valores de fecha/hora.

Siga los siguientes ejemplos de uso para:

Calcular la diferencia entre dos valores de fecha utilizando la función AGE().

SELECT AGE(sale_date, CURRENT_DATE) AS age_difference
FROM sales
WHERE product_name = 'Apples';

Tenga en cuenta que devuelve el INTERVALO entre dos valores.

Calcular la diferencia entre dos valores de fecha utilizando el operador menos (-).

SELECT sale_timestamp - CURRENT_TIMESTAMP AS difference
FROM sales
WHERE product_name = 'Apples';

Tenga en cuenta que devuelve un intervalo entre dos valores, como haría la función age().

Calcular la diferencia entre dos valores de tiempo utilizando el operador menos (-).

SELECT sale_time, sale_time - '09:00:00' AS difference
FROM sales
WHERE product_name = 'Apples';

Suma INTERVALOS.

Utilice el operador más (+) para añadir un INTERVAL a un valor de fecha/hora:

SELECT sale_date + INTERVAL '1 day' AS next_day
FROM sales
WHERE product_name = 'Apples';

Restar INTERVALOS.

Utilice el operador menos (-) para restar un INTERVAL de un valor de fecha/hora.

SELECT sale_date - INTERVAL '1 week' AS previous_week
FROM sales
WHERE product_name = 'Apples';

Consulte estos manuales para aprender más:

• Cómo Calcular la Diferencia de Fecha en PostgreSQL/Oracle
• Cómo Calcular la Diferencia de Fechas y Horas en PostgreSQL
• Cómo Encontrar el Intervalo entre Dos Fechas en PostgreSQL
• Cómo Obtener la Fecha de Ayer en PostgreSQL

Manipulación de Valores de Fecha y Hora

PostgreSQL provee funciones que facilitan la extracción de partes específicas de valores de fecha/hora, tales como DATE_PART(), DATE_TRUNC(), y EXTRACT(). En análisis de datos, estas funciones son comúnmente usadas para agrupar datos por día, mes o año - permitiendo así la creación de reportes con estadísticas sobre periodos de tiempo definidos.

DATE_PART()

Extrae un componente específico (por ejemplo, año, mes o día) de un valor de fecha/hora.

SELECT DATE_PART('year', sale_date) AS sale_year
FROM sales
WHERE product_name = 'Apples';

EXTRACT()

Extrae una parte específica (por ejemplo, año, mes o día) de un valor de fecha/hora.

SELECT EXTRACT(YEAR FROM sale_date) AS sale_year
FROM sales
WHERE product_name = 'Apples';

La función EXTRACT() puede utilizarse indistintamente con la función DATE_PART(). Sin embargo, EXTRACT() puede manejar zonas horarias.

DATE_TRUNC()

Trunca un valor de fecha/hora a la unidad especificada (por ejemplo, año, mes o día).

SELECT DATE_TRUNC('year', sale_date) AS year_start
FROM sales
WHERE product_name = 'Apples';

HACER_FECHA()

Ensambla una fecha completa a partir de un año, un mes y un día.

SELECT MAKE_DATE('2024', '6', '1') AS date;

GENERAR_SERIES()

Genera una serie de valores de fecha/hora con intervalos iguales.

SELECT GENERATE_SERIES(
	   '2024-06-01 08:00:00'::timestamp, 
	   '2024-06-01 09:00:00'::timestamp, 
	   '20 minutes'::interval
             ) AS generated_timestamp;

Consulte estos libros de cocina para obtener más información:

• Cómo Agrupar por Mes en PostgreSQL
• Cómo Agrupar por Año en SQL
• Cómo Extraer el Número de Semana de una Fecha en PostgreSQL
• Cómo Obtener los Nombres de los Días en PostgreSQL
• Cómo Obtener el Día del Año a partir de una Fecha en PostgreSQL
• Cómo Ordenar por Nombre de Mes en PostgreSQL u Oracle
• Cómo Obtener el Mes Anterior en SQL

Formateo de Valores de Fecha y Hora

PostgreSQL ofrece varias funciones de formato que facilitan la lectura de sus reportes. Estas incluyen TO_CHAR(), TO_DATE(), y TO_TIMESTAMP().

TO_CHAR()

Convierte un valor de fecha/hora en una cadena formateada según un formato especificado.

SELECT TO_CHAR(sale_date, 'MM/DD/YYYY') AS formatted_date
FROM sales
WHERE product_name = 'Apples';

SELECT TO_CHAR(sale_date, 'MM/DD/YYYY') AS formatted_date
FROM sales
WHERE product_name = 'Apples';

Consulte los formatos de fecha/hora disponibles proporcionados por PostgreSQL aquí.

TO_DATE()

Convierte una cadena a un valor de fecha según un formato especificado.

SELECT TO_DATE('2024-06-01', 'YYYY-MM-DD') AS date_value;

TO_TIMESTAMP()

Convierte una cadena en un valor de marca de tiempo según un formato especificado.

SELECT TO_TIMESTAMP('2024-06-01 08:00:00', 'YYYY-MM-DD HH24:MI:SS')
AS timestamp_value;

Consulte estos libros de cocina para obtener más información:

• Cómo formatear una fecha en PostgreSQL
• Cómo convertir una cadena a una fecha en PostgreSQL
• Cómo Convertir una Cadena en un Timestamp en PostgreSQL

Trabajando con Funciones de Fecha y Hora en PostgreSQL

Las funciones de fecha y hora, tal como las ofrece PostgreSQL, son herramientas cruciales en el análisis y reporte de datos. Pueden ayudarnos a comprender patrones de datos a lo largo del tiempo, como tendencias de ventas o picos de tráfico en sitios web. Con estas funciones, podemos agrupar los datos por día, mes o año, lo que facilita la detección de tendencias y la toma de decisiones informadas. Además, nos ayudan a dar formato a nuestros informes, lo que facilita su lectura y comprensión.

Como los datos suelen almacenarse en diferentes bases de datos o archivos, puede importar sus datos en formato CSV a PostgreSQL para su posterior procesamiento.

Si necesita un recordatorio práctico sobre las funciones SQL, consulte nuestra hoja de trucos gratuita Funciones estándar de SQL. Puede descargarla, imprimirla y tenerla a mano mientras trabaja y aprende.

Si quiere solidificar su conocimiento de PostgreSQL, revise nuestro completo curso SQL de la A a la Z en PostgreSQL. Ofrece cientos de ejercicios para enseñarle todo lo que necesita saber para ser un profesional de Postgres - incluyendo conceptos avanzados como funciones de ventana, consultas recursivas y PostGIS. ¡Feliz aprendizaje!