Redondeo y recorte de datos temporales: DATE_TRUNC()

DATE_TRUNC() es una herramienta potente que te deja recortar valores temporales hasta una unidad de tiempo concreta. Por ejemplo, puedes redondear una marca de tiempo al inicio del día, mes, año, hora, y así. Esto es súper útil cuando analizas datos por periodos (por ejemplo, si necesitas agrupar pedidos por día, mes o año).

Imagina la fecha y hora como una cadena larga de texto, donde tienes horas, minutos, segundos. La función DATE_TRUNC() toma esa cadena y "recorta" lo que sobra, dejando solo la parte que te interesa. Por ejemplo:

• Quieres recortar la fecha 2023-10-01 15:30:45 al inicio del día. El resultado será 2023-10-01 00:00:00.
• O quieres dejar solo el primer segundo de la hora, o sea 2023-10-01 15:00:00.

Sintaxis

La sintaxis de la función DATE_TRUNC() es así:

DATE_TRUNC(field, source)

• field — es la unidad de tiempo hasta la que quieres "recortar" la fecha. Por ejemplo, year, month, day, hour, minute.
• source — es el valor temporal que quieres recortar. Puede ser una columna tipo TIMESTAMP o el resultado de otra función, como NOW().

Ejemplo de uso sencillo:

SELECT DATE_TRUNC('day', TIMESTAMP '2023-10-01 15:30:45');
-- Resultado: 2023-10-01 00:00:00

Unidades soportadas

Aquí tienes una lista de algunas unidades de tiempo que puedes usar en DATE_TRUNC():

Cuanto más pequeña la unidad de tiempo, más preciso será el recorte. Por cierto, la semana empieza el domingo :)

Ejemplos de uso de DATE_TRUNC()

Recortar al inicio del día. En este ejemplo vamos a tomar una marca de tiempo y redondearla al inicio del día:

SELECT DATE_TRUNC('day', TIMESTAMP '2023-10-01 15:30:45') AS truncated_day;
-- Resultado: 2023-10-01 00:00:00

Recortar al inicio del mes. Ahora recortamos la fecha al inicio del mes:

SELECT DATE_TRUNC('month', TIMESTAMP '2023-10-01 15:30:45') AS truncated_month;
-- Resultado: 2023-10-01 00:00:00

Recortar al inicio del año. Vamos a redondear la fecha al inicio del año:

SELECT DATE_TRUNC('year', TIMESTAMP '2023-10-01 15:30:45') AS truncated_year;
-- Resultado: 2023-01-01 00:00:00

Uso con la hora actual (NOW()). Si necesitas trabajar siempre con la fecha y hora actual, puedes combinar DATE_TRUNC() y NOW():

SELECT DATE_TRUNC('hour', NOW()) AS truncated_hour;
-- El resultado depende de la hora actual, por ejemplo: 2023-10-01 15:00:00

Agrupar pedidos por meses. Ahora vamos a un ejemplo más práctico. Supón que tienes una tabla de pedidos, donde cada registro tiene la fecha del pedido. Queremos contar cuántos pedidos hubo cada mes:

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    order_date TIMESTAMP NOT NULL
);

INSERT INTO orders (order_date) VALUES
('2023-10-01 10:15:00'),
('2023-10-01 15:30:00'),
('2023-09-15 12:45:00'),
('2023-08-20 09:00:00'),
('2023-08-25 10:30:00');

SELECT DATE_TRUNC('month', order_date) AS order_month,
       COUNT(*) AS total_orders
FROM orders
GROUP BY order_month
ORDER BY order_month;

Resultado:

Casos prácticos de uso

Análisis de datos temporales por periodos: ¿quieres saber cuántos usuarios se registraron cada año, mes o día? Usa DATE_TRUNC() para agrupar los datos.

Creación de informes: redondear bien la marca de tiempo ayuda a que los informes sean más fáciles de leer.

Comparación de fecha y hora: si tienes datos temporales con mucha precisión (por ejemplo, con milisegundos), recórtalos al nivel que necesites para comparar bien.

Errores típicos al usar DATE_TRUNC()

Usar campos no soportados. Por ejemplo, el campo millisecond no está soportado, y si lo usas te dará error.

Tipo de dato incorrecto. La función DATE_TRUNC() solo funciona con tipos de datos temporales, como TIMESTAMP. Si le pasas una cadena, te va a dar error.

Error de redondeo. Recuerda que DATE_TRUNC() siempre recorta la hora al inicio de la unidad de tiempo indicada. Si quieres redondear la hora, tendrás que usar otros métodos.