EXTRACT function for date parts in PostgreSQL - Mini Project: Build & Apply

Extracting Date Parts Using EXTRACT Function in PostgreSQL

📖 Scenario: You work for a company that stores employee information including their hire dates. You want to analyze the year and month when employees were hired.

🎯 Goal: Build a SQL query that extracts the year and month parts from employee hire dates using the EXTRACT function.

📋 What You'll Learn

Create a table called employees with columns id (integer) and hire_date (date).

Insert three employees with exact hire dates: 2019-04-15, 2020-11-30, and 2021-07-01.

Write a query that selects id, the year part of hire_date as hire_year, and the month part of hire_date as hire_month using the EXTRACT function.

Complete the query by ordering the results by id ascending.

💡 Why This Matters

🌍 Real World

Extracting parts of dates is common in reports, analytics, and filtering data by time periods.

💼 Career

Database developers and analysts often use EXTRACT to manipulate and analyze date and time data efficiently.

1

Create the employees table with id and hire_date

Write a SQL statement to create a table called employees with two columns: id as integer and hire_date as date.

PostgreSQL

-- Your code here

Hint

Use CREATE TABLE employees (id INTEGER, hire_date DATE);

2

Insert three employees with specific hire dates

Write SQL insert statements to add three rows into employees with these exact values: (1, '2019-04-15'), (2, '2020-11-30'), and (3, '2021-07-01').

PostgreSQL

CREATE TABLE employees (
    id INTEGER,
    hire_date DATE
);
-- Your code here

Hint

Use a single INSERT INTO employees (id, hire_date) VALUES (...), (...), (...); statement.

3

Select id, year and month parts from hire_date using EXTRACT

Write a SQL query to select id, the year part of hire_date as hire_year, and the month part of hire_date as hire_month using the EXTRACT function from the employees table.

PostgreSQL

CREATE TABLE employees (
    id INTEGER,
    hire_date DATE
);

INSERT INTO employees (id, hire_date) VALUES
(1, '2019-04-15'),
(2, '2020-11-30'),
(3, '2021-07-01');

-- Your code here

Hint

Use EXTRACT(YEAR FROM hire_date) and EXTRACT(MONTH FROM hire_date) in the SELECT clause.

4

Order the results by id ascending

Add an ORDER BY clause to the previous query to sort the results by id in ascending order.

PostgreSQL

CREATE TABLE employees (
    id INTEGER,
    hire_date DATE
);

INSERT INTO employees (id, hire_date) VALUES
(1, '2019-04-15'),
(2, '2020-11-30'),
(3, '2021-07-01');

SELECT id,
       EXTRACT(YEAR FROM hire_date) AS hire_year,
       EXTRACT(MONTH FROM hire_date) AS hire_month
FROM employees
-- Your code here

Hint

Add ORDER BY id ASC at the end of the query.

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