PostgreSQL Difference Between Two Timestamps

In this comprehensive PostgreSQL article, I’ll walk you through multiple approaches to finding difference between two timestamps in PostgreSQL, from basic subtraction to more advanced techniques.

PostgreSQL Difference Between Two Timestamps

Before beginning, we need to be familiar with the PostgreSQL function named EXTRACT(), which retrieves or extracts a sub-field, such as a year, month, or day, from a date and timestamp value.

Syntax:

EXTRACT(sub-field FROM source)

Where the sub-field can be one of the following values.

• CENTURY: It is the number of centuries
• DAY: It is the day of the month (1-31) or the number of days
• DECADE: It is a decade, which is 10 years.
• DOW: It is the day of the week, Sunday (0) to Saturday (6).
• DOY: It is the day of the year that ranges from 1 to 366.
• EPOCH: It is the total number of seconds in the interval
• HOUR: It is the number of hours
• MILLENNIUM: It is the number of millennia
• MILLISECONDS: It is the second’s field, including fractional parts.
• MINUTE: It is the minute (0-59) or several minutes.
• MONTH: The number of months (1-12).
• SECOND: It is the number of seconds.
• WEEK: It is the number of weeks
• YEAR: It is the year.

The source can be a date or timestamp value.

Let’s create a table named journey that we will use in this tutorial.

CREATE TABLE journey(id SERIAL, arrival TIMESTAMP, departure TIMESTAMP);

Insert the following data.

INSERT INTO journey(departure,arrival)VALUES('2017-02-24 12:00:00','2017-05-06 07:30:00'),
('2020-09-12 15:50:00','2020-10-23 10:30:30'),('2019-07-14 16:15:00','2019-07-14 20:40:30'),
('2019-01-05 08:35:00','2020-01-08 14:00:00');

SELECT * FROM journey;

Approach -1 Using the direct subtraction with the minus operator (-)

In PostgreSQL, we can calculate the difference between two timestamps by subtracting one timestamp from the other timestamp using the minus operator (-).

SELECT
  id,
  departure,
  arrival,
  arrival - departure AS timestamp_difference
FROM journey;

In the above code, we subtract the two timestamps, arrival and departure, to calculate the difference.

The output of the code above is provided below.

Read: PostgreSQL Date Difference

Approach 2: Using the Extract function

Example 1: Timestamps in days

In PostgreSQL, the difference between two timestamps in days can be calculated by providing the day as a subfield to the Extract function.

SELECT
  id,
  departure,
  arrival,
  Extract(day FROM (arrival - departure)) AS days
FROM journey;

In the above code, we extract the number of days from the difference between the arrival and departure dates by providing a day sub-field in the EXTRACT function, creating a new column named ‘days’.

The output of the code above is provided below.

In the output, look at the ‘days’ column, where the number of days is between the two timestamps named ‘arrival’ and ‘departure’.

Read: Create a stored procedure in PostgreSQL

Example 2: Timestamps in hours

In PostgreSQL, the difference between two timestamps in hours can be calculated by providing the day as a subfield to the Extract function, and the result from the Extract will be multiplied by 24 to get the hours.

SELECT
  id,
  departure,
  arrival,
  Extract(day FROM (arrival - departure))*24 AS hours
FROM journey;

In the above code, we first retrieve the number of days from the Extract function and then multiply the result (or the number of days) by 24 to get the number of hours, because 1 day equals 24 hours.

The output of the code above is provided below.

Read: PostgreSQL list databases

Example 3: Timestamps in months

In PostgreSQL, the difference between two timestamps in months can be calculated by providing the month as a subfield to the Extract function.

SELECT
  id,
  departure,
  arrival,
  Extract(month FROM arrival) - Extract(month FROM departure) AS months
FROM journey;

In the above code, we extract the month from arrival and departure separately, and then subtract departure from arrival to calculate the number of months as a new column named ‘months’.

The output of the code above is provided below.

Read: PostgreSQL date between two dates.

Example 4: Timestamps in years

In PostgreSQL, the difference between two timestamps in years can be calculated by providing the year as a subfield to the Extract function.

SELECT
  id,
  departure,
  arrival,
  Extract(year FROM arrival) - Extract(year FROM departure) AS years
FROM journey;

In the above code, we extract the year from arrival and departure separately, and then subtract departure from arrival to calculate the number of years as a new column named ‘years’.

The output of the code above is provided below.

Approach 3: Using EXTRACT(EPOCH)

Example 1: Timestamps in seconds

In PostgreSQL, the difference between two timestamps in seconds can be calculated by providing the epoch as a subfield to the Extract function.

SELECT
  id,
  departure,
  arrival,
  Extract(epoch FROM (arrival - departure)) AS seconds
FROM journey;

In the above code, we extract the number of seconds from the difference between the arrival and departure dates by providing an epoch sub-field in the EXTRACT function as a new column named ‘seconds’.

The output of the code above is provided below.

Read: PostgreSQL Data Types

Example 2: Timestamps in milliseconds

In PostgreSQL, the difference between two timestamps in milliseconds can be calculated by providing the epoch as a sub-field to the Extract function, and the result from the Extract will be multiplied by 1000 to get the milliseconds.

SELECT
  id,
  departure,
  arrival,
  Extract(epoch FROM (arrival - departure))*1000 AS milliseconds
FROM journey;

In the above code, we first retrieve the number of seconds from the Extract function and then multiply the result (or the number of seconds) by 1000 to get the milliseconds, because 1 second equals 1000 milliseconds.

The output of the code above is provided below.

Read: PostgreSQL Date Add

Example 3: Timestamps in minutes

In PostgreSQL, the difference between two timestamps in minutes can be calculated by providing the epoch as a subfield to the Extract function, and the result from the Extract will be divided by 60 to get the minutes.

SELECT
  id,
  departure,
  arrival,
  Extract(epoch FROM (arrival - departure))/60 AS minutes
FROM journey;

In the above code, first, we retrieve the number of seconds from the Extract function, then divide the result (or the number of seconds) by 60 to get the minutes, because 1 minute equals 60 seconds.

The output of the code above is provided below.

Read: PostgreSQL create user with password.

Approach 4: Using the AGE() Function

The AGE() The function calculates the difference in years, months, and days, making it particularly useful for estimating someone’s age or tenure, or some time duration.

Example

Let us execute the following query to calculate the travel duration.

SELECT AGE(arrival, departure) AS travel_duration
FROM journey;

After executing the above query, I obtained the expected output, as shown in the screenshot below.

Comparison of Methods

Here’s a comparison table of the different methods we’ve discussed:

Method	Return Type	Best For	Example Use Case
Direct Subtraction	interval	Human-readable output	Displaying duration to users
AGE() Function	interval	Age/tenure calculations	Employee service anniversaries
EXTRACT(EPOCH)	numeric	Mathematical operations	Performance metrics, sorting
EXTRACT components	numeric	Component analysis	Breaking down time into parts
Date casting	integer	Calendar day differences	Business day calculations

Conclusion

After working with PostgreSQL timestamp calculations for many projects, I’ve found that understanding these different methods is crucial for effective database design and reporting. The right approach depends on your specific requirements:

• Use EXTRACT With component extraction, when you need to analyze specific parts of the duration
• Use basic subtraction for simple, human-readable intervals
• Use AGE() When you need year/month/day breakdowns
• Use EXTRACT(EPOCH) When you need numerical values for calculations

You may also find the following PostgreSQL tutorials helpful.

• How to find the primary column name in PostgreSQL
• Postgresql date_trunc function + Examples
• PostgreSQL Concat + Examples
• PostgreSQL TO_NUMBER() function
• PostgreSQL TO_TIMESTAMP function
• PostgreSQL change column data type