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 – STATEMENT_TIMESTAMP

ByAdmin

The statement_timestamp() function is used to retrieve the timestamp when the current statement within a transaction started. Unlike transaction_timestamp(), which gives you the timestamp when the transaction started (and remains constant throughout the transaction), statement_timestamp() provides the timestamp specific to the current SQL statement being executed. This can be useful for recording when individual statements…

PostgreSQL -CLOCK_TIMESTAMP

ByAdmin

The clock_timestamp() function is used to retrieve the current timestamp as of the current transaction’s start time. It returns a timestamp with time zone that represents the exact time when the current transaction began. Here’s how you can use the clock_timestamp() function in PostgreSQL: SELECT clock_timestamp(); The above SQL statement will return the current timestamp,…