In Section we will learn how to use the PostgreSQL DATE_PART() function to retrieve the subfields such as year, month, and week from a date , Date time value.
Syntax for DATE_PART() Function in PostgreSQL:
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.
The values of the unit must be one of the following values:
- century
- decade
- year
- month
- day
- hour
- minute
- second
- microseconds
- milliseconds
- dow
- doy
- epoch
- isodow
- isoyear
- timezone
- timezone_hour
- timezone_minute
PostgreSQL DATE_PART() example
Below are some of the Examples for DATE_PART() function in postgresql.
Extract Year from date using DATE_PART() function in PostgreSQL:
In the below Example we will be passing year as “unit” argument and date as “source” argument in order to extract year from Date function
SELECT DATE_PART('year', '2024-02-24'::timestamp) AS year_part;
Output:
Extract month from date using DATE_PART() function in PostgreSQL:
In the below Example we will be passing month as “unit” argument and date as “source” argument in order to extract month from Date function
Select DATE_PART('month', '2024-02-24'::timestamp) AS month_part
Output:
Extract day from date using DATE_PART() function in postgresql:
In the below Example we will be passing day as “unit” argument and date as “source” argument in order to extract day from Date or datetime
Select DATE_PART('day', '2024-02-24'::timestamp) AS day_part
Output:
Extract hour from datetime using DATE_PART() function in postgresql:
In the below Example we will be passing hour as “unit” argument and date as “source” argument in order to extract hour from Date or datetime
Select DATE_PART('hour', '2024-02-24 15:30:00'::timestamp) AS hour_part
Output:
Extract minute from datetime using DATE_PART() function in postgresql:
In the below Example we will be passing minute as “unit” argument and date as “source” argument in order to extract minute from Date or datetime
Select DATE_PART('minute', '2024-02-24 15:30:00'::timestamp) AS minute_part
Output:
Extract second from date using DATE_PART() in postgresql:
In the below Example we will be passing second as “unit” argument and date as “source” argument in order to extract second from Date or datetime
Select DATE_PART('second', '2024-02-24 15:30:45'::timestamp) AS second_part
Output:
Extract quarter from date using DATE_PART() in postgresql:
In the below Example we will be passing quarter as “unit” argument and date as “source” argument in order to extract month from Date or datetime
SELECT DATE_PART('quarter', '2024-02-24'::timestamp) AS quarter_part;
Output:
Extract week from date using DATE_PART() function in postgresql:
In the below Example we will be passing week as “unit” argument and date as “source” argument in order to extract week from Date or datetime
SELECT DATE_PART('week', '2024-02-24'::timestamp) AS week_part;
Output:
Extract microseconds from datetime using DATE_PART() in postgresql:
In the below Example we will be passing microseconds as “unit” argument and date as “source” argument in order to extract microseconds from Date or datetime
SELECT DATE_PART('microseconds', '2024-02-24 15:30:45.123456'::timestamp) AS microsec_part;
Output:
Extract decade from date using DATE_PART() function in postgresql:
In the below Example we will be passing decade as “unit” argument and date as “source” argument in order to extract decade from Date or datetime
SELECT DATE_PART('decade','2024-02-24'::timestamp) AS decade_part;
Output:
Extract century from date using DATE_PART() in postgresql:
In the below Example we will be passing century as “unit” argument and date as “source” argument in order to extract century from Date or datetime
SELECT DATE_PART('century','2024-02-24'::timestamp) AS Century_part;
Output:
Extract day of year from date using DATE_PART() in postgresql:
In the below Example we will be passing doy as “unit” argument and date as “source” argument in order to extract day of year from Date or datetime
SELECT DATE_PART('doy', '2024-02-24'::timestamp) AS day_of_year_part;
Output:
Extract day of week from date using DATE_PART() in postgresql:
In the below Example we will be passing dow as “unit” argument and date as “source” argument in order to extract day of week from Date or datetime
SELECT DATE_PART('dow', '2024-02-24'::timestamp) AS day_of_week_part;
Output:
