DATE_TRUNC()

Concepts covered: Date Truncation

date_trunc(unit, value) keeps every field down to the named unit and resets the smaller ones to their minimum: day 1, hour 0, minute 0, second 0. The result has the input's type, so a truncated timestamp can still be sorted and compared, and interval arithmetic works on it. Every value inside the same unit lands on the same start point, which turns the truncated column into a grouping key.

SELECT
  date_trunc('minute', TIMESTAMP '2024-03-14 17:42:09') AS to_minute,
  date_trunc('hour', TIMESTAMP '2024-03-14 17:42:09') AS to_hour,
  date_trunc('day', TIMESTAMP '2024-03-14 17:42:09') AS to_day,
  date_trunc('week', TIMESTAMP '2024-03-14 17:42:09') AS to_week,
  date_trunc('month', TIMESTAMP '2024-03-14 17:42:09') AS to_month,
  date_trunc('quarter', TIMESTAMP '2024-03-14 17:42:09') AS to_quarter

March 14, 2024 is a Thursday, and 'week' snaps it back to Monday, March 11: date_trunc always starts weeks on Monday, the convention the ISO 8601 section covers in full. 'quarter' lands on January 1 because March sits in the first quarter, and 'year' would land there too.

The units are 'second', 'minute', 'hour', 'day', 'week', 'month', 'quarter' and 'year'.

Truncation keeps the year; month() drops it

Grouping by month(ordered_at), from the intermediate lesson, returns 3 for every March. That puts March 2023 and March 2024 in 1 group. date_trunc('month', ordered_at) returns a full timestamp such as 2024-03-01 00:00:00, which still names its year and sorts across years in calendar order.

month()

Returns 1 row: month 3 with 4 orders, placed a year apart and counted together.

SELECT
  month(ordered_at) AS order_month,
  COUNT(*) AS orders
FROM orders
GROUP BY month(ordered_at)
ORDER BY order_month

date_trunc

Returns 2 rows, 2023-03-01 and 2024-03-01, each with 2 orders.

SELECT
  date_trunc('month', ordered_at) AS order_month,
  COUNT(*) AS orders
FROM orders
GROUP BY date_trunc('month', ordered_at)
ORDER BY order_month

The GROUP BY repeats the whole date_trunc expression because GROUP BY runs before the SELECT list names order_month. Change the unit string and the same query counts at another grain, such as week or quarter. Every key stays a timestamp, so later steps can filter on it or format it.

Truncate on the local clock

On a TIMESTAMP WITH TIME ZONE, date_trunc cuts at the boundaries of the zone the value carries. Truncate a UTC value to 'day' and days break at UTC midnight, which is 6:00 p.m. in Chicago during standard time, so an evening shift splits across 2 days. Convert with AT TIME ZONE before truncating, and days break at Chicago midnight.

SELECT
  trip_id,
  date_trunc('day', CAST(ended_at AS TIMESTAMP WITH TIME ZONE)) AS utc_day,
  date_trunc('day', CAST(ended_at AS TIMESTAMP WITH TIME ZONE) AT TIME ZONE 'America/Chicago') AS chicago_day
FROM trips

Trips 101 and 103 each move back 1 day once the cut reads Chicago's clock, because both ended at 23:50 local. Trip 102 ended at 1:05 p.m. in Chicago and keeps March 10 either way. A daily total grouped by utc_day would credit 2 Sunday-night fares to Monday.

Weeks that open Monday at 4:00 a.m.

A pay week that runs Monday 4:00 a.m. to the next Monday 3:59 a.m. matches no unit. Shift every timestamp back 4 hours so the boundary lands on midnight, truncate to 'week', then add the 4 hours back. The fares table below holds Chicago wall-clock times as plain timestamps; with UTC data, convert before shifting. Here are 3 fares walked through by hand.

SELECT
  date_trunc('week', ended_local - INTERVAL '4' HOUR) + INTERVAL '4' HOUR AS pay_week_start,
  COUNT(*) AS fares,
  SUM(amount) AS earnings
FROM fares
GROUP BY date_trunc('week', ended_local - INTERVAL '4' HOUR) + INTERVAL '4' HOUR
ORDER BY pay_week_start

Fare 2 ended on Monday, March 4, before 4:00 a.m., so it joins the week of February 26 with Sunday night's fare 1. Fare 5 at 03:59 on March 11 still counts toward the March 4 week, and fare 6, 1 minute later, opens the next one.

Using the 4-hour shift from the fares query, which pay week start does a fare that ends at 2024-03-18 03:30:00 get?

• 2024-03-18 04:00:00
• 2024-03-11 00:00:00
• 2024-03-11 04:00:00
• 2024-03-18 00:00:00

About this lesson

This section is part of the Dates: Advanced lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.