DATE_TRUNC for rounding dates in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to round the timestamp to the nearest day.

PostgreSQL

SELECT DATE_TRUNC([1], '2024-06-15 13:45:30'::timestamp);

Drag options to blanks, or click blank then click option'

Amonth

Bhour

Cminute

Dday

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'hour' or 'minute' will keep time parts, not round to the day.

Using 'month' rounds to the first day of the month, not the day.

The DATE_TRUNC function rounds a timestamp to the specified precision. Using 'day' rounds to the start of the day.

2fill in blank

medium

Complete the code to round the timestamp to the nearest month.

PostgreSQL

SELECT DATE_TRUNC([1], '2024-06-15 13:45:30'::timestamp);

Drag options to blanks, or click blank then click option'

Aweek

Byear

Cmonth

Dday

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'week' rounds to the start of the week, not the month.

Using 'year' rounds to the start of the year.

Using 'month' with DATE_TRUNC rounds the timestamp to the first day of the month at midnight.

3fill in blank

hard

Fix the error in the code to correctly round the timestamp to the nearest hour.

PostgreSQL

SELECT DATE_TRUNC([1], '2024-06-15 13:45:30'::timestamp);

Drag options to blanks, or click blank then click option'

Aday

Bhour

Cminute

Dsecond

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'minute' rounds to the nearest minute, not hour.

Using 'second' rounds to the nearest second.

The correct precision for rounding to the nearest hour is 'hour'. Using other values will round differently.

4fill in blank

hard

Fill both blanks to round the timestamp to the nearest week starting on Monday.

PostgreSQL

SELECT DATE_TRUNC([1], '2024-06-15 13:45:30'::timestamp) + INTERVAL '[2] days';

Drag options to blanks, or click blank then click option'

Aweek

Bday

C1

D0

Attempts:

3 left

💡 Hint

Common Mistakes

Not adding the interval shifts the week start to Sunday.

Adding 0 days does not change the start day.

DATE_TRUNC('week', timestamp) rounds to the start of the week on Sunday. Adding 1 day shifts it to Monday.

5fill in blank

hard

Fill all three blanks to round the timestamp to the nearest quarter (3 months).

PostgreSQL

SELECT DATE_TRUNC([1], '2024-06-15 13:45:30'::timestamp) + INTERVAL '[2] months' * ((EXTRACT(MONTH FROM '2024-06-15'::date) - 1) / [3]);

Drag options to blanks, or click blank then click option'

Amonth

B3

Dquarter

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'quarter' directly in DATE_TRUNC causes an error.

Using wrong numbers for months multiplier or divisor.

Rounding to the nearest quarter involves truncating to the month, then adding months in multiples of 3. The divisor and multiplier are both 3.

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