Finding the interval between two dates in PostgreSQL is a common task that can be accomplished using the built-in AGE() function or the minus “-” operator. Both these approaches are useful for calculating the person's age, finding the duration of an event, analyzing job experience, and so on.
This tutorial will explore the usage of the Postgres’ AGE() function and the minus “-” operator via practical examples.
How to Find/Get the Interval Between Two Date Fields Via AGE() Function in Postgres?
In Postgres, AGE() is a built-in function that accepts two dates as arguments and finds the interval between the given dates. It subtracts the second timestamp/date field from the first and retrieves the resultant INTERVAL as output.
Syntax
Use the following syntax to get the services of the Postgres AGE() function:
AGE(TIMESTAMP_1, TIMESTAMP_2);
The above snippet shows that the AGE() function accepts two timestamps/dates as arguments.
Example 1: How to Calculate the Person’s Age Using AGE() Function in Postgres?
Let’s suppose we want to calculate the age of a person whose birth date is “1980-07-11”. To do that, we will execute the AGE() function as follows:
SELECT AGE(CURRENT_DATE, '1980-07-11');
In the above snippet, we pass today’s date as the first argument and the person’s birth date as the second argument. Consequently, the AGE() function will retrieve the following output:
The output shows that the AGE() function calculates the age of the selected person and retrieves an INTERVAL as output.
Example 2: How to Find Employees’ Work Experience Using the AGE() Function in Postgres?
We have already created a table named “emp_example” that contains the necessary data of all the company’s employees:
SELECT * FROM emp_example;
Let’s find the total work experience of each individual via the AGE() function:
SELECT emp_name, AGE(emp_resign_date, emp_join_date) AS total_experience FROM emp_example;
This way, you can find the interval between two dates via the Postgres’ AGE() function.
How to Get an Interval Between Two Date Fields Via the “-” Operator in Postgres?
In PostgreSQL, the minus “-” operator is used to get an interval between the two dates. Use the following syntax to get the interval via the “-” operator:
'date_1'::DATE - 'date_2'::DATE;
The above query will retrieve an integer value representing the total number of days between the specified dates.
Example 1: How to Get the Interval Between Two Dates Using “-” Operator?
Execute the following query to get the number of days between the given dates:
SELECT '2020-02-01'::DATE - '2011-11-14'::DATE;
The output shows that the total number of days between the specified dates is 3001.
Example 2: How to Use the “-” Operator on the Table’s Data?
Suppose we want to find the total duration of the published articles. For this purpose, we can use the “-” operator as follows:
SELECT (CURRENT_DATE - published_date) AS total_days FROM published_articles;
In the above snippet, we utilized the “-” operator to find the interval between the current date and the articles’ published date:
The output snippet shows that the “-” operator finds the interval between the current_date and published_date successfully.
That’s it from this blog!
Conclusion
In PostgreSQL, finding the interval between two dates can be accomplished using the built-in AGE() function or the minus “-” operator. Most users prefer the AGE() function because it retrieves detailed information regarding the interval; on the other hand, the “-” operator retrieves only the number of days. This blog post explained how to find the interval between two dates via the AGE() function or the minus “-” operator.
