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 ValueTIMESTAMPInterval
CENTURYThe centuryThe number of centuries
DECADEThe decade that is the year divided by 10Sames as TIMESTAMP
YEARThe yearSames as TIMESTAMP
MONTHMonth, 1-12The number of months, modulo (0-11)
DAYThe day of the month (1-31)The number of days
HOURThe hour (0-23)The number of hours
MINUTEThe minute (0-59)The number of minutes
SECONDThe secondThe number of seconds
MICROSECONDSThe second’s field, including fractional parts, multiplied by 1000000Sames as TIMESTAMP
MILLISECONDSThe second’s field, including fractional parts, multiplied by 1000Sames as TIMESTAMP
DOWThe day of the week Sunday (0) to Saturday (6)
DOYThe day of the year ranges from 1 to 366
EPOCHThe number of seconds since 1970-01-01 00:00:00 UTCThe total number of seconds in the interval
ISODOWDay of the week based on ISO 8601 Monday (1) to Sunday (7)
ISOYEARISO 8601 week number of year
TIMEZONEThe timezone offset from UTC, measured in seconds
TIMEZONE_HOURThe hour component of the time zone offset
TIMEZONE_MINUTEThe minute component of the time zone offset
MILLENNIUM The millenniumThe number of millennium
WEEK The number of the ISO 8601 week-numbering week of the year
QUARTERTHE Quarter of the yearThe number of quarters
DATE_PART() function field argument values

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;
PostgreSQL DATE_PART
PostgreSQL DATE_PART

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;
Postgresql DATE_PART
Postgresql DATE_PART

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;
PostgreSQL DATE_PART VS EXTRACT
PostgreSQL EXTRACT Function
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;
PostgreSQL DATE_PART VS EXTRACT Function
PostgreSQL EXTRACT Fuction Examples

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;
PostgreSQL DATE_PART VS DATE_TRUNC
PostgreSQL DATE_PART VS DATE_TRUNC

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;
PostgreSQL DATE_PART month and 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
PostgreSQL DATE_PART year
PostgreSQL DATE_PART 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;
PostgreSQL DATE_PART day of week
PostgreSQL DATE_PART 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 hour
PostgreSQL DATE_PART 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";
PostgreSQL DATE_PART month name
PostgreSQL DATE_PART month name

You may like the following PostgreSQL tutorials:

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