Calculate Timestamp Difference in Hours with PostgreSQL

If you need to calculate the difference in hours between two timestamps in PostgreSQL, you can use the EXTRACT function to extract the hours from the result of subtracting the timestamps.

Here's an example query:


SELECT EXTRACT(hour FROM (timestamp2 - timestamp1)) as hour_diff
FROM table;

In this query, "timestamp2" and "timestamp1" represent the two timestamps you want to compare, and "table" is the name of the table where the timestamps are stored.

The EXTRACT function extracts the hour component from the result of subtracting the two timestamps, and the "as hour_diff" clause renames the result column to "hour_diff" for clarity.

By default, the result of the EXTRACT function is an integer, so the result will be the difference in whole hours. If you need to include fractional hours, you can use the EXTRACT function with the "epoch" parameter to get the difference in seconds, and then divide by 3600 (the number of seconds in an hour). Here's an example:


SELECT EXTRACT(epoch FROM (timestamp2 - timestamp1)) / 3600.0 as hour_diff
FROM table;

This query will return the difference in hours with fractional precision.

In summary, to calculate the difference in hours between two timestamps in PostgreSQL, use the EXTRACT function to extract the hour component from the result of subtracting the timestamps. If you need fractional hours, use the EXTRACT function with the "epoch" parameter and divide by 3600.

Click to rate this post!
[Total: 0 Average: 0]

Related posts

Leave a Reply

Your email address will not be published. Required fields are marked *

Go up

Below we inform you of the use we make of the data we collect while browsing our pages. You can change your preferences at any time by accessing the link to the Privacy Area that you will find at the bottom of our main page. More Information