IsFinite(), justify_days() and justify_hours() function in PostgreSQL

Prerequisite : PostgreSQL – Introduction

1. IsFinite() function in PostgreSQL :
This function in PostgreSQL helps to test a finite date, timestamp, and interval. Since we know PostgreSQL supports a timestamp having infinity or negative infinity values, so here IsFinite() function plays an important role. The function will test the given data or timestamp and return true or false accordingly.

Syntax –

isfinite(date)
isfinite(timestamp)
isfinite(interval)

Parameters –
The function accepts only one parameter, which can be either of the following –

• date
• timestamp
• interval

Return –
The function will return “True” or “t” if the given date/timestamp/interval is finite otherwise “False” or “f”.

Example 1 –
Check the working of IsFinite() function using date “2021-09-13”

SELECT isfinite(date '2021-09-13');

Output – True
Since the date “2021-09-13” is not infinite, so the function will return True.

Example 2 –
Check the working of IsFinite() function using timestamp “2021-09-13 12:15:05”

SELECT isfinite(timestamp '2021-09-13 12:15:05');

Output – True
Since the timestamp “2021-09-13 12:15:05” is not infinite, so the function will return True.

Example 3 –
Check the working of IsFinite() function using interval “5 hours 15 minutes”

SELECT isfinite(interval '5 hours 15 minutes');

Output – True
Since the interval “5 hours 15 minutes” is not infinite, so the function will return True.

Example 4 –
Check the working of IsFinite() function using “Infinity” constant

SELECT isfinite('infinity'::timestamp);

Output – False
Since infinite constant is passed as an argument, so the function will return False.

Example 5 –
Check the working of IsFinite() function using “-Infinity” (Negative Infinity) constant

SELECT isfinite('-infinity'::timestamp);

Output – False
Since -infinite constant is passed as an argument, so the function will return False.

2. justify_days() function in PostgreSQL :
This function in PostgreSQL helps to adjust the interval value i.e the function can represent 30 days time period as months. Like if the user passes 30 days as an argument, the function will return 1 mon and so on.

Syntax –

justify_days(interval)

Parameters –
The function accepts only one parameter 

• interval – 
  A certain interval that can be represented in months
• Returns –
  The function will convert and returns the given interval in terms of months.

Example 1 –
Check the working of justify_days() function using interval “90 days”

SELECT justify_days(interval '90 days');

Output – 3 mon
Since 30 days is equal to 1 month so, 90 days will be represented as 3 months.

Example 2 –
Check the working of justify_days() function when the interval is not a perfect multiple of 30 
Taking interval as 35 days.

SELECT justify_days(interval '35 days');

Output – 1 mon 5 days
Since the interval is not a perfect multiple of 30 so, the result will in form of months and days.

Example 3 –
Check the working of justify_days() function when the interval is less than 30 days

SELECT justify_days(interval '14 days');

Output – 14 days
Since the given interval is 14 days which is smaller than 30 so, the output will remain 14 days.

Example 4 –
Check the working of justify_days() function when time is also passed along with the interval
   1. Taking interval as 50 days 40 minutes.

SELECT justify_days(interval '50 days 40 minutes');

Output – 1 mon 20 days 00:40:00

   2. Taking interval as 160 days 2 hours 40 minutes 59 seconds.

SELECT justify_days(interval '160 days 2 hours 40 minutes 59 seconds');

Output – 5 mon 10 days 02:40:59
Since time is also passed with an interval so the output displays months, days, and time in HH:MM:SS format.

3. justify_hours() function :
This function in PostgreSQL helps to adjust the interval value i.e the function can represent 24 hours time period as days. Like if the user passes 24 hours as an argument, the function will return 1 day and so on.

Syntax –

justify_hours(interval)

Parameters – 
The function accepts only one parameter,

• interval – 
  A certain interval that can be represented in days
• Returns –
  The function will convert and returns the given interval in terms of days. 

Example 1 –
Check the working of justify_hours() function using interval “72 hours”

SELECT justify_hours(interval '72 hours');

Output – 3 days
Since 24 hours is equal to 1 day so, 72 hours will be represented as 3 days.

Example 2 –
Check the working of justify_hours() function when the interval is less than 24 hours

SELECT justify_hours(interval '14 hours');

Output – 14:00:00
Since the given interval is 14 hours which is smaller than 24 so, the output will be in HH:MM:SS format.

Example 3 –
Check the working of justify_hours() function when the interval is in form of seconds, minutes, and months.
    1. Taking interval as 5.53 months.

SELECT justify_hours(interval '5.53 months');

Output – 5 mon 15 days 21:36:00

  2. Taking intervals as 300 hours 58 minutes.

SELECT justify_hours(interval '300 hours 58 minutes');

Output – 12 days 12:58:00

 3. Taking intervals as 3000 hours 10 minutes 20 seconds.

SELECT justify_hours(interval '3000 hours 10 minutes 20 seconds');

Output – 125 days 00:10:20
The output will be displayed in months, days, and time in HH:MM:SS format.