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

Need a 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

Need a 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

Need a 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

Need a hint?

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