In order to Get YEAR from date or Timestamp in postgresql we use Extract() function. Within the Extract() we have to mention YEAR as we are getting year from timestamp. To extract the year from a date in PostgreSQL, you can use the DATE_PART() function with the ‘year’ unit.
Let’s see how to
- Extract year from date or Timestamp in Postgresql
- Create the column which extracts year from timestamp column
With an example for both
Extract YEAR from date or Timestamp in Postgresql:
Within the Extract keyword we have to mention YEAR as we are getting year from timestamp
SELECT EXTRACT (YEAR FROM TIMESTAMP '2011-11-22 13:30:15')
So the resultant year will be
Syntax for DATE_PART() Function in Postresql:
DATE_PART(unit, source)
unit: Specifies the part of the date or timestamp to extract (e.g., ‘year’, ‘month’, ‘day’, ‘hour’, ‘minute’, ‘second’, etc.).
source: The date or timestamp from which to extract the specified part
In Our case. The values of the unit must be “year”.
Extract Year from Date in postgresql Simple Example:
In order to Extract year from date in postgresql we will be using DATE_PART() function.
In the below Example we will be passing year as “unit” argument and date as “source” argument in order to extract year from Date or datetime
SELECT DATE_PART('year', '2024-02-24'::timestamp) AS year_part;
Output:
Extract Year from Current date in postgresql :
This query uses the CURRENT_DATE function to get the current date and then extracts the year from current date using DATE_PART()
SELECT CURRENT_DATE AS current_date, DATE_PART('year', CURRENT_DATE) AS year_part;
Output:
Extract Year part from Date in postgresql table:
We will be using Below Student_detail table for our example to depict on how to extract year part from date in postgresql
Student_detail:
In the above table we will be using DATE_PART() Function, which will take “year” and column named “birthdaytime” as argument. Which will extract year part from “birthdaytime” column and store in the new column of postgresql table as shown below
select *, DATE_PART('year', birthdaytime::timestamp) AS year_part from Student_detail
so the resultant table will have year_part column
Extract Year part from Date in postgresql table using Extract():
We will be using Below Student_detail table for our example to depict on how to extract Year part from date in postgresql
Student_detail:
In the above table we will be using EXTRACT() Function, which will take “Year” and column named “birthdaytime” as argument. Which will extract Year from “birthdaytime” column and store in the new column of postgresql table as shown below
Select *, extract(YEAR FROM birthdaytime) as year_part from student_detail
So the resultant table will have year_part column