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
Postgresql difference between two timestamps

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.

Postgresql difference between two timestamps
Postgresql difference between two timestamps

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.

Postgresql difference between two timestamps in days
Postgresql difference between two timestamps in days

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.

Postgresql difference between two timestamps in hours
Postgresql difference between two timestamps in hours

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.

Postgresql difference between two timestamps in seconds
Postgresql difference between two timestamps in seconds

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.

Postgresql difference between two timestamps in milliseconds
Postgresql difference between two timestamps in milliseconds

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.

Postgresql difference between two timestamps in months
Postgresql difference between two timestamps in months

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.

Postgresql difference between two timestamps in minutes
Postgresql difference between two timestamps in minutes

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.

Postgresql difference between two timestamps in years
Postgresql difference between two timestamps in years

You may also like to read the following PostgreSQL tutorials.

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