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

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

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

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

Hint

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

Practice

1. What does the PostgreSQL AGE function return when given two timestamps?

easy

A. The number of seconds between the two timestamps as an integer

B. The difference between the two timestamps as an interval showing years, months, and days

C. The sum of the two timestamps as a new timestamp

D. The earlier of the two timestamps

Solution

1. Step 1: Understand the AGE function purpose

   The AGE function calculates the time difference between two timestamps.

2. Step 2: Identify the output format

   It returns the difference as an interval showing years, months, and days.

3. Final Answer:

   The difference between the two timestamps as an interval showing years, months, and days -> Option B

4. Quick Check:

   AGE returns interval difference [OK]

Hint: AGE shows difference as years, months, days interval [OK]

Common Mistakes:

• Thinking AGE returns seconds or integer
• Confusing AGE with addition or subtraction of timestamps
• Assuming AGE returns a timestamp

2. Which of the following is the correct syntax to get the age difference between two timestamps '2023-01-01' and '2024-06-01' in PostgreSQL?

easy

A. SELECT AGE('2024-06-01', '2023-01-01');

B. SELECT AGE('2023-01-01', '2024-06-01');

C. SELECT AGE('2024-06-01'::timestamp '2023-01-01'::timestamp);

D. SELECT AGE('2023-01-01'::timestamp, '2024-06-01'::timestamp);

Solution

1. Step 1: Check AGE function parameter order

   AGE calculates the difference as first timestamp minus second timestamp, so the later date should come first.

2. Step 2: Identify correct syntax with proper order

   Using AGE('2024-06-01', '2023-01-01') returns the positive interval difference.

3. Final Answer:

   SELECT AGE('2024-06-01', '2023-01-01'); -> Option A

4. Quick Check:

   Later date first in AGE [OK]

Hint: Put later date first in AGE function [OK]

Common Mistakes:

• Reversing the order of timestamps
• Not casting strings to timestamps when needed
• Using AGE with only one parameter incorrectly

3. What is the output of this query?

SELECT AGE('2024-06-01'::date, '2023-01-01'::date);

medium

A. 1 year 5 mons 1 day

B. 1 year 6 mons 0 days

C. 1 year 4 mons 30 days

D. 1 year 5 mons 0 days

Solution

1. Step 1: Calculate difference between dates

   From 2023-01-01 to 2024-06-01 is 1 year and 5 months exactly.

2. Step 2: Understand AGE output format

   AGE returns interval as years, months, and days; days are zero here.

3. Final Answer:

   1 year 5 mons 0 days -> Option D

4. Quick Check:

   AGE returns 1 year 5 mons 0 days [OK]

Hint: Count full years and months between dates [OK]

Common Mistakes:

• Counting months incorrectly as 6 instead of 5
• Confusing days count
• Assuming AGE returns total days only

4. Identify the error in this query:

SELECT AGE('2023-01-01');

medium

A. Incorrect date format used

B. AGE function cannot accept a single argument

C. No error; AGE accepts a single argument

D. AGE requires casting to timestamp explicitly

Solution

1. Step 1: Check AGE function argument requirements

   AGE can accept one or two arguments; with one argument it returns difference from current date.

2. Step 2: Analyze the query

   The query uses one argument but does not cast it; however, AGE accepts one argument as timestamp or date.

3. Step 3: Identify actual error

   The query is valid; PostgreSQL implicitly handles the date string and computes age to current timestamp. No error.

4. Final Answer:

   No error; AGE accepts a single argument -> Option C

5. Quick Check:

   AGE needs two timestamps or one with current date [OK]

Hint: AGE with one argument uses current date [OK]

Common Mistakes:

• Assuming AGE always needs two arguments
• Thinking date format is wrong
• Believing casting is mandatory

5. You want to find the exact age difference between a person's birthdate '1990-04-15' and today, but only in years ignoring months and days. Which query correctly uses AGE and extracts only the years part?

hard

A. SELECT EXTRACT(YEAR FROM AGE(CURRENT_DATE, '1990-04-15'));

B. SELECT EXTRACT(YEAR FROM AGE('1990-04-15', CURRENT_DATE));

C. SELECT AGE(CURRENT_DATE, '1990-04-15')::year;

D. SELECT DATE_PART('year', AGE('1990-04-15', CURRENT_DATE));

Solution

1. Step 1: Use AGE with current date and birthdate

   AGE(CURRENT_DATE, '1990-04-15') returns interval from birthdate to today.

2. Step 2: Extract only the year part

   Use EXTRACT(YEAR FROM interval) to get full years ignoring months and days.

3. Final Answer:

   SELECT EXTRACT(YEAR FROM AGE(CURRENT_DATE, '1990-04-15')); -> Option A

4. Quick Check:

   EXTRACT YEAR from AGE with later date first [OK]

Hint: Use EXTRACT(YEAR FROM AGE(later, earlier)) [OK]

Common Mistakes:

• Reversing AGE argument order
• Using wrong function like DATE_PART with reversed arguments
• Trying to cast interval directly to year