DATE_TRUNC for rounding dates in PostgreSQL - Mini Project: Build & Apply

Using DATE_TRUNC to Round Dates in PostgreSQL

📖 Scenario: You work in a company that tracks sales data with timestamps. You want to group sales by month to see monthly totals.

🎯 Goal: Build a simple PostgreSQL query that uses DATE_TRUNC to round timestamps to the start of the month.

📋 What You'll Learn

Create a table called sales with columns id (integer), sale_date (timestamp), and amount (numeric).

Insert three sales records with exact timestamps and amounts.

Create a variable or CTE to hold the truncation precision 'month'.

Write a query that selects the truncated sale_date by month and sums amount grouped by that truncated date.

💡 Why This Matters

🌍 Real World

Grouping sales or events by month helps businesses analyze monthly trends and make decisions.

💼 Career

Knowing how to use DATE_TRUNC is useful for data analysts and database developers working with time-series data.

1

Create the sales table and insert data

Create a table called sales with columns id as integer, sale_date as timestamp, and amount as numeric. Then insert these three rows exactly: (1, '2024-04-15 10:30:00', 100.50), (2, '2024-04-20 15:45:00', 200.75), and (3, '2024-05-05 09:00:00', 150.00).

PostgreSQL

-- Create the sales table and insert the three rows here
-- Your code here

Hint

Use CREATE TABLE to define the table and INSERT INTO to add rows.

2

Define the truncation precision

Create a variable or CTE called precision and set it to the string 'month' to specify the date truncation level.

PostgreSQL

CREATE TABLE sales (
  id INTEGER,
  sale_date TIMESTAMP,
  amount NUMERIC
);

INSERT INTO sales (id, sale_date, amount) VALUES
  (1, '2024-04-15 10:30:00', 100.50),
  (2, '2024-04-20 15:45:00', 200.75),
  (3, '2024-05-05 09:00:00', 150.00);

-- Create a variable or CTE called precision with value 'month'
-- Your code here

Hint

Use a CTE with WITH and SELECT 'month'::text AS level.

3

Write the query using DATE_TRUNC

Write a query that selects DATE_TRUNC('month', sale_date) as month_start and sums amount as total_amount from sales. Group the results by month_start.

PostgreSQL

CREATE TABLE sales (
  id INTEGER,
  sale_date TIMESTAMP,
  amount NUMERIC
);

INSERT INTO sales (id, sale_date, amount) VALUES
  (1, '2024-04-15 10:30:00', 100.50),
  (2, '2024-04-20 15:45:00', 200.75),
  (3, '2024-05-05 09:00:00', 150.00);

WITH precision AS (
  SELECT 'month'::text AS level
)

SELECT DATE_TRUNC('month', sale_date) AS month_start, SUM(amount) AS total_amount
FROM sales
GROUP BY month_start
-- Your code here

Hint

Use DATE_TRUNC('month', sale_date) in the SELECT and group by that alias.

4

Complete the query using the precision variable

Modify the query to use the precision CTE's level value inside DATE_TRUNC instead of the fixed string 'month'. Use a join or cross join with precision to access level.

PostgreSQL

CREATE TABLE sales (
  id INTEGER,
  sale_date TIMESTAMP,
  amount NUMERIC
);

INSERT INTO sales (id, sale_date, amount) VALUES
  (1, '2024-04-15 10:30:00', 100.50),
  (2, '2024-04-20 15:45:00', 200.75),
  (3, '2024-05-05 09:00:00', 150.00);

WITH precision AS (
  SELECT 'month'::text AS level
)

-- Modify the query to use precision.level inside DATE_TRUNC
-- Your code here

Hint

Use a cross join with precision and replace the fixed string with precision.level inside DATE_TRUNC.

Practice

1. What does the PostgreSQL function DATE_TRUNC('month', timestamp) do?

easy

A. It returns the exact timestamp without any change.

B. It rounds the timestamp down to the first day of the month at 00:00:00.

C. It rounds the timestamp up to the last day of the month at 23:59:59.

D. It converts the timestamp to a string showing only the month.

Solution

1. Step 1: Understand DATE_TRUNC behavior

   DATE_TRUNC cuts off smaller parts of a timestamp to the specified precision, rounding down.

2. Step 2: Apply 'month' precision

   Using 'month' sets the date to the first day of that month at midnight (00:00:00).

3. Final Answer:

   It rounds the timestamp down to the first day of the month at 00:00:00. -> Option B

4. Quick Check:

   DATE_TRUNC('month', timestamp) = first day of month [OK]

Hint: DATE_TRUNC cuts smaller parts, sets time to zero [OK]

Common Mistakes:

• Thinking it rounds up instead of down
• Assuming it returns a string
• Believing it keeps the original timestamp unchanged

2. Which of the following is the correct syntax to truncate a timestamp to the nearest hour in PostgreSQL?

easy

A. DATE_TRUNC('hour', 13:45:30)

B. DATE_TRUNC(hour, '2024-06-15 13:45:30')

C. DATE_TRUNC('hour', 2024-06-15 13:45:30)

D. DATE_TRUNC('hour', '2024-06-15 13:45:30')

Solution

1. Step 1: Check function syntax

   DATE_TRUNC requires the first argument as a string specifying the precision, e.g., 'hour'.

2. Step 2: Validate timestamp format

   The second argument must be a timestamp or string representing a timestamp enclosed in quotes.

3. Final Answer:

   DATE_TRUNC('hour', '2024-06-15 13:45:30') -> Option D

4. Quick Check:

   Correct syntax uses quotes around precision and timestamp [OK]

Hint: Always quote the precision string in DATE_TRUNC [OK]

Common Mistakes:

• Not quoting the precision string
• Passing timestamp without quotes
• Using incomplete timestamp formats

3. Given the query:
SELECT DATE_TRUNC('day', TIMESTAMP '2024-06-15 18:23:45');
What is the output?

medium

A. 2024-06-15 18:23:45

B. 2024-06-01 00:00:00

C. 2024-06-15 00:00:00

D. 2024-06-15 23:59:59

Solution

1. Step 1: Understand 'day' precision in DATE_TRUNC

   Truncating to 'day' sets the time to midnight (00:00:00) of the same date.

2. Step 2: Apply to given timestamp

   For '2024-06-15 18:23:45', truncation results in '2024-06-15 00:00:00'.

3. Final Answer:

   2024-06-15 00:00:00 -> Option C

4. Quick Check:

   DATE_TRUNC('day', timestamp) = date at midnight [OK]

Hint: Truncating to day resets time to 00:00:00 [OK]

Common Mistakes:

• Expecting original time to remain
• Confusing day truncation with month truncation
• Thinking it rounds to nearest day

4. Identify the error in this query:
SELECT DATE_TRUNC('min', '2024-06-15 12:34:56');

medium

A. The precision 'min' is invalid in DATE_TRUNC.

B. The timestamp string is not cast to TIMESTAMP type.

C. The function DATE_TRUNC requires three arguments.

D. The timestamp string should be in single quotes.

Solution

1. Step 1: Check precision argument

   DATE_TRUNC requires the first argument to be a specific precision string (e.g., 'minute', 'hour'). 'min' is invalid.

2. Step 2: Identify the error

   This causes an error like 'invalid value for parameter "field": "min"'.

3. Final Answer:

   The precision 'min' is invalid in DATE_TRUNC. -> Option A

4. Quick Check:

   Valid precisions include 'minute' [OK]

Hint: Use exact precision strings: 'minute' not 'min' [OK]

Common Mistakes:

• Assuming string is auto-converted
• Using invalid precision values
• Adding extra arguments to DATE_TRUNC

5. You want to group sales data by week starting on Monday using DATE_TRUNC. Which query correctly truncates the timestamp to the start of the week?

hard

A. SELECT DATE_TRUNC('week', sale_date + INTERVAL '1 day') - INTERVAL '1 day' FROM sales;

B. SELECT DATE_TRUNC('dow', sale_date) FROM sales;

C. SELECT DATE_TRUNC('month', sale_date) FROM sales;

D. SELECT DATE_TRUNC('week', sale_date) FROM sales;

Solution

1. Step 1: Understand DATE_TRUNC('week') behavior

   In PostgreSQL, DATE_TRUNC('week', timestamp) returns the start of the week on Sunday.

2. Step 2: Adjust to Monday start

   Adding 1 day before truncation shifts the week start to Monday, then subtracting 1 day corrects the date.

3. Final Answer:

   SELECT DATE_TRUNC('week', sale_date + INTERVAL '1 day') - INTERVAL '1 day' FROM sales; -> Option A

4. Quick Check:

   Shift date to align week start Monday [OK]

Hint: Shift date by 1 day to start week on Monday [OK]

Common Mistakes:

• Assuming 'week' starts on Monday by default
• Using invalid precision 'dow'
• Truncating to month instead of week