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

Need a 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

Need a 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

Need a 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

Need a hint?

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