PostgreSQL: date_part Function
This PostgreSQL tutorial explains how to use the PostgreSQL date_part function with syntax and examples.
Description
The PostgreSQL date_part function extracts parts from a date.
Syntax
The syntax for the date_part function in PostgreSQL is:
date_part( 'unit', date )
Parameters or Arguments
- date
- The date, timestamp, time, or interval value from which the date part is to be extracted.
- unit
The unit type of the interval such as day, month, minute, hour, and so on. It can be one of the following:
unit Explanation century Uses the Gregorian calendar where the first century starts at '0001-01-01 00:00:00 AD' day Day of the month (1 to 31) decade Year divided by 10 dow Day of the week (0=Sunday, 1=Monday, 2=Tuesday, ... 6=Saturday) doy Day of the year (1=first day of year, 365/366=last day of the year, depending if it is a leap year) epoch Number of seconds since '1970-01-01 00:00:00 UTC', if date value. Number of seconds in an interval, if interval value hour Hour (0 to 23) isodow Day of the week (1=Monday, 2=Tuesday, 3=Wednesday, ... 7=Sunday) isoyear ISO 8601 year value (where the year begins on the Monday of the week that contains January 4th) microseconds Seconds (and fractional seconds) multiplied by 1,000,000 millennium Millennium value milliseconds Seconds (and fractional seconds) multiplied by 1,000 minute Minute (0 to 59) month Number for the month (1 to 12), if date value. Number of months (0 to 11), if interval value quarter Quarter (1 to 4) second Seconds (and fractional seconds) timezone Time zone offset from UTC, expressed in seconds timezone_hour Hour portion of the time zone offset from UTC timezone_minute Minute portion of the time zone offset from UTC week Number of the week of the year based on ISO 8601 (where the year begins on the Monday of the week that contains January 4th) year Year as 4-digits
Note
- See also the extract function.
Applies To
The date_part function can be used in the following versions of PostgreSQL:
- PostgreSQL 9.4, PostgreSQL 9.3, PostgreSQL 9.2, PostgreSQL 9.1, PostgreSQL 9.0, PostgreSQL 8.4
Example
Let's look at some PostgreSQL date_part function examples and explore how to use the date_part function in PostgreSQL with date values.
For example:
postgres=# SELECT date_part('day', date '2014-04-25'); date_part ----------- 25 (1 row) postgres=# SELECT date_part('month', date '2014-04-25'); date_part ----------- 4 (1 row) postgres=# SELECT date_part('year', date '2014-04-25'); date_part ----------- 2014 (1 row)
Let's explore how to use the date_part function in PostgreSQL with timestamp values.
For example:
postgres=# SELECT date_part('day', timestamp '2014-04-25 08:44:21'); date_part ----------- 25 (1 row) postgres=# SELECT date_part('month', timestamp '2014-04-25 08:44:21'); date_part ----------- 4 (1 row) postgres=# SELECT date_part('minute', timestamp '2014-04-25 08:44:21'); date_part ----------- 44 (1 row) postgres=# SELECT date_part('hour', timestamp '2014-04-25 08:44:21'); date_part ----------- 8 (1 row)
Let's explore how to use the date_part function in PostgreSQL with time values.
For example:
postgres=# SELECT date_part('minute', time '08:44:21'); date_part ----------- 44 (1 row) postgres=# SELECT date_part('milliseconds', time '08:44:21.7'); date_part ----------- 21700 (1 row)
Let's explore how to use the date_part function in PostgreSQL with interval values.
For example:
postgres=# SELECT date_part('day', interval '5 days 3 hours'); date_part ----------- 5 (1 row) postgres=# SELECT date_part('hour', interval '5 days 3 hours'); date_part ----------- 3 (1 row)
Advertisements