Datediff postgresql

Datediff function is the greatest facility provided by postgresql to the new user. It is used to provide the difference between two provided dates by the user. This function has played an important role in database management systems, specifically in postgresql, as it acts as a calendar. We are familiar with entering a record in any database management system, so we should also know the relation of data entered with the date. Because whenever we are going to add data in our system, its date and time are saved, and that save information is useful in future terms. Similarly, the stored data in the system also contains the date information of the entity, whose attributes are saved by dates, i.e., date of birth, last day in school, etc. this information of any employee or student helps the organization in dealing with the record of the time the student/ employee has passed in that particular organization.

By using datediff, we can be aware of next date, previous dates and similar is the case with days and weeks. This article is based on some basic usage of this function and leads it to the differences of weeks and months. You need to install postgresql with the database in the working state. We can apply these commands on both PgAdmin and the PSQL as well. Here we have chosen psql for the accomplishment of this function.

Let us start with the syntax of the datediff function we will use later in the article.

Interval is the difference between the two dates we provide in the command.

Function Now()

Now moving towards the first and foremost part of datediff function. It’s a now() function. All you need is to write it, and it will do the rest in retrieving the date and current time of writing the command.

This command gives the date and time with seconds and the detail of timestamps with the timezone.

Function “current time of the day”

The current time, date, and day are known by using this function here.

The Quarter function()

A year contains 3 quarters in it. From the current command, we come to know about the quarter part of the current date. As today is the 30 August, it means the current date will lie in the 3rd quarter.

The new part will determine the date, and then the rest function will calculate the quarter part.

Days passed till now

This function extracts the number of days that have passed till now according to the current date. The ‘doy’ will calculate the current day from the now() function and display it as an output.

Current day in the week

We can also calculate the current day number in the week. The function works to extract the data from the system, which is done by now() function, to know the current day of the week. For example, if it is Monday, the day number will be 1, 2 for Tuesday, and so on. The day number of Sunday is set as ‘0’ by default by the system.

Now we also have an alternative for calculations of these days/months. For instance, we can replace ‘extract’ with ‘date_part’. The command works the same, and the result can be compared with the above examples. We can implement all previous examples with this keyword as well.

So these were some basic examples. Now we will move towards the datediff functions and will see it’s working.

years difference

The difference between two specific years can be calculated by using the “date_part” keyword. But the syntax is quite different from other examples. Date_part is used because an error occurs if we directly use “datediff” in the command. You need to enter the keyword ‘year’ in the command, following the current date or the one you want to make a difference. Then after the minus operator, use the other date that is to be subtracted.

The total difference of year is 26.

days difference

Unlike the previous ones, this example used the timestamp to calculate the days between two dates with time.

The resultant value displays the total days and also the difference in time. The difference between the time is calculated when the difference between two days is known through the timestamps in the form of days. On that particular day, time is then calculated. This calculation is done in 24 hours format.

hours difference

To determine the difference between hours, we need to calculate it through dates with a timestamp time. To take hours as an output, we will multiply the value by 24 and add the value with the resultant of all calculations.

Hence the result displays the value in hours.

mid of the month

This can be obtained by subtracting the number from 17 numbers
.

Datediff using a table in postgresql

Till now, we have undergone through basics of date differentiation and some examples of datediff as well. But now, this part is somehow extensive as it includes the involvement of the table in the database.

Create the table “baby” using the create command given. The table contains the data of a child. We have taken two dates in the form of birth_date and discharge_date.

After creating the table, insert the data by using the insert query you can see from the image attached here.

Moving back to the topic, as we are trying to get the difference in dates, we will calculate the baby’s age by using a single input ‘birth_date’, column value from the table.

This query will create another column, ‘AGE’, to calculate the age by taking the dates of each child. AGE is a built-in method of calculation in postgresql. The difference is calculated from the current date to the age given in the table.

Moving towards another similar yet different in taking input for the age function. Here we use both the birth_date and discharge_date as the input. All other things remain the same in the command

You can observe the result from the given image.

Conclusion

Datediff is a meaningful way of getting the history of any task or a person. It is not used directly but has many flavors to use with like age, months, year and second, etc., to be calculated. This article throws light on each aspect of datediff postgresql.