PostgreSQL DATE_PART() Function with examples

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