PostgreSQL

What is Date_Part in PostgreSQL?

PostgreSQL is a blend of different functionalities provided and supported by this database management system’s different functions, commands, and statements (DBMS). With the help of these functions and commands, you can perform multiple operations on your data very conveniently. There is a very frequently used function in PostgreSQL named the “date_part” function. In this article, we aim to explore the usage of this function in PostgreSQL in Windows 10 by presenting different relevant examples that make use of this function.

The Date_Part Function in PostgreSQL in Windows 10:

“Date_Part” is a very useful function in PostgreSQL that can be used to extract different values that are related to the date and time depending upon the parameters provided to this function. Some of the values extracted from this function are year, month, day, hours, minutes, seconds, century, millennium, etc. The syntax of the “Date_Part” function in PostgreSQL in Windows 10 is shown below:

# SELECT date_part(‘Value_To_Be_Extracted’, Source_Value);

You have to replace the Value_To_Be_Extracted with the allowable value or parameter that can be extracted while using the “date_part” function and the Source_Value with a value or a function from which you want to extract the results.

Examples of Using the Date_Part Function in PostgreSQL in Windows 10:

To understand the usage of the Date_Part function in PostgreSQL in Windows 10, you will have to go through the following five examples:

Note: Before proceeding with these examples, you need to log into your PostgreSQL server from the Windows 10 command prompt.

Example # 1: Extracting the Century from the Current Date:

If you want to know the current century, then you can easily extract it from the current date by executing the following PostgreSQL query:

# SELECT date_part(‘century’,now());

In this query, we have used the “date_part” function that accepts two arguments, i.e., the value you want to extract and the value from where you want to extract it. The “now” function in PostgreSQL simply returns the current date and time in the year, month, and the date and the hours, minutes, and seconds formats, respectively. If you do not want to use the “now” function over here, then you can simply replace it with the current date and time in the yy:mm:dd and hh:mm:ss formats. Moreover, “century” refers to the value to be extracted, i.e., the current century.

After running this query, we got the result shown in the image below. You can easily verify that this query is produced the correct results since the current century is the 21st.

Example # 2: Extracting the Millennium from the Current Date:

If you want to enquire about the current millennium, then you can conveniently get it from the current date by running the PostgreSQL query shown below:

# SELECT date_part(‘millennium’,now());

Again, we have provided the two necessary arguments of the “date_part” function in this query. The purpose of the “now” function in this query is the same as we explained in our first example. Moreover, “millennium” in this query refers to the value extracted, i.e., the current millennium.

After executing this query, we acquired the following result. You can confirm the correctness of this result since the current millennium is 3rd.

Example # 3: Extracting the Year from the Current Date:

The current year can also be extracted very conveniently from the current date by executing the PostgreSQL query shown below:

# SELECT date_part(YEAR,now());

This PostgreSQL query is more or less the same as the ones that we have used for our first two examples. The only difference is that the value to be extracted this time is the current year.

We know that the current year is 2021, and it can also be seen from the result of this query as shown in the following image:

Example # 4: Extracting the Hours, Minutes, and Seconds from the Current Time:

Now, we will execute a different kind of PostgreSQL query while using the “date_part” function. For that, we could also have used the “now” function, but here, we are only concerned with the time and not the date so, we will simply make use of the “CURRENT_TIME” function that just returns the current time in the hours, minutes, and seconds format. The PostgreSQL query depicting this functionality is as follows:

# SELECT date_part(HOUR,CURRENT_TIME)h, date_part(MINUTE,CURRENT_TIME)m, date_part(SECOND,CURRENT_TIME)s;

Since we are trying to extract multiple values in this query, we have used a comma to separate out the different integrated queries. Moreover, we have used the “h”, “m”, and “s” characters to display the headers of the values extracted in the output. We are extracting the hours, minutes, and seconds from the current time.

The results of this query, i.e., all the extracted values against their respective headers, are shown in the image below:

Example # 5: Extracting the Day of the Week and the Day of the Year from the Current Date:

This example will also share with you a different function for extracting values from the current date. Again, we could have used the “now” function over here as well, but the function used over here is just an alternative to the “now” function. Therefore, we are going to use the “CURRENT_TIMESTAMP” function for this example. The exact query representing this functionality is as follows:

# SELECT date_part(‘dow’,CURRENT_TIMESTAMP)dow, date_part(‘doy’,CURRENT_TIMESTAMP)doy;

Since we are trying to extract multiple values in this query, we have used a comma to separate out the different integrated queries. Moreover, we have used the “dow” and “doy” notations to display the headers of the values extracted in the output. We are basically extracting the day of the week and the day of the year from the current date.

The results of this query, i.e., all the extracted values against their respective headers, are shown in the image below:

Conclusion:

Once you go through this complete guide, you will easily understand the usage of the “date_part” function of PostgreSQL in Windows 10. This function can be used to extract different values depending upon the provided parameters. In this article, we have tried to cover the most commonly used values; however, you can also explore other allowable values extracted from this function by using the same syntax we shared.

Apart from the simple usage of this function in PostgreSQL in Windows 10, we have also explained how you can extract multiple values in a single PostgreSQL query by calling the “date_part” function multiple times i.e., by using it with different parameters every time. In this way, you can easily get multiple results in the output just by executing a single PostgreSQL query. Moreover, other than the “date_part” function, we have also tried to throw light on the usage of some other functions of PostgreSQL in Windows 10 that can be used in conjunction with the “date_part” function.

About the author

Aqsa Yasin

I am a self-motivated information technology professional with a passion for writing. I am a technical writer and love to write for all Linux flavors and Windows.