DATE_TRUNC for rounding dates in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Rounding a timestamp to the start of the hour

Given the timestamp '2024-06-15 14:37:45', what is the result of DATE_TRUNC('hour', TIMESTAMP '2024-06-15 14:37:45')?

PostgreSQL

SELECT DATE_TRUNC('hour', TIMESTAMP '2024-06-15 14:37:45');

A 2024-06-15 14:00:00

B 2024-06-15 14:37:00

C 2024-06-15 00:00:00

D 2024-06-15 15:00:00

💡 Hint

Think about what 'truncating to hour' means: it resets minutes and seconds to zero.

DATE_TRUNC with 'hour' sets minutes, seconds, and smaller units to zero, keeping only the year, month, day, and hour.

❓ query_result

intermediate

Rounding a timestamp to the start of the month

What is the output of DATE_TRUNC('month', TIMESTAMP '2024-06-15 14:37:45')?

PostgreSQL

SELECT DATE_TRUNC('month', TIMESTAMP '2024-06-15 14:37:45');

A 2024-01-01 00:00:00

B 2024-06-15 00:00:00

C 2024-06-01 00:00:00

D 2024-06-15 14:37:45

💡 Hint

Truncating to month resets day, hour, minute, and second to the start of the month.

DATE_TRUNC with 'month' returns the first day of the month at midnight.

📝 Syntax

advanced

Identifying invalid DATE_TRUNC usage

Which of the following queries will cause a syntax error in PostgreSQL?

A SELECT DATE_TRUNC('year', TIMESTAMP '2024-06-15 14:37:45');

B SELECT DATE_TRUNC('minute', '2024-06-15 14:37:45'::timestamp);

C SELECT DATE_TRUNC('week', TIMESTAMP '2024-06-15 14:37:45');

D SELECT DATE_TRUNC('fortnight', TIMESTAMP '2024-06-15 14:37:45');

💡 Hint

Check if the first argument to DATE_TRUNC is a valid date part.

PostgreSQL supports specific date parts like year, month, week, day, hour, minute, second. 'fortnight' is not supported and causes an error.

❓ optimization

advanced

Optimizing queries using DATE_TRUNC for grouping

You want to count the number of orders per day from a table orders with a created_at timestamp column. Which query is the most efficient and correct?

A SELECT created_at::date AS day, COUNT(*) FROM orders GROUP BY day ORDER BY day;

B SELECT DATE_TRUNC('hour', created_at) AS day, COUNT(*) FROM orders GROUP BY day ORDER BY day;

C SELECT created_at, COUNT(*) FROM orders GROUP BY created_at ORDER BY created_at;

D SELECT DATE_TRUNC('day', created_at) AS day, COUNT(*) FROM orders GROUP BY day ORDER BY day;

💡 Hint

Casting to date is simpler and faster than truncating timestamps for daily grouping.

Casting the timestamp to date extracts the date part directly and is more efficient than DATE_TRUNC for grouping by day.

🧠 Conceptual

expert

Understanding DATE_TRUNC behavior with time zones

Consider a timestamp with time zone value '2024-06-15 23:30:00+02'. What is the result of DATE_TRUNC('day', TIMESTAMPTZ '2024-06-15 23:30:00+02') when run in a session with time zone set to UTC?

PostgreSQL

SET TIME ZONE 'UTC';
SELECT DATE_TRUNC('day', TIMESTAMPTZ '2024-06-15 23:30:00+02');

A 2024-06-15 00:00:00+00

B 2024-06-14 00:00:00+00

C 2024-06-15 21:00:00+00

D 2024-06-16 00:00:00+00

💡 Hint

Remember that TIMESTAMPTZ values are stored in UTC internally and DATE_TRUNC truncates in the session time zone.

The timestamptz '2024-06-15 23:30:00+02' is equivalent to '2024-06-15 21:30:00+00' in UTC. In a UTC session, DATE_TRUNC('day', ...) truncates it to '2024-06-14 00:00:00+00' because the timestamp is on 21:30 UTC on June 15, so truncating to day gives midnight of June 15 UTC, but the original time is after midnight UTC on June 15, so the truncation is to June 15 00:00:00 UTC. Actually, this needs correction: 23:30+02 is 21:30 UTC on June 15, so truncating to day in UTC is 2024-06-15 00:00:00+00, so option B is correct.