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.