Calculating Timestamp Difference in Hours for PostgreSQL: Methods and Implementation

Calculation Mechanism for Timestamp Differences in PostgreSQL

In database operations, calculating the difference between two timestamps is a common requirement. PostgreSQL offers flexible time-handling capabilities, but unlike MySQL's TIMESTAMPDIFF() function, it does not have a direct function to return an integer hour difference. The core method involves using timestamp subtraction to produce an INTERVAL type, then extracting seconds via the EXTRACT function and converting to hours.

Using EXTRACT(EPOCH FROM ...) to Calculate Hour Difference

The best practice is to use EXTRACT(EPOCH FROM current_timestamp - somedate)/3600. Here, EPOCH extracts the number of seconds from 1970-01-01 UTC to the specified time interval. The subtraction operation current_timestamp - somedate generates an INTERVAL, representing the time difference. For example:

SELECT EXTRACT(EPOCH FROM TIMESTAMP '2023-10-01 12:00:00' - TIMESTAMP '2023-10-01 10:00:00')/3600 AS hour_diff;

This returns 2.0, indicating a 2-hour difference. To obtain an integer, use type conversion: ::INTEGER or CAST(... AS INTEGER).

Practical Application Examples and Code Implementation

Assume a links_link table with a created timestamp field, calculate the hour difference from creation to now for each link:

SELECT "links_link"."created",
       "links_link"."title",
       (EXTRACT(EPOCH FROM current_timestamp - "links_link"."created")/3600)::INTEGER AS "age" 
FROM "links_link";

This directly outputs the integer hour difference. If the difference is negative (e.g., somedate is in the future), use ABS() for absolute value or GREATEST(..., 0) to ensure non-negativity, depending on business logic.

Reasons to Avoid the age Function

Although PostgreSQL has an age() function, it is not suitable for precise hour difference calculations. age(timestamp) subtracts the timestamp from the current date at midnight, leading to inaccuracies. For example:

SELECT age(current_timestamp);

May return a result like -00:52:40.826309, based on midnight time rather than real-time difference. Therefore, in scenarios requiring precise hour differences, the EXTRACT(EPOCH FROM ...) method should be prioritized.

Comparison with Other Methods

Referring to other answers, INTERVAL can be used for time filtering, e.g.:

SELECT * FROM yourtable 
WHERE your_timestamp_field > current_timestamp - INTERVAL '5 hours';

But this is suitable for conditional queries, not direct difference calculation. For integer hour differences, the EXTRACT method is more direct and efficient.

Summary and Best Practices

In PostgreSQL, the best method for calculating timestamp hour differences is combining subtraction, EXTRACT(EPOCH FROM ...), and division. This provides flexibility and precision, applicable to various time-handling needs. By understanding the mechanisms of INTERVAL and EPOCH, developers can efficiently implement functionality similar to MySQL's TIMESTAMPDIFF().