Why date handling matters in PostgreSQL - Challenge Your Understanding

Challenge - 5 Problems

❓ query_result

intermediate

What is the output of this date extraction query?

Consider the following PostgreSQL query that extracts the year from a date column. What will be the output?

PostgreSQL

SELECT EXTRACT(YEAR FROM DATE '2023-06-15') AS year;

A Error: invalid syntax

B 06

C 2023

D 15

💡 Hint

EXTRACT gets a part of the date, like year, month, or day.

The EXTRACT function returns the specified part of the date. Here, it returns the year 2023.

🧠 Conceptual

intermediate

Why is time zone important in PostgreSQL date handling?

Which of the following best explains why handling time zones correctly is important in PostgreSQL?

A Because time zones affect how timestamps are stored and displayed, preventing confusion across regions.

B Because dates without time zones are always stored as UTC internally.

C Because PostgreSQL does not support time zones at all.

D Because time zones only matter for date columns, not timestamp columns.

💡 Hint

Think about users in different parts of the world viewing the same timestamp.

PostgreSQL stores timestamps with or without time zone differently. Time zones ensure correct display and calculations across regions.

📝 Syntax

advanced

Which query correctly adds 1 month to a date in PostgreSQL?

You want to add exactly one month to the date '2023-01-31'. Which query will correctly do this without error?

A SELECT '2023-01-31'::date + INTERVAL '1 month';

B SELECT DATE '2023-01-31' + INTERVAL '1 month';

C SELECT DATE '2023-01-31' + 1 MONTH;

D SELECT '2023-01-31' + INTERVAL '1 month';

💡 Hint

Casting to date and using INTERVAL is key.

Options A and B correctly cast the string to a date and add an interval of one month. Option C uses invalid syntax. Option D tries to add interval to a string without casting, causing error.

❓ optimization

advanced

How to optimize queries filtering by date range on large tables?

You have a large table with a timestamp column. Which approach optimizes filtering rows between two dates?

A Use a function on the timestamp column in WHERE clause, e.g., WHERE DATE(timestamp_col) = '2023-06-01'

B Use a range condition directly on the timestamp column, e.g., WHERE timestamp_col >= '2023-06-01' AND timestamp_col < '2023-06-02'

C Cast the timestamp to text and compare strings in WHERE clause

D Use OR conditions for each date separately, e.g., WHERE timestamp_col = '2023-06-01' OR timestamp_col = '2023-06-02'

💡 Hint

Avoid wrapping columns in functions in WHERE clauses for performance.

Using direct range conditions allows PostgreSQL to use indexes efficiently. Applying functions on columns disables index use and slows queries.

🔧 Debug

expert

Why does this query return unexpected results with time zones?

Given the table events(event_time TIMESTAMP WITH TIME ZONE), why does this query return no rows? SELECT * FROM events WHERE event_time::date = '2023-06-15';

A Because casting TIMESTAMP WITH TIME ZONE to date converts using the session time zone, causing mismatch with local dates.

B Because the date literal '2023-06-15' is invalid format.

C Because event_time column does not support casting to date.

D Because the query syntax is incorrect; casting requires parentheses.

💡 Hint

Think about how time zones affect date extraction from timestamps.

Casting TIMESTAMP WITH TIME ZONE to date uses the session time zone, so local times on '2023-06-15' may fall on a different date, causing no matches.