In this PostgreSQL tutorial, we will discuss, about PostgreSQL DATE_PART() function to retrieve year, month, week, hour, etc., from a date or time value and will also cover the following topic:
- PostgreSQL DATE_PART
- PostgreSQL DATE_PART VS EXTRACT
- PostgreSQL DATE_PART VS DATE_TRUNC
- PostgreSQL DATE_PART month and year
- PostgreSQL DATE_PART year
- PostgreSQL DATE_PART day of week
- PostgreSQL DATE_PART hour
- PostgreSQL DATE_PART month name
New to PostgreSQL date? Check out PostgreSQL DATE Functions with Examples.
PostgreSQL DATE_PART
DATE_PART() function is one of the date-time functions available in PostgreSQL that allows you to retrieve subfields like., year, month, week, etc., from a date or time value.
DATE_PART(field,source)
In the above syntax,
- The DATE_PART() function returns a double precision type value.
- The source is a temporal expression which will evaluates to TIMESTAMP, TIME, or INTERVAL. If it evaluvates to the DATE then, the function will cast it to TIMESTAMP.
- And, the field is like an identifier which specifies the field to be extracted from the source. The values of the field in PostgreSQL can be one out of the following mentioned:
Field Value | TIMESTAMP | Interval |
---|---|---|
CENTURY | The century | The number of centuries |
DECADE | The decade that is the year divided by 10 | Sames as TIMESTAMP |
YEAR | The year | Sames as TIMESTAMP |
MONTH | Month, 1-12 | The number of months, modulo (0-11) |
DAY | The day of the month (1-31) | The number of days |
HOUR | The hour (0-23) | The number of hours |
MINUTE | The minute (0-59) | The number of minutes |
SECOND | The second | The number of seconds |
MICROSECONDS | The second’s field, including fractional parts, multiplied by 1000000 | Sames as TIMESTAMP |
MILLISECONDS | The second’s field, including fractional parts, multiplied by 1000 | Sames as TIMESTAMP |
DOW | The day of the week Sunday (0) to Saturday (6) | – |
DOY | The day of the year ranges from 1 to 366 | – |
EPOCH | The number of seconds since 1970-01-01 00:00:00 UTC | The total number of seconds in the interval |
ISODOW | Day of the week based on ISO 8601 Monday (1) to Sunday (7) | – |
ISOYEAR | ISO 8601 week number of year | – |
TIMEZONE | The timezone offset from UTC, measured in seconds | – |
TIMEZONE_HOUR | The hour component of the time zone offset | – |
TIMEZONE_MINUTE | The minute component of the time zone offset | – |
MILLENNIUM | The millennium | The number of millennium |
WEEK | The number of the ISO 8601 week-numbering week of the year | – |
QUARTER | THE Quarter of the year | The number of quarters |
Example:
SELECT date_part('century',TIMESTAMP '2017-01-01');
SELECT date_part('century',TIMESTAMP '1998-03-08') AS Century;
SELECT date_part('decade',TIMESTAMP '1998-03-08') AS Decade;
SELECT date_part('month',TIMESTAMP '1998-03-08') AS Month;
SELECT date_part('day',TIMESTAMP '1998-03-08') AS Day;
SELECT date_part('isoyear',TIMESTAMP '1998-03-08') AS ISO_Year;

Let’s again provide the date in US or United States format to the Date_PART function and see the result.
SELECT date_part('century',TIMESTAMP '01-01-2017');
SELECT date_part('century',TIMESTAMP '03-08-1998') AS Century;
SELECT date_part('decade',TIMESTAMP '03-08-1998') AS Decade;
SELECT date_part('month',TIMESTAMP '03-08-1998') AS Month;
SELECT date_part('day',TIMESTAMP '03-08-1998') AS Day;
SELECT date_part('isoyear',TIMESTAMP '03-08-1998') AS ISO_Year;

Read PostgreSQL DROP TABLE + Examples
PostgreSQL DATE_PART VS EXTRACT
- In PostgreSQL, both the DATE_PART() function and EXTRACT() function gives similar results.
- In fact the EXTRACT() function gets re-written for the PostgreSQL to DATE_PART() function. thats why they both have same execution plan.
- The EXTRACT() actually compiles with the SQL standard and DATE_PART() is a PostgreSQL specific query. Hence, there is no performance difference in general.
- SO, it is totally upto you, that which function you want to use. If you want to stick to the SQL standards then use EXTRACT() else, you can use DATE_PART().
- There is a minure difference in between DATE_PART() and EXTRACT() in PostgreSQL. As the EXTRACT() syntax call the internal DATE_PART() function. so, if SQL portibility is not a concern, then calling DATE_PART() directly should be a bit quicker.
The syntax for the EXTRACT() function is as follows:
EXTRACT(field FROM source)
In the above syntax,
- It also returns a double precision type value as same as in the DATE_PART().
- There are two arguments as similar to the DATE_PART() function, field and source.
- The source can be a value of type TIMESTAMP or INTERVAL as similar as in DATE_PART(), and also the function casts the DATE value, if passed, to a TIMESTAMP value.
- And the field argument specifies the field to be extracted from the date or time value. The valid field values are same as in the DATE_PART().
Example:
As we have done some of the examples of the DATE_PART() in the previous topic, so let’s do some examples of EXTRACT().
SELECT EXTRACT(YEAR FROM TIMESTAMP '2020-12-07 13:30:15');
SELECT EXTRACT(second FROM TIMESTAMP '2020-12-07 13:30:15');
SELECT EXTRACT(epoch FROM TIMESTAMP '2020-12-07 13:30:15');
SELECT EXTRACT(epoch FROM TIMESTAMP '2020-12-07 13:30:15') AS epoch;
SELECT EXTRACT(microseconds FROM TIMESTAMP '2020-12-07 13:30:15') AS Microseconds;
SELECT EXTRACT(microseconds FROM INTERVAL '21 years 11 months 7 days 15 hours 17 minutes 7 second') AS Quater;
SELECT EXTRACT(month FROM INTERVAL '21 years 11 months 7 days 15 hours 17 minutes 7 second') AS Month;

SELECT EXTRACT(decade FROM INTERVAL '21 years 11 months 7 days 15 hours 17 minutes 7 second') AS Decades;
SELECT EXTRACT(isodow FROM TIMESTAMP '2020-12-07 13:30:15') AS ISO_DOW;
SELECT EXTRACT(dow FROM TIMESTAMP '2020-12-07 13:30:15') AS DOW;
SELECT EXTRACT(timezone FROM NOW()) AS Timezone;
SELECT EXTRACT(week FROM NOW()) AS Week;

Read PostgreSQL INSERT Multiple Rows
PostgreSQL DATE_PART VS DATE_TRUNC
- The DATE_PART() function pick a specified field from a TIMESTAMP or date value as explained above, while DATE_TRUNC() rounds off the value to the specified precision.
- eg – DATE_PART() will return a day, month, year, etc., while DATE_TRUNC() will return the beginning of the month, year, hour, etc.,
- The DATE_TRUNC() function truncates (trim) an INTERVAL or TIMESTAMP value on the basis of a specified date part. eg – The DATE_TRUNC() returns the month, week, hour, etc., with a level of precision.
- The syntax of the DATE_TRUNC() function is as follows:
date_trunc('datepart', field)
- The DATE_TRUNC() function returns a TIMESTAMP or an INTERVAL value.
- The datepart argument specifies the level of precision used to truncate (trim) the datepart given.
- And, the field argument is a TIMESTAMP or an INTERVAL value to be truncated. It can be an expression that results in a TIMESTAMP or an INTERVAL value or can be one of the following, which are same as in DATE_PART():
- millennium
- century
- decade
- year
- quarter
- month
- week
- day
- hour
- minute
- second
- milliseconds
- microseconds
Example:
As we have done some of the examples of the DATE_PART() in the previous topics, so let’s do some examples of DATE_TRUNC().
SELECT DATE_TRUNC('hour', TIMESTAMP '2021-03-17 02:09:30');
SELECT DATE_TRUNC('minute', TIMESTAMP '2021-03-17 02:09:30');
SELECT DATE_TRUNC('year', TIMESTAMP '2021-03-17 02:09:30') AS Y;
SELECT DATE_TRUNC('decade', TIMESTAMP '2021-03-17 02:09:30') AS By_Decade;

Read PostgreSQL DROP COLUMN + 7 examples
PostgreSQL DATE_PART month and year
You can return the month and year of a TIMESTAMP, DATE, or an INTERVAL value in PostgreSQL by using the comma-separated DATE_PART() function and specifying the field as month and year to them in a SELECT statement. The syntax is as follows:
SELECT
date_part('month', source_expression) AS Month,
date_part('year', source_expression) AS Year;
In the above syntax,
- The source_expression is the source argument that can be any TIMESTAMP, DATE, INTERVAL value or an expression returning these values.
- The month and year enclosed in the quotes (”) represents the field argument to be extracted from the source_expression.
- And, the Month and Year not enclosed in the quotes are the Reference name specified to the results generated and printed by the SELECT query. You can give any name to the results generated.
Example:
SELECT date_part('month',NOW()) AS Month,
date_part('year',NOW()) AS year;

Read PostgreSQL INSERT INTO table + 9 Examples
PostgreSQL DATE_PART year
You can return the year of a TIMESTAMP, DATE, or an INTERVAL value in PostgreSQL using the DATE_PART() function and specify the field as the year in a SELECT statement. The syntax is as follows:
SELECT date_part('year', source_expression) AS Year;
The above is similar to the syntax explained in the previous topic. So, let’s practice the concept by doing some examples.
Example:
SELECT date_part('year',TIMESTAMP '1997-03-17 02:09:30') AS year;
SELECT date_part('year', INTERVAL '21 years 11 months 7 days 15 hours 17 minutes 7 second') AS BY_year

Read How to create a view in PostgreSQL
PostgreSQL DATE_PART day of week
You can return the day of the week from a TIMESTAMP, DATE, or an INTERVAL value in PostgreSQL by using the DATE_PART() function and specifying the field as dow. The syntax is as follows:
SELECT date_part('dow', source_expression) AS Day;
Example:
SELECT date_part('dow', NOW()) AS Day_of_week;
SELECT date_part('dow',TIMESTAMP '1997-03-15 02:09:30') AS Day_of_week;

Read PostgreSQL ALTER TABLE + 19 Examples
PostgreSQL DATE_PART hour
You can return the hour from a TIMESTAMP, DATE, or an INTERVAL value in PostgreSQL using the DATE_PART() function and specify the field as the hour. The syntax is as follows:
SELECT date_part('hour', source_expression) AS Hour;
Example:
SELECT date_part('hour',TIMESTAMP '1997-03-15 02:09:30') AS Hour;
SELECT date_part('hour', NOW()) AS Hour;

PostgreSQL DATE_PART month name
You can return the name of the month of a TIMESTAMP, DATE, or an INTERVAL value in PostgreSQL by using the TO_CHAR function followed by the DATE_PART() function and specifying the field as the month, which will convert the month number returned by the DATE_PART() to the name of the month. The syntax is as follows:
SELECT TO_CHAR('month', source_expression) AS Month;
In the above syntax,
- TO_CHAR() is used in place of the DATE_PART() function, as the later one returns the month number, but not the name.
- The argument month is the field name that specifies the field to be extracted from a date or time value, and the source_expression is the date or time value giving expression or the value.
- You can also specify the formatting of the returned value by specifying the lowercase, uppercase, capitalized, or formatted as a short month name in the field like ‘month’, ‘Month’, ‘MONTH’, ‘Mon’, ‘mon’, etc.,
Example:
SELECT
TO_CHAR(NOW(), 'month') AS "month",
TO_CHAR(NOW(), 'Month') AS "Month",
TO_CHAR(NOW(), 'MONTH') AS "MONTH",
TO_CHAR(NOW(), 'mon') AS "mon",
TO_CHAR(NOW(), 'Mon') AS "Mon",
TO_CHAR(NOW(), 'MON') AS "MON";

You may like the following PostgreSQL tutorials:
- PostgreSQL ADD COLUMN + 17 Examples
- PostgreSQL vs SQL Server: Detailed Comparison
- Postgresql date_trunc function
In this PostgreSQL tutorial, we have learned about PostgreSQL DATE_PART() function to retrieve year, month, week, hour, etc., from a date or time value and have covered the following topics:
- PostgreSQL DATE_PART
- PostgreSQL DATE_PART VS EXTRACT
- PostgreSQL DATE_PART VS DATE_TRUNC
- PostgreSQL DATE_PART month and year
- PostgreSQL DATE_PART year
- PostgreSQL DATE_PART day of week
- PostgreSQL DATE_PART hour
- PostgreSQL DATE_PART month name
I am Bijay having more than 15 years of experience in the Software Industry. During this time, I have worked on MariaDB and used it in a lot of projects. Most of our readers are from the United States, Canada, United Kingdom, Australia, New Zealand, etc.
Want to learn MariaDB? Check out all the articles and tutorials that I wrote on MariaDB. Also, I am a Microsoft MVP.