EXTRACT function for date parts in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Extracting the year from a timestamp

What is the output of this query?

SELECT EXTRACT(YEAR FROM TIMESTAMP '2023-11-15 08:30:00');

PostgreSQL

SELECT EXTRACT(YEAR FROM TIMESTAMP '2023-11-15 08:30:00');

A 15

B 11

C 08

D 2023

💡 Hint

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

The EXTRACT function returns the specified part of the date/time. Here, YEAR returns 2023 from the timestamp.

❓ query_result

intermediate

Extracting the month from a date

What does this query return?

SELECT EXTRACT(MONTH FROM DATE '2024-02-29');

PostgreSQL

SELECT EXTRACT(MONTH FROM DATE '2024-02-29');

A 29

B 2

C 2024

D 0

💡 Hint

MONTH extracts the month number from the date.

The EXTRACT function with MONTH returns the month number, which is 2 for February.

📝 Syntax

advanced

Identify the syntax error in EXTRACT usage

Which option contains a syntax error when using EXTRACT in PostgreSQL?

A SELECT EXTRACT(HOUR, TIMESTAMP '2023-05-10 12:00:00');

B SELECT EXTRACT(DAY FROM TIMESTAMP '2023-05-10 12:00:00');

C SELECT EXTRACT(MINUTE FROM TIMESTAMP '2023-05-10 12:00:00');

D SELECT EXTRACT(SECOND FROM TIMESTAMP '2023-05-10 12:00:00');

💡 Hint

Check the syntax: EXTRACT expects the part and source separated by FROM.

Option A uses a comma instead of FROM, which is invalid syntax for EXTRACT.

❓ query_result

advanced

Extracting fractional seconds from a timestamp

What is the output of this query?

SELECT EXTRACT(SECOND FROM TIMESTAMP '2023-07-20 14:23:45.678');

PostgreSQL

SELECT EXTRACT(SECOND FROM TIMESTAMP '2023-07-20 14:23:45.678');

A 0.678

B 678

C 45.678

D 45

💡 Hint

EXTRACT(SECOND) returns seconds including fractional part.

The EXTRACT function returns seconds with decimals, so 45.678 is correct.

🧠 Conceptual

expert

Understanding EXTRACT with time zone aware timestamps

Given the timestamp with time zone '2023-12-31 23:00:00+02', what does this query return?

SELECT EXTRACT(HOUR FROM TIMESTAMPTZ '2023-12-31 23:00:00+02');

PostgreSQL

SELECT EXTRACT(HOUR FROM TIMESTAMPTZ '2023-12-31 23:00:00+02');

A 23

B 21

C 1

D 0

💡 Hint

EXTRACT returns the hour part in the local time of the timestamp, ignoring time zone offset.

EXTRACT(HOUR) returns the hour part as stored, which is 23, not adjusted to UTC.