Introduction to PostgreSQL datediff
PostgreSQL provides a datediff function to users. The datediff means we can return the difference between two dates based on their specified interval. The datediff function plays an important role in the database management system because datediff functions as a calendar and it is very helpful to users. With the help of datediff function we can determine any previous date, next date similarly we can determine previous day and next day as well as we can also determine time, week and minutes. Some functions of datediff use the system current date as per query syntax.
Syntax:
select datediff function() date1, interval date2;
Explanation:
- In the above syntax we use select clause where datediff function means various date related functions, where date1 first specified date and date2 is second specified date.
- Here interval is used to determine the difference between two dates that we specified.
How datediff Function works in PostgreSQL?
- We must install PostgreSQL in our system.
- We require basic knowledge about PostgreSQL.
- We must require the database to apply the datediff function.
Given below are the different datediff function as follows:
First we see basic functions related to date and time.
1. now() function
This function is used to know the current date and time of the system and this function is a system defined function, it gives a result in timestamp.
Example:
Code:
select now();
Output:
2. Time of day function
This function shows date and time in text format.
Example:
Code:
Select timeofday();
Output:
3. Quarter function
This function is used to know quarter in year.
Example:
Code:
select extract (quarter from now());
Explanation:
- In this example we extract quarter of the current year.
- Illustrate the end result of the above declaration by using the use of the following snapshot.
Output:
4. Day of the year
This function is used to calculate the number of days in a single year.
Code:
select extract (doy from now());
Output:
Explanation:
- In this example, we use extract function to know the number of days in a year specific in this example we use now a function that means it counts days from system current date.
- Illustrate the end result of the above declaration by using the use of the following snapshot.
5. Day of the week
This function is used to extract days from the week.
Example:
Code:
Select extract (dow from now());
Explanation:
- In this example, extract the current day in week it takes the system current date to know the day it starts from Monday as 1, Tuesday as 2 and so on. Thus by default the value of Sunday is 0.
- Illustrate the end result of the above declaration by using the use of the following snapshot.
Output:
6. Current month
This function is used to know the current month.
Example:
Code:
select extract (month from now());
Explanation:
- In this example we extract the current month, where now() function takes the current date of the system.
- Illustrate the end result of the above declaration by using the use of the following snapshot.
Output:
Apart from datediff and extract function we have another function which we call date_part function working of this function is the same like datediff function. The datediff function is not directly supported by PostgreSQL.
Example:
Code:
Select date_part ('dow', now());
Explanation:
- In this example we use the date_part function instead of extract. Similarly we implement all above functions by using date_part function.
- Illustrate the end result of the above declaration by using the use of the following snapshot.
Output:
Now we see the example of main datediff function:
Example #1: Difference between two years
Code:
select date_part ('year', '2020/01/01'::date) - date_part ('year', '2010/01/01'::date);
Explanation:
- Suppose we need to know the difference between two dates at that time we use the above statement, see here we use date_part instead of datediff because PostgreSQL does not support directly.
- Illustrate the end result of the above declaration by using the use of the following snapshot.
Output:
Example #2: Difference between two days
Code:
Select '2020-07-28 22:00:00' :: timestamp - '2020-07-26 02:00:00' :: timestamp;
Explanation:
- Suppose we need to determine the difference between two days at that time we use the above statement.
- Illustrate the end result of the above declaration by using the use of the following snapshot.
Output:
Example #3: Difference between the two week
Code:
Select trunk (date_part ('day', '2020-07-28' :: timestamp - '2020-07-21' :: timestamp) / 7);
Explanation:
- Suppose we need to know the difference between the two week at that time we use the above statement.
- Illustrate the end result of the above declaration by using the use of the following snapshot.
Output:
Example #4: Difference between hours
Code:
select date_part('day','2020-07-28 07:55'::timestamp - '2020-07-28 08:05'::timestamp)
* 24+
date_part('hour','2020-07-28 07:55'::timestamp - '2020-07-28 08:05'::timestamp);
Explanation:
- Suppose we need to know the difference of two hours at that time we use the above statement.
- Illustrate the end result of the above declaration by using the use of the following snapshot.
Output:
Example #5: Difference between minutes
Code:
select date_part ('hour', '09:40:10'::time - '09:45:50'::time) *60+
date_part ('minute', '09:40:10'::time - '09:45:50'::time);
Explanation:
- Suppose we need to know the difference between minutes at that time we use the above statement.
- Illustrate the end result of the above declaration by using the use of the following snapshot.
Output:
Example #6: Difference between seconds
Code:
select date_part ('hour','09:40:10'::time - '09:40:50'::time)*60+
date_part ('minute','09:40:10'::time - '09:40:50'::time)*60+
date_part ('second','09:40:10'::time - '09:40:50'::time);
Explanation:
- Suppose we need to know the difference between the second at that time we use the above statement.
- Illustrate the end result of the above declaration by using the use of the following snapshot.
Output:
Conclusion
From the above article we saw syntax of datediff and basic functions of datediff as well as some advanced functions like determine difference between year, month, day, hour and minutes with multiple examples. From this article we saw how we can handle different datediff functions.
Recommended Articles
This is a guide to PostgreSQL datediff. Here we discuss the introduction and how datediff function works in the PostgreSQL? You may also have a look at the following articles to learn more –
