PostgreSQL

Get Year from Date PostgreSQL

Like many other databases, PostgreSQL is quite famous for its built-in queries to fetch much information regarding the dates and times. PostgreSQL provides us with many built-in functions to get the specific year, month, or day from a date. Thus, we have been using all those methods to fetch the year from a date i.e. extract, to_char, and date_part. Have a look at some of the examples in the PostgreSQL shell to get the specified year from a date. Let’s start with the opening of the PostgreSQL shell in windows 10 of our system. For this, search for the psql on the search bar of windows 10 held at the left-most corner of your desktop. The “SQL Shell” option will pop up. Tap on the SQL shell application to open it. Within a few seconds, a PostgreSQL shell will be opened on your screen asking you for the Server name.

Add your server name i.e., localhost. Add the database in which you want to work i.e., aqsayasin, and port number i.e., 5432. After this, you have to specify the username you want to use for working in the PostgreSQL shell i.e. aqsayasin, and the password for that particular user. This will set the SQL environment for specified database and username in this shell as shown. The PostgreSQL shell is ready to be used for queries.

The PostgreSQL has been utilized to fetch the specific year from the date using the EXTRACT() function in our PostgreSQL SELECT query. This function uses two arguments in it. Its first argument will show us the field value to be fetched and the second is the source from where we have been fetching the first value. In our case, the first value must be “Year” to fetch it from a certain date. Let’s just start our first example to fetch a year from a specific date in the PostgreSQL shell using the SELECT query. So, we have started the query with the keyword “SELECT” and used the keyword “EXTRACT” to add a function. Within the query, the function is utilizing the keyword “YEAR” to fetch the year from a given timestamp specified by the keyword “TIMESTAMP” and the date as the value of timestamp. The keyword “FROM” is used as complementary to relate the field value with the source. You can see the format of the date is “month-date-year”. On executing this SQL query on the PostgreSQL shell, the year mentioned in the specified date of query has been shown on the PostgreSQL shell i.e., 1996.

Let’s take a closer look at more examples of fetching the year from a date using the EXTRACT function in a select query of PostgreSQL. This time, we will be changing the format of date as “month/date/year” i.e., use of slash. The remaining query is unchanged and executed on the PostgreSQL shell. It shows the year “2000” in return beneath the auto-generate “date_part”.

Let’s see another example to use the different format of date for fetching the year from it. Therefore, we have been utilizing the “year-month-date” format in our SELECT query to do so. On execution of the updated query, it has also been successful and returned the year “2000” specified in the date_part column.

Let’s take another illustration to see how the EXTRACT query works on the date-time timestamp within the PostgreSQL shell. We have been using the different format of date and time this time as compared to the above examples. So, we have started our query with the same SELECT and EXTRACT keywords. Within the EXTRACT() function, we have been using the keyword “YEAR” to fetch the year from a date-time timestamp specifying some date and time. The date format is “year/month/date” and the time format is “hour:minutes:seconds”. In this case, the time doesn’t matter actually i.e., you can skip it in the query. The execution of this format of date also returns the year mentioned in the date i.e., 2000.

Let’s see how the second argument of the EXTRACT function can be modified or changed to see similar results as we got in the above examples. This time, we have been mentioning the DATE as the second argument instead of the timestamp. The DATE argument is using the date as its value in single commas i.e., string value. The “AS” part of the SELECT query is used to rename or modify the returned column name on the output screen. Therefore, we have been using it to see our result beneath the column “year” i.e., user-defined name for column “date_part”. The running of this query will lead us to display the year mentioned in the DATE format used in the argument i.e., “year-month-date”. This is how we utilize the DATE argument in our instruction.

Let’s see the way to fetch the year from a date without mentioning some specific date. This may only be valid for the current date timestamp. Therefore, we have to utilize the “CURRENT_TIMESTAMP” as the second argument of the EXTRACT() function in the SELECT instruction. The year will be displayed on the shell for the current year i.e., 2022.

To get the year from a specified string, we have also utilized the EXTRACT() function in the query. This method uses the INTERVAL as its second argument. The value for INTERVAL must be a string mentioning the date and time as shown. In return, it outputs the year 10 on the shell.

Let’s move a little further to see how the SELECT query can be manipulated or edited to use it for fetching the year in return. This time, we will not be using the EXTRACT function to get our goal. Else, we have been using another function “date_part” in the SELECT query of the PostgreSQL shell. So, we have started this query with a keyword SELECT followed by a function date_part(). The date-part function takes two arguments i.e. field to be fetched and the source to be fetched from. In our case, we have to fetch the string field “year” from a source “timestamp”.

We have to mention the argument “year” and the date-time timestamp in the single commas to make the strings. Through this, the date_part() function will be able to fetch the year from the date. Also, we have been utilizing the “AS” part of the SELECT query to rename the column name as “year” which the year will be going to display. The query execution is clearly showing the year returned “2030” from the specified timestamp “year-month-date”.

The function “to_Char()” is also very famous to get the year from a specified date. It uses two arguments i.e., DATE and “string” format to get the specified value. In our case, we have mentioned the “yyyy” stands for a year from a date “2050-1-12”. In return, it shows us the year 2050 on the PostgreSQL command-line shell.

Conclusion

This article is here to cover the discussion about fetching the year from some specified date on the PostgreSQL shell. This has been achieved using various built-in methods of PostgreSQL database like to_char(), date_parT() and extract() within the SELECT instruction. Also, different formats of dates have been utilized in all the examples to make it easy in any way. Avoiding all the unnecessary discussions makes this article easier to understand.

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.