Date, time, and timestamp types in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the output of this timestamp extraction?

Given the table events with a column event_time of type timestamp, what is the result of this query?

SELECT EXTRACT(HOUR FROM event_time) AS hour FROM events WHERE event_time = '2024-06-15 14:45:30';

PostgreSQL

CREATE TABLE events (event_time timestamp);
INSERT INTO events VALUES ('2024-06-15 14:45:30');
SELECT EXTRACT(HOUR FROM event_time) AS hour FROM events WHERE event_time = '2024-06-15 14:45:30';

A 14

B 15

C 30

D 2024

💡 Hint

EXTRACT(HOUR FROM timestamp) returns the hour part of the time.

The EXTRACT function with HOUR returns the hour component from the timestamp. Here, the time is 14:45:30, so the hour is 14.

🧠 Conceptual

intermediate

Which data type stores only the time of day without date?

In PostgreSQL, which data type should you use to store only the time of day, without any date information?

A time

B date

C interval

D timestamp

💡 Hint

Think about what stores hours, minutes, and seconds but no day or year.

The time data type stores only the time of day (hours, minutes, seconds) without any date.

📝 Syntax

advanced

Which query correctly adds 3 days to a timestamp?

You want to add 3 days to a timestamp column created_at. Which of these queries is syntactically correct in PostgreSQL?

A SELECT created_at + 3 DAY FROM orders;

B SELECT created_at + '3 days' FROM orders;

C SELECT created_at + INTERVAL 3 DAY FROM orders;

D SELECT created_at + INTERVAL '3 days' FROM orders;

💡 Hint

PostgreSQL uses INTERVAL with a string literal for time intervals.

Option D uses the correct syntax: INTERVAL '3 days'. Options A and C are invalid syntax. Option D tries to add a string directly, which causes an error.

🔧 Debug

advanced

Why does this query raise an error?

Consider this query:

SELECT event_date + event_time FROM schedule;

where event_date is of type date and event_time is of type time. Why does it raise an error?

A The column event_time is not allowed in arithmetic operations.

B The table schedule does not exist.

C You cannot add date and time types directly; you must cast one to timestamp.

D The query is missing a WHERE clause.

💡 Hint

Think about how PostgreSQL handles adding date and time types.

PostgreSQL does not allow direct addition of date and time types. You must cast the date to timestamp or combine them properly.

❓ optimization

expert

How to optimize filtering by date ignoring time in a timestamp column?

You have a large table logs with a created_at column of type timestamp. You want to find all rows from '2024-06-01' ignoring the time part. Which query is the most efficient for this filter?

A SELECT * FROM logs WHERE DATE_TRUNC('day', created_at) = '2024-06-01';

B SELECT * FROM logs WHERE created_at >= '2024-06-01 00:00:00' AND created_at < '2024-06-02 00:00:00';

C SELECT * FROM logs WHERE created_at::date = '2024-06-01';

D SELECT * FROM logs WHERE TO_CHAR(created_at, 'YYYY-MM-DD') = '2024-06-01';

💡 Hint

Consider how indexes work with range queries versus functions on columns.

Option B uses a range condition that can use an index on created_at efficiently. Options B, C, and D apply functions on the column, which prevents index use and slows queries.