How to resolve "pg_catalog.pgdate_part" Error When Using DATEDIFF in PostgreSQL
Introduction
When working with PostgreSQL and attempting to calculate the hour difference between two dates using the DATEDIFF function, you may encounter an error stating that the function pg_catalog.pgdate_part('Unknown', interval) does not exist.
This Knowledge Base article provides a solution to resolve the “pg_catalog.pgdate_part” error and guides you through the steps to calculate the hour difference between two dates.
Issue
When attempting to calculate the hour difference between two dates using DATEDIFF function in PostgreSQL, the function pg_catalog.pgdate_part("Unknown", interval) does not exist error is thrown.
Cause
The issue arises when utilizing an invalid or unrecognized date part with the pgdate_part function.
Note: PostgreSQL on Amazon RDS does not have direct support for the DATEDIFF function. We have the custom implementation of this function in Bold BI.
Solution
To calculate the hour difference between two dates without using the DATEDIFF function, follow these steps:
- Subtract the start date from the end date.
- Extract the
epoch(number of seconds) from the resulting interval. - Divide the epoch by 3600 to obtain the hour difference.
Expression: EXTRACT(EPOCH FROM ([enddate] - [startdate])) / 3600
Sample screenshot
Expression Result:
Conclusion
By following the solution provided in this article, you can resolve the pg_catalog.pgdate_part does not exist error when using the DATEDIFF function in PostgreSQL.
Additional References
