EXTRACT function for date parts in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to extract the year from the date column.

PostgreSQL

SELECT EXTRACT([1] FROM order_date) AS year FROM orders;

Drag options to blanks, or click blank then click option'

Ayear

Bmonth

Cday

Dhour

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'month' or 'day' instead of 'year' will extract the wrong part.

Forgetting to use EXTRACT syntax correctly.

The EXTRACT function extracts the specified part from a date or timestamp. Here, 'year' extracts the year part.

2fill in blank

medium

Complete the code to extract the month from the timestamp column.

PostgreSQL

SELECT EXTRACT([1] FROM created_at) AS month FROM users;

Drag options to blanks, or click blank then click option'

Aday

Bminute

Cmonth

Dyear

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'day' or 'year' will give wrong results.

Not using EXTRACT function properly.

To get the month part from a timestamp, use EXTRACT with 'month'.

3fill in blank

hard

Fix the error in the code to correctly extract the day from the date column.

PostgreSQL

SELECT EXTRACT([1] FROM order_date) AS day FROM orders;

Drag options to blanks, or click blank then click option'

Aday

Bday_of_month

Cdate

Ddays

Attempts:

3 left

💡 Hint

Common Mistakes

Using plural 'days' instead of 'day'.

Using 'date' which is not a valid part for EXTRACT.

The correct keyword to extract the day is 'day'. 'days' or 'date' are invalid in EXTRACT.

4fill in blank

hard

Fill both blanks to extract the hour and minute from the timestamp column.

PostgreSQL

SELECT EXTRACT([1] FROM login_time) AS hour, EXTRACT([2] FROM login_time) AS minute FROM sessions;

Drag options to blanks, or click blank then click option'

Ahour

Bminute

Csecond

Dday

Attempts:

3 left

💡 Hint

Common Mistakes

Mixing up hour and minute keywords.

Using invalid parts like 'day' or 'second' for these blanks.

Use 'hour' to get the hour part and 'minute' to get the minutes from a timestamp.

5fill in blank

hard

Fill all three blanks to extract year, month, and day from the birth_date column.

PostgreSQL

SELECT EXTRACT([1] FROM birth_date) AS year, EXTRACT([2] FROM birth_date) AS month, EXTRACT([3] FROM birth_date) AS day FROM people;

Drag options to blanks, or click blank then click option'

Ayear

Bmonth

Cday

Dhour

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'hour' instead of 'day' for the last blank.

Mixing the order of year, month, and day.

Use 'year', 'month', and 'day' to extract these parts from a date.

Practice

1. What does the EXTRACT function do in PostgreSQL when used with a date or timestamp?

easy

A. It adds days to the date.

B. It changes the date format to a string.

C. It retrieves a specific part like year, month, or day from the date or timestamp.

D. It deletes the time part from the timestamp.

Solution

1. Step 1: Understand the purpose of EXTRACT

   The EXTRACT function is designed to pull out specific parts such as year, month, day, hour, etc., from a date or timestamp value.

2. Step 2: Compare with other options

   Options B, C, and D describe different operations like formatting, adding days, or truncating time, which EXTRACT does not do.

3. Final Answer:

   It retrieves a specific part like year, month, or day from the date or timestamp. -> Option C

4. Quick Check:

   EXTRACT returns date parts = A [OK]

Hint: EXTRACT pulls out parts like year or month from dates [OK]

Common Mistakes:

• Thinking EXTRACT changes date format
• Confusing EXTRACT with date arithmetic
• Assuming EXTRACT removes time part

2. Which of the following is the correct syntax to extract the month from a timestamp column named created_at?

easy

A. EXTRACT(created_at, MONTH);

B. EXTRACT(MONTH FROM created_at);

C. EXTRACT('MONTH', created_at);

D. EXTRACT MONTH created_at;

Solution

1. Step 1: Recall correct EXTRACT syntax

   The correct syntax is EXTRACT(part FROM source), so to get month: EXTRACT(MONTH FROM created_at);

2. Step 2: Identify incorrect syntax forms

   Options A, B, and D use wrong argument order, quotes around part name, missing FROM keyword, or wrong punctuation.

3. Final Answer:

   EXTRACT(MONTH FROM created_at); -> Option B

4. Quick Check:

   EXTRACT(part FROM source) = C [OK]

Hint: Use EXTRACT(part FROM source) syntax exactly [OK]

Common Mistakes:

• Omitting FROM keyword
• Using quotes around part name
• Swapping argument order

3. Given the query:

SELECT EXTRACT(DAY FROM TIMESTAMP '2024-06-15 10:30:00');

What will be the output?

medium

A. 10

B. 6

C. 2024

D. 15

Solution

1. Step 1: Identify the part extracted

   The query extracts the DAY part from the timestamp '2024-06-15 10:30:00'. The day is 15.

2. Step 2: Confirm output value

   Other options represent month (6), year (2024), and hour (10), which are not extracted here.

3. Final Answer:

   15 -> Option D

4. Quick Check:

   EXTRACT(DAY) = 15 [OK]

Hint: Match EXTRACT part with date component value [OK]

Common Mistakes:

• Confusing day with month or year
• Expecting full date output
• Ignoring time part in timestamp

4. What is wrong with this query?

SELECT EXTRACT('YEAR' FROM CURRENT_DATE);

medium

A. The part name YEAR should not be in quotes.

B. CURRENT_DATE cannot be used with EXTRACT.

C. Missing parentheses around CURRENT_DATE.

D. EXTRACT requires two arguments separated by a comma.

Solution

1. Step 1: Check syntax for EXTRACT part

   The part name like YEAR should be unquoted. Quotes cause syntax error.

2. Step 2: Verify other parts of query

   CURRENT_DATE is valid input, no parentheses needed, and EXTRACT uses FROM keyword, not comma.

3. Final Answer:

   The part name YEAR should not be in quotes. -> Option A

4. Quick Check:

   Part names unquoted in EXTRACT [OK]

Hint: Do not put quotes around date parts in EXTRACT [OK]

Common Mistakes:

• Using quotes around part names
• Adding commas instead of FROM
• Thinking CURRENT_DATE needs parentheses

5. You want to find all records from a table orders where the order was placed in the first quarter (January to March) of any year. Which query correctly uses EXTRACT to filter these records?

hard

A. SELECT * FROM orders WHERE EXTRACT(QUARTER FROM order_date) = 1;

B. SELECT * FROM orders WHERE EXTRACT('MONTH' FROM order_date) < 4;

C. SELECT * FROM orders WHERE EXTRACT(MONTH, order_date) IN (1, 2, 3);

D. SELECT * FROM orders WHERE EXTRACT(MONTH order_date) BETWEEN 1 AND 3;

Solution

1. Step 1: Understand quarter extraction

   PostgreSQL supports EXTRACT(QUARTER FROM date) which returns 1 for Jan-Mar, 2 for Apr-Jun, etc.

2. Step 2: Evaluate each option

   Options A (missing FROM), B (quotes around MONTH), and C (comma instead of FROM) have syntax errors. SELECT * FROM orders WHERE EXTRACT(QUARTER FROM order_date) = 1; correctly uses EXTRACT(QUARTER FROM order_date) = 1.

3. Final Answer:

   SELECT * FROM orders WHERE EXTRACT(QUARTER FROM order_date) = 1; -> Option A

4. Quick Check:

   Use EXTRACT(QUARTER) = 1 for first quarter [OK]

Hint: Use EXTRACT(QUARTER) = 1 for first quarter filtering [OK]

Common Mistakes:

• Missing FROM keyword
• Using quotes around part names
• Using comma instead of FROM
• Not knowing EXTRACT supports QUARTER