PostgreSQL AGE() Date/Time Function

PostgreSQL AGE() Date/Time Function

The PostgreSQL AGE() function is used to calculate the difference between two timestamps or between a timestamp and the current date, producing an interval that represents the elapsed time in years, months, and days. This function is essential for calculating ages, durations, and time intervals in a human-readable format.

Syntax

AGE(timestamp1, timestamp2)

The AGE() function has the following components:

  • timestamp1: The later timestamp.
  • timestamp2: The earlier timestamp to subtract from timestamp1.
AGE(timestamp)

The AGE() function also has a single argument form:

  • timestamp: The timestamp to subtract from the current date (at midnight).

Example PostgreSQL AGE() Queries

Let's look at some examples of PostgreSQL AGE() function queries:

1. AGE() Between Two Timestamps

SELECT AGE(timestamp '2001-04-10', timestamp '1957-06-13') AS age_difference;

This query calculates the age difference between '2001-04-10' and '1957-06-13', resulting in '43 years 9 mons 27 days'.

2. AGE() From a Timestamp to Current Date

SELECT AGE(timestamp '1957-06-13') AS age_difference;

This query calculates the age difference between the current date (at midnight) and '1957-06-13', resulting in '62 years 6 mons 10 days'.

3. AGE() with Column Values

SELECT id, name, AGE(birthdate) AS age
FROM people;

This query retrieves the id, name, and the age calculated from the birthdate for each row in the people table.

Full Example

Let's go through a complete example that includes creating a table, inserting data, and using the AGE() function to calculate ages based on birthdates.

Step 1: Creating a Table

This step involves creating a new table named people to store people's data, including their birthdates.

CREATE TABLE people (
    id SERIAL PRIMARY KEY,
    name TEXT,
    birthdate TIMESTAMP
);

In this example, we create a table named people with columns for id, name, and birthdate.

Step 2: Inserting Data into the Table

This step involves inserting some sample data into the people table.

INSERT INTO people (name, birthdate)
VALUES ('Alice', '1980-05-15'),
       ('Bob', '1990-10-25'),
       ('Charlie', '2000-12-30');

Here, we insert data into the people table.

Step 3: Using the AGE() Function

This step involves using the AGE() function to calculate the ages based on the birthdates in the people table.

Calculate age from birthdate to current date:

SELECT id, name, AGE(birthdate) AS age
FROM people;

This query calculates the age for each person based on their birthdate and the current date.

Conclusion

The PostgreSQL AGE() function is a fundamental tool for calculating the difference between timestamps, producing an interval in years, months, and days. Understanding how to use the AGE() function and its syntax is essential for accurate time-based calculations and comparisons in PostgreSQL databases.