PostgreSQL Date Difference Examples

In this Postgresql tutorial, we will learn about “Postgresql date difference”, how to find the date difference in days, hours, minutes, months, etc.

• Postgresql date difference in days
• Postgresql date difference in hours
• Postgresql date difference minutes
• Postgresql date difference in milliseconds
• Postgresql date timestamp difference
• Postgresql get date difference in months
• Postgresql get date difference seconds

In Postgresql, there is no function like DATEDIFF that can calculate the difference between 2 datetime values in seconds, minutes, hours, days, weeks, month, but It has an alternate solution. That is the DATE_PART function that extracts the subfield of date or time like seconds, minutes, hours, days, etc.

Syntax:

DATE_PART(subfield,source)

The subfield is an identifier that determines what subfield to extract from the source. The following are the values of the subfield that can accept:

• century
• decade
• year
• month
• day
• hour
• minute
• second
• microseconds
• milliseconds
• dow
• doy
• epoch
• isodow
• isoyear
• timezone
• timezone_hour
• timezone_minute

The source can be a date, timestamp, interval, etc.

Let’s see through an example.

SELECT date_part('year',date'2021-10-05');

The above code extracts the year part of the date 2021-10-05 and the output is given below.

In the above output, the year of date 2021-10-05 is 2021.

Read Add days to date in PostgreSQL Examples

Postgresql date difference in days

In Postgresql, the days’ difference between two dates can be calculated using the date_part function, let’s understand through an example.

SELECT date_part('day','2022-01-15'::timestamp - '2021-01-01'::timestamp) as days;

In the above code, we are calculating the number of days between 2022-01-15 and 2021-01-01 date, where “:: timestamp” means, we are casting the string date to timestamp data type using typecast operator (::).

The output of the above code is given below.

In the above output, The day’s difference between the two dates is 379.

Read Postgresql date between two dates

Postgresql date difference in hours

In Postgresql, we can also find the date difference in hours using the DATE_PART function.

Use the example is given below to calculate the hours between dates.

SELECT date_part('day','2021-12-15 10:50'::timestamp - '2021-12-14 07:15'::timestamp) * 24 +
date_part('hour','2021-12-15 10:50'::timestamp - '2021-12-14 07:15'::timestamp) as hours;

In the above code, first, we are calculating hours between ‘2021-12-15 10:50’ and ‘2021-12-14 07:15’, then multiplying with 24 because one day equals 24 hours, after that adding exact hours difference to it from the second date_part function.

The output is given below.

In the above output, the hour’s difference between the ‘2021-12-15 10:50’ and ‘2021-12-14 07:15’ dates is 27 hours.

Postgresql date difference minutes

In Postgresql, the date difference minutes can also be calculated using the DATE_PART function.

Let’s understand through an example.

SELECT
date_part('hour','2021-12-15 10:50'::timestamp - '2021-12-15 07:15'::timestamp) * 60 +
date_part('minute','2021-12-15 10:50'::timestamp - '2021-12-15 07:15'::timestamp) as minutes;

In the above code, first, we are calculating hours between ‘2021-12-15 10:50’ and ‘2021-12-15 07:15’, then multiplying with 60 because one hour equals 60 minutes, after that adding exact minute’s difference to it from the second date_part function.

The output of the above code is given below.

Read Postgresql date_trunc function

Postgresql date difference in milliseconds

In Postgresql, we are going to find the date difference in milliseconds using the DATE_PART function.

Below is the demonstration of the date difference in milliseconds.

SELECT EXTRACT(MILLISECONDS FROM TIME '17:12:28.5')- EXTRACT(MILLISECONDS FROM TIME '17:11:20.5') as milliseconds;

Postgresql date timestamp difference

In Postgresql, the timestamp is a data type that represents the date with time, so we will calculate the date timestamp difference using arithmetic operators like minus (-).

Let’s understand with an example.

SELECT ('2018-07-14 20:40:30'::timestamp) - ('2018-07-14 16:15:00'::timestamp) ;

Read PostgreSQL DATE_PART() Function with examples

Postgresql get date difference in months

In Postgresql, we can also get the date difference in months using the date_part function.

Use the below code to get the date difference in months.

SELECT (date_part('year', '2022-01-01'::date) - date_part('year', '2021-10-02'::date)) * 12 +
(date_part('month', '2022-01-01'::date) - date_part('month', '2021-10-02'::date)) as months;

Postgresql get date difference seconds

In Postgresql, the date difference seconds can also be calculated using the DATE_PART function.

Let’ find date difference seconds.

SELECT (date_part('hour', '08:56:10'::time - '08:54:55'::time) * 60 +
               date_part('minute', '08:56:10'::time - '08:54:55'::time)) * 60 +
               date_part('second', '08:56:10'::time - '08:54:55'::time) as seconds;

You may like the following PostgreSQL tutorials:

• PostgreSQL DATE Functions with Examples
• Postgresql function return table
• PostgreSQL DATE Format + Examples
• Postgresql difference between two timestamps
• Update query in PostgreSQL
• Create a stored procedure in PostgreSQL
• PostgreSQL list databases
• PostgreSQL Data Types

So in this tutorial, we have learned about “Postgresql date difference” and covered the following topics.

• Postgresql date difference in days
• Postgresql date difference in hours
• Postgresql date difference minutes
• Postgresql date difference in milliseconds
• Postgresql date timestamp difference
• Postgresql get date difference in months
• Postgresql get date difference seconds