AGE function for differences in PostgreSQL - Mini Project: Build & Apply

Calculate Age Differences Using AGE Function in PostgreSQL

📖 Scenario: You work in a human resources department. You have a table of employees with their birth dates and hire dates. You want to find out how many years, months, and days each employee has been working at the company compared to their age.

🎯 Goal: Create a PostgreSQL query that uses the AGE function to calculate the difference between two dates: the employee's hire date and birth date, and also between the current date and the birth date.

📋 What You'll Learn

Create a table called employees with columns id (integer), name (text), birth_date (date), and hire_date (date).

Insert three employees with exact birth and hire dates.

Write a SELECT query that uses the AGE function to calculate the age of each employee as of today.

Write a SELECT query that uses the AGE function to calculate the time difference between the hire date and birth date for each employee.

💡 Why This Matters

🌍 Real World

HR departments often need to calculate employee ages and tenure to manage benefits and compliance.

💼 Career

Knowing how to use date functions like AGE in SQL is essential for data analysts and database administrators working with time-based data.

1

Create the employees table and insert data

Create a table called employees with columns id (integer), name (text), birth_date (date), and hire_date (date). Then insert these exact rows: (1, 'Alice', '1985-04-12', '2010-06-01'), (2, 'Bob', '1990-09-23', '2015-08-15'), and (3, 'Charlie', '1978-12-05', '2000-01-20').

PostgreSQL

-- Create the employees table and insert the three rows below
-- Your code here

Need a hint?

Use CREATE TABLE to define the table and INSERT INTO to add the rows with exact values.

2

Add a configuration variable for the current date

Create a variable called current_date and set it to the current date using CURRENT_DATE in a WITH clause to use later in queries.

PostgreSQL

CREATE TABLE employees (
    id INTEGER,
    name TEXT,
    birth_date DATE,
    hire_date DATE
);

INSERT INTO employees (id, name, birth_date, hire_date) VALUES
(1, 'Alice', '1985-04-12', '2010-06-01'),
(2, 'Bob', '1990-09-23', '2015-08-15'),
(3, 'Charlie', '1978-12-05', '2000-01-20');

-- Create a WITH clause named current_date with CURRENT_DATE
-- Your code here

Need a hint?

Use a WITH clause to define current_date as a subquery returning CURRENT_DATE as today.

3

Write a query to calculate each employee's age using AGE function

Write a SELECT query that uses AGE(current_date.today, employees.birth_date) to calculate the age of each employee. Join the employees table with the current_date CTE. Select name and the calculated age as age.

PostgreSQL

CREATE TABLE employees (
    id INTEGER,
    name TEXT,
    birth_date DATE,
    hire_date DATE
);

INSERT INTO employees (id, name, birth_date, hire_date) VALUES
(1, 'Alice', '1985-04-12', '2010-06-01'),
(2, 'Bob', '1990-09-23', '2015-08-15'),
(3, 'Charlie', '1978-12-05', '2000-01-20');

WITH current_date AS (
    SELECT CURRENT_DATE AS today
)
-- Write a SELECT query joining employees and current_date
-- Calculate age using AGE(current_date.today, employees.birth_date)
-- Select name and age
-- Your code here

Need a hint?

Use a comma join between employees and current_date. Use AGE() with current_date.today and employees.birth_date.

4

Write a query to calculate the difference between hire date and birth date

Write a SELECT query that uses AGE(employees.hire_date, employees.birth_date) to calculate the time difference between hire date and birth date for each employee. Select name and this difference as time_at_company.

PostgreSQL

CREATE TABLE employees (
    id INTEGER,
    name TEXT,
    birth_date DATE,
    hire_date DATE
);

INSERT INTO employees (id, name, birth_date, hire_date) VALUES
(1, 'Alice', '1985-04-12', '2010-06-01'),
(2, 'Bob', '1990-09-23', '2015-08-15'),
(3, 'Charlie', '1978-12-05', '2000-01-20');

WITH current_date AS (
    SELECT CURRENT_DATE AS today
)
SELECT employees.name, AGE(current_date.today, employees.birth_date) AS age
FROM employees, current_date;

-- Write a SELECT query to get name and AGE(hire_date, birth_date) as time_at_company
-- Your code here

Need a hint?

Use a simple SELECT from employees and apply AGE() with hire_date and birth_date.