PostgreSQL – AGE Function

ByAdmin

In PostgreSQL, the AGE() function is used to calculate the difference between two dates or timestamps and returns the result as an interval. The interval represents the duration between the two dates in years, months, days, and fractional seconds.

The basic syntax of the AGE() function is as shown below:

AGE(end_date, start_date);

where end_date is the later date or timestamp,

and start_date is the earlier date or timestamp.

Below is an example of using the AGE() function to calculate the age difference between two dates:

SELECT AGE('2023-08-06', '1990-03-15') AS age_difference;

This query will return the difference between the two dates as an interval, such as 33 years 4 months 22 days. The result indicates that the duration between the start date (‘1990-03-15’) and end date (‘2023-08-06’) is 33 years, 4 months, and 22 days.

You can also use the AGE() function with timestamps as below:

SELECT AGE('2023-08-06 15:30:00', '2023-08-05 12:45:30') AS time_difference;

In this example, the AGE() function will return the difference between the two timestamps.

AGE() function can be used with the current timestamp obtained from the NOW() function to calculate the age of a date relative to the current date:

SELECT AGE(NOW(), '1990-03-15') AS age_relative_to_current_date;

This query will give you the age of the date ‘1990-03-15’ relative to the current date and time.

The result of the AGE() function is an interval data type. If you want to extract specific components (e.g., years, months, days) from the interval, you can use the appropriate date and time functions like EXTRACT(), DATE_PART(), or arithmetic operations with the interval.

SELECT EXTRACT(YEAR FROM AGE('2023-08-06', '1990-03-15')) AS years,
       EXTRACT(MONTH FROM AGE('2023-08-06', '1990-03-15')) AS months,
       EXTRACT(DAY FROM AGE('2023-08-06', '1990-03-15')) AS days;

These examples will extract the years, months, and days components from the interval returned by the AGE() function.

Similar Posts

PostgreSQL – LOCALTIMESTAMP

ByAdmin

The localtimestamp function is used to retrieve the current timestamp according to the time zone setting of your database session. This function returns the current date and time as a timestamp with time zone. Here’s how you can use localtimestamp in PostgreSQL: SELECT localtimestamp; The above SQL statement will return the current timestamp based on…