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
I am Bijay having more than 15 years of experience in the Software Industry. During this time, I have worked on MariaDB and used it in a lot of projects. Most of our readers are from the United States, Canada, United Kingdom, Australia, New Zealand, etc.
Want to learn MariaDB? Check out all the articles and tutorials that I wrote on MariaDB. Also, I am a Microsoft MVP.