DATE_TRUNC for rounding dates in PostgreSQL - Time & Space Complexity

Time Complexity: DATE_TRUNC for rounding dates

O(n)

Understanding Time Complexity

We want to understand how the time it takes to round dates using DATE_TRUNC changes as we work with more data.

How does the work grow when we apply DATE_TRUNC to many rows?

Scenario Under Consideration

Analyze the time complexity of the following code snippet.

SELECT DATE_TRUNC('month', order_date) AS month_start,
       COUNT(*) AS orders_count
FROM orders
GROUP BY month_start
ORDER BY month_start;
    

This query rounds each order date down to the first day of its month, then counts orders per month.

Identify Repeating Operations

Identify the loops, recursion, array traversals that repeat.

• Primary operation: Applying DATE_TRUNC to each row's date in the orders table.
• How many times: Once for every row in the orders table.

How Execution Grows With Input

As the number of rows grows, the database must round more dates, so work grows steadily.

Input Size (n)	Approx. Operations
10	10 date rounds
100	100 date rounds
1000	1000 date rounds

Pattern observation: The work grows directly with the number of rows; doubling rows doubles the work.

Final Time Complexity

Time Complexity: O(n)

This means the time to round dates grows in a straight line with the number of rows processed.

Common Mistake

[X] Wrong: "DATE_TRUNC runs once and applies to all rows instantly."

[OK] Correct: Each row's date must be processed separately, so the work adds up with more rows.

Interview Connect

Understanding how functions like DATE_TRUNC scale helps you explain query performance clearly and confidently.

Self-Check

"What if we added an index on the order_date column? How would that affect the time complexity of this query?"

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