In this Postgresql tutorial, we will learn about “Postgresql difference between two timestamps” and cover the following topics.
- Postgresql difference between two timestamps
- Postgresql difference between two timestamps in days
- Postgresql difference between two timestamps in hours
- Postgresql difference between two timestamps in seconds
- Postgresql difference between two timestamps in milliseconds
- Postgresql difference between two timestamps in months
- Postgresql difference between two timestamps in hours
- Postgresql difference between two timestamps in years
Difference between two timestamps in PostgreSQL
Before beginning, we need to know about the PostgreSQL function named EXTRACT() that retrieves or extracts a sub-field such as a year, month, and day from a date and timestamp value.
Syntax:
EXTRACT(sub-field FROM source)
Where sub-field can be 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 the decade that is the year divided by 10.
- 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 millennium
- MILLISECONDS: It is the second’s field, including fractional parts,
- MINUTE: It is the minute (0-59) or a number of 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;

Postgresql difference between two timestamps
In Postgresql, we can calculate the difference between two timestamps by subtracting one timestamp from other timestamps using the minus operator (-).
SELECT
id,
departure,
arrival,
arrival - departure AS timestamp_difference
FROM journey;
In the above code, we are subtracting the two timestamps arrival and departure to get the difference.
The output of the above code is given below.

Read: PostgreSQL Date Difference
Postgresql difference between two timestamps in days
In Postgresql, the difference between two timestamps in days can be calculated by providing the day as a sub-field to the Extract function.
SELECT
id,
departure,
arrival,
Extract(day FROM (arrival - departure)) AS days
FROM journey;
In the above code, we are extracting the number of days from the difference of arrival and departure date by providing a day sub-field in the EXTRACT function as a new column named days.
The output of the above code is given below.

In the output, look at the column days, where a number of days between two timestamps named arrival and departure.
Read: Create a stored procedure in PostgreSQL
Postgresql difference between two timestamps in hours
In Postgresql, the difference between two timestamps in hours can be calculated by providing the day as a sub-field 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, first, we are retrieving the number of days from the Extract function, then multiplying the result ( or a number of days ) by 24 to get the number of hours, because 1 day equals 24 hours.
The output of the above code is given below.

Read: PostgreSQL list databases
Postgresql difference between two timestamps in seconds
In Postgresql, the difference between two timestamps in seconds can be calculated by providing the epoch as a sub-field to the Extract function.
SELECT
id,
departure,
arrival,
Extract(epoch FROM (arrival - departure)) AS seconds
FROM journey;
In the above code, we are extracting the number of seconds from the difference of arrival and departure date by providing an epoch sub-field in the EXTRACT function as a new column named seconds.
The output of the above code is given below.

Read: PostgreSQL Data Types
Postgresql difference between two 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, first, we are retrieving the number of seconds from the Extract function, then multiplying the result ( or a number of seconds ) by 1000 to get the milliseconds, because 1 second equals 1000 milliseconds.
The output of the above code is given below.

Read: PostgreSQL Date Add
Postgresql difference between two timestamps in months
In Postgresql, the difference between two timestamps in months can be calculated by providing the month as a sub-field 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 are extracting month from arrival and departure separately, and then subtracting departure from arrival to getting the number of months as a new column named months.
The output of the above code is given below.

Read: Postgresql date between two dates
Postgresql difference between two timestamps in minutes
In Postgresql, the difference between two timestamps in minutes can be calculated by providing the epoch as a sub-field 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 are retrieving the number of seconds from the Extract function, then dividing the result ( or a number of seconds ) by 60 to get the minutes, because 1 minute equals 60 seconds.
The output of the above code is given below.

Read: Postgresql create user with password
Postgresql difference between two timestamps in years
In Postgresql, the difference between two timestamps in years can be calculated by providing the year as a sub-field 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 are extracting year from arrival and departure separately, and then subtracting departure from arrival to getting the number of years as a new column named years.
The output of the above code is given below.

You may also like to read the following PostgreSQL tutorials.
- How to find 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
So, in this tutorial, we have learned about “Postgresql difference between two timestamps” and covered the following topics.
- Postgresql difference between two timestamps
- Postgresql difference between two timestamps in days
- Postgresql difference between two timestamps in hours
- Postgresql difference between two timestamps in seconds
- Postgresql difference between two timestamps in milliseconds
- Postgresql difference between two timestamps in months
- Postgresql difference between two timestamps in hours
- Postgresql difference between two timestamps in years
After working for more than 15 years in the Software field, especially in Microsoft technologies, I have decided to share my expert knowledge of SQL Server. Check out all the SQL Server and related database tutorials I have shared here. Most of the readers are from countries like the United States of America, the United Kingdom, New Zealand, Australia, Canada, etc. I am also a Microsoft MVP. Check out more here.