Postgresql difference between two timestamps

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