Database management systems, such as SQL Server, Oracle, etc., utilize the DATEDIFF function to find the difference between given Dates. However, Postgres doesn’t support the DATEDIFF function. So, how to find the DateTime difference in Postgres?
Well! In PostgreSQL, the “-” operator, DATE_PART(), EXTRACT(), and AGE() functions are used to calculate the difference between various DateTime values.
This post presents a comprehensive guide on calculating the difference between different dates, times, timestamps, and intervals using examples.
How to Find DateTime Difference in Postgres Via the DATE_PART() Function?
The DATE_PART() function is used in Postgres to get a specific field from a date. So, it can be used with the “-” operator to find the difference between the given DateTime values.
This section demonstrates how to use the DATE_PART() function to find date differences in:
- Year
- Month
- Week
- Day
- Hour
- Minute
- Second
Date Difference in Years
To find the DateTime difference in “Years” using DATE_PART() function, use the below syntax:
DATE_PART('YEAR', end_date) - DATE_PART('YEAR', start_date);Where the “start_date” and “end_date” can be “Timestamps”, “Dates”, “Intervals”, etc.
Example: Calculating the Date Difference in Years
The DATE_PART() is utilized in the following code snippet to calculate the difference between the given dates in “years”:
SELECT
DATE_PART('YEAR', '2023-01-01' :: DATE) -
DATE_PART('YEAR', '2019-06-01' :: DATE) AS year_diff;
The output successfully retrieves the date difference in years.
Date Difference in Months
With DATE_PART() function, you can calculate the DateTime difference in "Month" as follows:
(datediff_in_years) *12 + (DATE_PART('Month', end_date) - DATE_PART('Month', start_date));From the above syntax, you can notice that to find the date difference in months, first, you need to find the date difference in years. Multiply the “date difference in years” with “12” and add it to the “date difference in months” to find the precise date difference in months.
Example: Calculating the Date Difference in Months
The below snippet demonstrates how to find the date difference in “Month” using the DATE_PART() function:
SELECT
(DATE_PART('YEAR', '2023-01-01' :: DATE) -
DATE_PART('YEAR', '2020-04-01' :: DATE)) * 12
+ (DATE_PART('Month', '2023-01-01' :: DATE) -
DATE_PART('Month', '2020-04-01' :: DATE)) AS month_diff;
The output successfully retrieves the date difference in months.
Date Difference in Days
To get the date difference in days, use the following syntax:
DATE_PART('Day', end_date - start_date);Here is an example code:
Example: Calculating Date Difference in Days
In the following snippet, the DATE_PART() function is used to find the date difference in “Days”:
SELECT
DATE_PART('Day', '2023-02-01'::TIMESTAMP - '2023-01-10'::TIMESTAMP) AS day_diff;
The output snippet demonstrates that the date difference is “22” days.
Date Difference in Weeks
Divide the “date difference in days by 7” and wrap it within TRUNC() function to find the date difference in weeks, as shown below:
TRUNC(DATE_PART('Day', end_date - start_date)/7);Here, the TRUNC() function is used to trim the floating points from the weeks' difference.
Example: Calculating Date Difference in Weeks
In the below code snippet, the DATE_PART() function is used along with the TRUNC() function to get the date difference in weeks:
SELECT
TRUNC(DATE_PART('Day', '2023-02-01'::TIMESTAMP - '2023-01-10'::TIMESTAMP)/7) AS week_diff;
The output demonstrates that the difference between the input dates is “3” weeks.
Date Difference in Hours
To find the date difference in hours from the given DateTime values, you must follow the below-provided steps:
- First, find the “date_diff” in days and multiply it with “24”.
- Calculate the “date_diff” in hours and add it with the “date_diff in days”.
The below syntax will help you understand this concept better:
(DATE_PART('Day', end_date - start_date)) * 24 +
(DATE_PART('Hour', end_date) - DATE_PART('Hour', start_date));Example: Calculating Date Difference in Hours
Let’s learn how to find the date difference in hours using the “DATE_PART()” function:
SELECT
DATE_PART('Day', '2023-01-03 04:10:00'::TIMESTAMP - '2023-01-02 03:15:00'::TIMESTAMP) * 24
+ (DATE_PART('Hour', '2023-01-03 04:10:00'::TIMESTAMP) -
DATE_PART('Hour', '2023-01-02 03:15:00'::TIMESTAMP)) AS hour_diff;
The output shows that the difference between the given TIMESTAMPS is “25” hours.
Date Difference in Minutes
To find the date difference in minutes from the given DateTime values, use the following steps:
- First, find the “date_diff” in days and multiply it with “24”.
- Find the “date_diff” in hours and multiply it by “60”.
- Find the “date_diff” in minutes and add it with the “date_diff” in hours and the “date_ diff” in days.
Here is the basic syntax:
(DATE_PART('Day', end_date - start_date)) * 24 +
(DATE_PART('Hour', end_date start_date)) * 60 +
(DATE_PART('Minute', end_date - start_date));Let’s put this concept into practice.
Example: Calculating Date Difference in Minutes
In this example, the DATE_PART() function is applied on two different TIMESTAMPS to find the data difference in minutes:
SELECT
((DATE_PART('Day', '2023-01-03 04:10:00'::TIMESTAMP - '2023-01-02 03:15:00'::TIMESTAMP) * 24 +
DATE_PART('Hour', '2023-01-03 04:10:00'::TIMESTAMP - '2023-01-02 03:15:00'::TIMESTAMP)) * 60 +
DATE_PART('Minute', '2023-01-03 04:10:00'::TIMESTAMP - '2023-01-02 03:15:00'::TIMESTAMP));
The difference between the given TIMESTAMPS is “1495” minutes.
Date Difference in Seconds
To find the date difference in seconds from the specific DateTime values, you must follow the following instructions:
- First, find the “date_diff” in days and multiply it with “24”.
- Calculate the “date diff” in hours and minutes and multiply them by “60”.
- Find the “date_diff” in seconds and add it with the “date_diff” in hours, days, and minutes.
Here is the syntax:
(DATE_PART('Day', end_date - start_date)) * 24 +
(DATE_PART('Hour', end_date) - DATE_PART('Hour', start_date)) * 60 +
(DATE_PART('Minute', end_date - start_date)) *60 +
DATE_PART('Second', end_date - start_date);Example: Calculating Date Difference in Seconds
Using DATE_PART(), the below example calculates the date difference in seconds:
SELECT
((DATE_PART('Day', '2023-01-03 04:10:00'::TIMESTAMP - '2023-01-02 03:15:00'::TIMESTAMP) * 24 +
DATE_PART('Hour', '2023-01-03 04:10:00'::TIMESTAMP - '2023-01-02 03:15:00'::TIMESTAMP)) * 60 +
DATE_PART('Minute', '2023-01-03 04:10:00'::TIMESTAMP - '2023-01-02 03:15:00'::TIMESTAMP)) * 60
+ DATE_PART('Second', '2023-01-03 04:10:00'::TIMESTAMP - '2023-01-02 03:15:00'::TIMESTAMP)
AS sec_diff;
The above snippet demonstrates that the difference between the provided TIMESTAMPS is “89700” seconds.
How to Find DateTime Difference in Postgres Via the EXTRACT() Function?
The EXTRACT() function can be used instead of the DATE_PART() function to get the date difference in years, months, seconds, etc. In that case, the “,” will be replaced with the “FROM” clause.
For instance, the below snippet depicts how to find the date difference in seconds using the EXTRACT() function:
SELECT ((EXTRACT('Day' FROM '2023-01-03 04:10:00'::TIMESTAMP - '2023-01-02 03:15:00'::TIMESTAMP) * 24 +
EXTRACT('Hour' FROM '2023-01-03 04:10:00'::TIMESTAMP - '2023-01-02 03:15:00'::TIMESTAMP)) * 60
+ EXTRACT('Minute' FROM '2023-01-03 04:10:00'::TIMESTAMP - '2023-01-02 03:15:00'::TIMESTAMP))*
60 + EXTRACT('Second' FROM '2023-01-03 04:10:00'::TIMESTAMP - '2023-01-02 03:15:00'::TIMESTAMP)
sec_diff;
The EXTRACT() function retrieves the date difference in seconds.
How to Find DateTime Difference in Postgres Via the AGE() Function?
In PostgreSQL, the built-in AGE() function can also be used to calculate the date difference. The AGE() function returns the DateTime difference as an interval. Here is the syntax that exhibits the basic usage of the AGE() function:
AGE(end_date, start_date);
The AGE() function will subtract the start date from the end date and retrieve the resultant DateTime as an interval.
Example: Calculating DateTime Difference
Let’s learn how to get date difference as an INTERVAL in Postgres:
SELECT AGE('2023-01-03 04:10:00' - '2023-01-02 03:15:00') date_diff;
The above snippet illustrates that the difference between the provided dates is “1 year 10 months and 3 days”.
Conclusion
In databases, such as SQL Server, Oracle, etc., the DATEDIFF function is used to find the difference between provided Dates or TIMESTAMPS. However, Postgres doesn’t support the DATEDIFF function. Alternatively, the “-” operator, DATE_PART(), EXTRACT(), and AGE() functions can be used in PostgreSQL to calculate the difference between various DateTime values. This post explained different alternatives of the DATEDIFF function in PostgreSQL.
