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 Postresql:
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() function example
Below are some of the Examples for DATE_PART() function in PostgreSQL.
1.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:
2. 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:
3. 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:
4. 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:
5. 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:
6. Extract second from date using DATE_PART() function 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:
7. 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:
8. 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:
9. 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:
10. Extract decade from date using DATE_PART() 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:
11. 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:
12. 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:
13. 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:
