DATE_TRUNC for rounding dates in PostgreSQL - Step-by-Step Execution

Concept Flow - DATE_TRUNC for rounding dates

Input: timestamp

↓

Choose precision: year, month, day, hour, etc.

↓

DATE_TRUNC function applies

↓

Output: timestamp rounded down to chosen precision

DATE_TRUNC takes a timestamp and rounds it down to the specified precision like year, month, or day.

Execution Sample

PostgreSQL

SELECT DATE_TRUNC('month', TIMESTAMP '2024-06-15 13:45:30');

This query rounds the timestamp down to the start of the month.

Execution Table

Step	Input Timestamp	Precision	Action	Output Timestamp
1	2024-06-15 13:45:30	month	Round down to start of month	2024-06-01 00:00:00
2	2024-06-15 13:45:30	day	Round down to start of day	2024-06-15 00:00:00
3	2024-06-15 13:45:30	hour	Round down to start of hour	2024-06-15 13:00:00
4	2024-06-15 13:45:30	year	Round down to start of year	2024-01-01 00:00:00
5	2024-06-15 13:45:30	minute	Round down to start of minute	2024-06-15 13:45:00
6	2024-06-15 13:45:30	second	Round down to start of second	2024-06-15 13:45:30
7	2024-06-15 13:45:30	month	End of examples	Execution stops

💡 All examples shown; DATE_TRUNC rounds down timestamp to chosen precision.

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	After Step 4	After Step 5	After Step 6	Final
Input Timestamp	2024-06-15 13:45:30	2024-06-15 13:45:30	2024-06-15 13:45:30	2024-06-15 13:45:30	2024-06-15 13:45:30	2024-06-15 13:45:30	2024-06-15 13:45:30	2024-06-15 13:45:30
Precision	month	month	day	hour	year	minute	second	month
Output Timestamp	N/A	2024-06-01 00:00:00	2024-06-15 00:00:00	2024-06-15 13:00:00	2024-01-01 00:00:00	2024-06-15 13:45:00	2024-06-15 13:45:30	N/A

Key Moments - 3 Insights

Why does DATE_TRUNC('month', timestamp) set day and time to zero?

Because DATE_TRUNC rounds down to the start of the month, it resets day to 1 and time to 00:00:00 as shown in execution_table step 1.

Does DATE_TRUNC round up or down?

DATE_TRUNC always rounds down (truncates) to the start of the specified precision, never up, as seen in all output timestamps in execution_table.

What happens if you use 'second' precision on a timestamp with seconds?

The output stays the same because seconds are the smallest unit shown, so no change occurs, as in execution_table step 6.

Visual Quiz - 3 Questions

Look at the execution_table, what is the output timestamp at step 3 when precision is 'hour'?

A 2024-06-01 00:00:00

B 2024-06-15 00:00:00

C 2024-06-15 13:00:00

D 2024-06-15 13:45:30

💡 Hint

Check the 'Output Timestamp' column in execution_table row for step 3.

At which step does the output timestamp become '2024-01-01 00:00:00'?

A Step 2

B Step 4

C Step 5

D Step 6

💡 Hint

Look for the 'year' precision in the execution_table and its output timestamp.

If you change the precision from 'month' to 'day' in step 1, what would the output timestamp be?

A 2024-06-15 00:00:00

B 2024-06-01 00:00:00

C 2024-06-15 13:45:30

D 2024-01-01 00:00:00

💡 Hint

Refer to execution_table step 2 where precision is 'day' and see the output.

Concept Snapshot

DATE_TRUNC('precision', timestamp)
Rounds timestamp down to start of given precision.
Common precisions: year, month, day, hour, minute, second.
Output resets smaller units to zero.
Useful for grouping or truncating dates.

Full Transcript

DATE_TRUNC is a PostgreSQL function that rounds a timestamp down to a specified precision like year, month, or day. For example, DATE_TRUNC('month', '2024-06-15 13:45:30') returns '2024-06-01 00:00:00'. It always truncates down, resetting smaller units to zero. This is helpful when you want to group or compare dates at a certain level of detail. The execution table shows how the output changes with different precisions, confirming the truncation behavior.

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