AGE function for differences in PostgreSQL - Step-by-Step Execution

Concept Flow - AGE function for differences

Input: two dates

↓

Calculate difference

↓

Return interval result

↓

Output age as years, months, days

The AGE function takes two dates and calculates the difference between them, returning the result as an interval showing years, months, and days.

Execution Sample

PostgreSQL

SELECT AGE('2024-06-15', '2020-01-10');

Calculates the difference between June 15, 2024 and January 10, 2020 as an interval.

Execution Table

Step	Input Dates	Action	Intermediate Result	Output
1	'2024-06-15', '2020-01-10'	Parse dates	Dates recognized	N/A
2	Dates parsed	Calculate difference	4 years, 5 months, 5 days	N/A
3	Difference calculated	Format as interval	Interval type	4 years 5 mons 5 days
4	Result formatted	Return output	Final interval	4 years 5 mons 5 days

💡 Completed calculation and returned interval representing age difference.

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	Final
date1	N/A	2024-06-15	2024-06-15	2024-06-15	2024-06-15
date2	N/A	2020-01-10	2020-01-10	2020-01-10	2020-01-10
difference	N/A	N/A	4 years 5 mons 5 days	4 years 5 mons 5 days	4 years 5 mons 5 days

Key Moments - 2 Insights

Why does AGE return an interval instead of just a number?

AGE returns an interval to show the difference in years, months, and days clearly, as seen in execution_table step 3 where the result is formatted as an interval.

What happens if the first date is earlier than the second date?

AGE will return a negative interval showing the difference in reverse order, but the calculation steps remain the same as shown in execution_table.

Visual Quiz - 3 Questions

Look at the execution_table, what is the output after step 3?

A Dates recognized

B 4 years 5 mons 5 days

C Final interval

D N/A

💡 Hint

Check the 'Output' column in row for step 3 in execution_table.

At which step is the difference between the two dates calculated?

A Step 1

B Step 3

C Step 2

D Step 4

💡 Hint

Look at the 'Action' column in execution_table to find when calculation happens.

If date1 was '2019-01-10' and date2 was '2020-06-15', how would the difference appear?

A -1 year 5 mons 5 days

B 1 year 5 mons 5 days

C 4 years 5 mons 5 days

D -4 years 5 mons 5 days

💡 Hint

AGE returns negative interval if first date is earlier, check variable_tracker for date order.

Concept Snapshot

AGE(date1, date2) returns the interval difference between two dates.
Result shows years, months, and days.
If date1 is earlier, result is negative interval.
Useful for calculating age or time spans.
Returns type: interval.

Full Transcript

The AGE function in PostgreSQL calculates the difference between two dates and returns it as an interval showing years, months, and days. It first parses the input dates, then calculates the difference, formats it as an interval, and finally returns the result. The output helps understand the exact time span between two dates, useful for age calculations. If the first date is earlier than the second, the result is a negative interval. This step-by-step execution shows how the function processes inputs and produces the output interval.