intro
Let’s learn everything you need to know about DATEDIFF Postgres equivalent functions to properly calculate date and time differences in the DBMS.
SQL Server is famous for its support for various SQL functions and statements not available in other database management systems. One of those functions is DATEDIFF: this function is widely used to calculate date and time differences. PostgreSQL users will have a hard time finding such a thing in their database management systems. That’s not without a reason — such a function simply doesn’t exist. At the same time, there are some Postgres DATEDIFF alternatives.
Let’s explore them all!
What Is DATEDIFF?
The DATEDIFF function is mostly prevalent in SQL Server. This function returns the difference between two dates as integers and in SQL Server, the function can be used like so:
1
SELECT DATEDIFF(year, ‘DATE1’, ‘DATE2’) [AS column_name];
In MySQL, the DATEDIFF function would look like this:
1
SELECT DATEDIFF('YYYY-MM-DD [HH:mm:ss]', 'YYYY-MM-DD') [AS custom_name];
In PostgreSQL, there is no DATEDIFF function. So, developers would elect to use something like DATE_PART instead (this SQL query will return how many years have passed between the two dates):
1
SELECT DATE_PART('year', '2024-01-01'::date) - DATE_PART('year', '2023-01-01'::date);
For your results to be displayed under a custom column name, employ the SQL alias AS clause again:
1
SELECT DATE_PART('year', '2024-01-01'::date) - DATE_PART('year', '2023-01-01'::date) AS custom_name;
Woohoo! We have similar results in PostgreSQL too. Let’s explore the methods to simulate a Postgres DATEDIFF function.
Postgres DATEDIFF Alternative Implementations
Let’s explore some approaches to get time difference results in days, weeks, hours, minutes, and seconds as if the Postgres DATEDIFF function existed.
Days
The following SQL Server query would calculate the difference between August 28, 2024 11:30 and September 30, 2024 01:00 in days:
1
SELECT DATEDIFF(day, '2024-08-24 11:30:00', '2024-09-30 01:00:00');
If you try to call the same query in PostgreSQL, it would interpret day in the same query as a column instead:
Calling the Postgres DATEDIFF function as if you were an SQL Sever user would result in an error. As a solution, you could define the SQL date type after the date definition like so:
1
SELECT '2024-08-24 11:30:00'::timestamp - '2024-09-30 01:00:00'::timestamp AS time_passed;
Such queries would allow for the subtraction of one datetime value from another and return an interval value in the form of “A days B hours” where A is the number of days that have passed and B is the number of hours. For the opposite operation, explore our SQL add to date guide.
Weeks
Time differences in weeks would be a little harder to calculate, but they’re not impossible. SQL Server users would benefit from simpler DATEDIFF-based queries like the following:
1
SELECT DATEDIFF(week, '2024-01-01', '2024-12-01');
Where PostgreSQL users would need to make use of the Postgres TRUNC (truncate) function in conjunction with the DATE_PART function once again:
1
SELECT TRUNC(DATE_PART('day', '2024-01-01'::timestamp - '2024-12-01'::timestamp)/7) AS week_difference;
Keep in mind that a proper datatype definition is important. Otherwise, PostgreSQL will error out as well:
Hours
To calculate between two dates in hours, in SQL Server we could use such a query:
1
SELECT DATEDIFF(hour, '2024-07-30 08:55', '2024-07-30 09:45');
Such a query would return "1" as in one hour even though not a full hour would've passed. In PostgreSQL, we could use something like this:
1
SELECT DATE_PART('hour', '2024-07-30 08:55'::timestamp - '2024-07-30 09:55'::timestamp);
Note that we end up with a value of “-1.0”: an hour of difference. If we would alter the second part of the query just even a minute:
We would end up with “0.0” (no difference in hours because not a full hour has passed — there’s only 59 minutes of a difference.)
Minutes and Seconds
In SQL Server, one can also make use of the DATEDIFF function to calculate the difference between two dates in seconds. Take a look at this example:
1
SELECT DATEDIFF(second, '21:44:09', '21:45:09');
Self-explanatory, right? SQL Server would return “60”, as in 60 seconds had passed between the two dates.
For PostgreSQL though, one would need to make use of the DATE_PART function once again. Simple approaches like the one below wouldn’t work:
1
SELECT(DATE_PART('second', '21:44:09'::time - '21:45:09'::time));
There’s nothing wrong with this query — the main problem here, though, is that PostgreSQL isn’t returning seconds: it’s returning minutes. To make PostgreSQL return the number of actual seconds, you would need to use a query that selects the hour, minute, and seconds together. Your query would look something like this:
1
SELECT (DATE_PART('hour', '21:44:09'::time - '21:45:09'::time) * 60 +
2
DATE_PART('minute', '21:44:09'::time - '21:45:09'::time)) * 60 +
3
DATE_PART('second', '21:44:09'::time - '21:45:09'::time);
The bottom line is clear — since you cannot use DATEDIFF when working with PostgreSQL, you must use other functions, some of which aren’t very easy to understand at first glance. One of such functions is DATE_PART and it can quickly become confusing if you aren’t aware of its contents:
The DATE_PART function is not a piece of cake: it’s likely to require some practice before you get it right, but once you do, things should fall into place eventually.
The Role of SQL Clients
Many of you have probably noticed that most of the queries shown in this blog were run using a SQL client, and you’re not mistaken: I’ve run these queries in DbVisualizer, the SQL client with the highest customer satisfaction on the market. DbVisualizer does offer a free 21-day trial where you can explore more features of this awesome tool including the ability to generate ERD schemas, the drag-and-drop tool allowing you to drag-and-drop tables you want to query, and more. Make sure to grab a free 21-day trial of this tool and try this tool for yourself: you have nothing to lose!
Did I mention it can easily format complex SQL queries too?
Summary
There’s no direct Postgres DATEDIFF equivalent: one will need to make use of the DATE_PART function to achieve goals. Since the syntax between database management systems differs, one would need to define the data type for PostgreSQL too.
The DATE_PART function would likely necessitate prior knowledge of PostgreSQL and some practice before you get it right too.
Knowledge isn’t hard to obtain though: explore our blog for more articles on database management systems, and you will be an expert in no time.
FAQ
What DBMS supports the DATEDIFF function?
The database management systems that support the DATEDIFF function include most relational database management systems like MySQL, SQL Server, and Sybase. PostgreSQL does not support DATEDIFF by default.
What functions to use to achieve Postgres DATEDIFF functionality?
Make use of the DATE_PART function: if necessary, combine the results of that function into one result set as shown in the examples in this blog.
Where to learn more about PostgreSQL and other database management systems?
You can learn more about the internals of both PostgreSQL and other database management systems on our blog as well as by watching the videos available in the Database Dive YouTube channel.
