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.