Summary
: in this tutorial, you will learn how to use the PostgreSQL
DATE_PART()
function to retrieve the subfields such as year, month, and week from a
date
or
time
value.
Introduction to the PostgreSQL DATE_PART() function
The
DATE_PART()
function allows you to extract a subfield from a date or time value.
The following illustrates the basic syntax for the
DATE_PART()
function:
DATE_PART(field, source)Code language: SQL (Structured Query Language) (sql)
The
DATE_PART()
function has two optional parameters
field
and
source
. The
field
is an identifier that determines what to extract from the
source
.
The values of the field must be one of the following permitted values:
- century
- decade
- year
- month
- day
- hour
- minute
- second
- microseconds
- milliseconds
- dow
- doy
- epoch
- isodow
- isoyear
- timezone
- timezone_hour
- timezone_minute
The
source
is a temporal expression that evaluates to
TIMESTAMP
,
TIME
, or
INTERVAL
. If the
source
evaluates to
DATE
, the function will be cast to
TIMESTAMP
.
The
DATE_PART()
function returns a value whose type is double precision.
PostgreSQL DATE_PART() function examples
Let’s explore some examples of using the
DATE_PART()
function.
1) Basic PostgreSQL DATE_PART() function example
The following example uses the
DATE_PART()
function to extract the century from a timestamp:
SELECT date_part('century',TIMESTAMP '2017-01-01');Code language: JavaScript (javascript)Output:
date_part
-----------
(1 row)Code language: SQL (Structured Query Language) (sql)2) Extracting the year from a timestamp
To extract the year from the same timestamp, you pass the year to the
field
argument:
SELECT date_part('year',TIMESTAMP '2017-01-01');Code language: SQL (Structured Query Language) (sql)Output:
date_part
-----------
(1 row)3) Extracting the quarter from a timestamp
The following example uses the
DATE_PART()
function to extract the quarter from a timestamp:
SELECT date_part('quarter',TIMESTAMP '2017-01-01');Code language: SQL (Structured Query Language) (sql)Output:
date_part
-----------
(1 row) 4) Extracting month from a timestamp
The following example uses the
DATE_PART()
function to extract the month from a timestamp:
SELECT date_part('month',TIMESTAMP '2017-09-30');Code language: JavaScript (javascript)Output:
date_part
-----------
(1 row)Code language: SQL (Structured Query Language) (sql)5) Extracting a decade from a timestamp
The following example uses the
DATE_PART()
function to extract the decade from a timestamp:
SELECT date_part('decade',TIMESTAMP '2017-09-30');Code language: SQL (Structured Query Language) (sql)Output:
date_part
-----------
(1 row)6) Extracting a week number from a timestamp
To extract the week number from a time stamp, you pass the week as the first argument:
SELECT date_part('week',TIMESTAMP '2017-09-30');Code language: JavaScript (javascript)Output:
date_part
-----------
(1 row)Code language: SQL (Structured Query Language) (sql)7) Extracting a week number from a timestamp
To get the current millennium, you use the
DATE_PART()
function with the
NOW()
function as follows:
SELECT date_part('millennium',now());Code language: JavaScript (javascript)Output:
date_part
-----------
(1 row)Code language: SQL (Structured Query Language) (sql)8) Extracting day from a timestamp
To extract the day part from a timestamp, you pass the
day
string to the
DATE_PART()
function:
SELECT date_part('day',TIMESTAMP '2017-03-18 10:20:30');Code language: SQL (Structured Query Language) (sql)Output:
date_part
-----------
(1 row)9) Extracting hour, minute, and second from a timestamp
To extract the hour, minute, and second, from a time stamp, you pass the corresponding value hour, minute, and second to the
DATE_PART()
function:
SELECT date_part('hour',TIMESTAMP '2017-03-18 10:20:30') h,
date_part('minute',TIMESTAMP '2017-03-18 10:20:30') m,
date_part('second',TIMESTAMP '2017-03-18 10:20:30') s;Code language: JavaScript (javascript)Output:
h | m | s
----+----+----