PostgreSQL DATE_PART()

Introduction to PostgreSQL DATE_PART()

PostgreSQL DATE_PART() function is mainly used to return the part of the date and time; the date_part function in PostgreSQL will subtract the subfield from the date and time value. PostgreSQL date_part function will allow retrieving subfields, e.g. week, month and year, from the date and time value. Basically, there are two parameters we have a pass with date_part function, i.e. field and source; field determines which field we have extracting from the source.

Syntax:

Given below is the syntax of the PostgreSQL date_part function:

date_part (text, timestamp)

date_part (text, interval)

date_part (unit, date)

date_part (field, source)

Given below is the parameter description of the above syntax: 

• Date part: PostgreSQL date_part function is used to extract the date part values from the expression. We can provide an argument with the date_part function as text and timestamp in PostgreSQL.
• Text: Text is the first parameter which we have used with the date_part function in PostgreSQL.
• Timestamp: This is the second parameter of the date_part function in PostgreSQL. We have used to define the timestamp value with date_part function in PostgreSQL.
• Interval: Interval is used with the same as timestamp value in PostgreSQL. We can use an interval instead of a timestamp value in PostgreSQL.
• Unit: Unit type in date_part function is interval such as minute, hour and month. Unit is an essential parameter in the date_part function.
• Date: This is defined as date, timestamp, time or interval value which is used to be partly executed.
• Field: This is a parameter of the date_part function in PostgreSQL used to which field to extract from the source.
• Source: Source is defined in the date_part function as the second parameter. This is the same as the interval parameter in the date_part function in PostgreSQL.

How PostgreSQL DATE_PART() Function works?

If we want to return only date and time value in PostgreSQL same time, we are using the date_part function. Using the date_part function in PostgreSQL, we can extract the century from the time stamp. For extracting year from the date timestamp, we need to pass the field argument in the date_part function.

In PostgreSQL, the unit type has the following form used in the date_part function. The unit type is nothing but a day, month, minute and hour.

• Century: It uses the Gregorian calendar in which the first century will start from the ‘0001-01-01 00:00:00 AD’.
• Day: This is defined as the day of the month from 1 to 31.
• Decade: Decade is defined as the year divided by 10 in PostgreSQL date_part function.
• Day of week: This is defined as a day of the week from Sunday to Saturday.
• Day of year: This is defined as a day of the year from January to December.
• Epoch: This is defined as several interval values in the date_part function.
• Hour: This is defined as an hour of the day from 0 to 23.
• Millennium: This is defined as the millennium value in the date_part function.
• Milliseconds: This is defined as a second multiply with 1000.
• Microseconds: This is defined as a second multiply with 100000.
• Minute: This is defined as a minute of the hour from 0 to 59.
• Quarter: Quarter is defined in date_part function is 1 to 4.
• Second: This is fractional second in the date_part function.
• Year: This is defined as a year in four-digit value.

To extract the second, minute and hour from timestamp by using the date_part function, we need to pass the corresponding value that is second, minute and hour.

Examples of PostgreSQL DATE_PART()

Given below are the examples mentioned:

Example #1

Extract century from a timestamp using the date_part function.

In the below example, we have used the year as 2020 and 1920 to extract a century from the timestamp.

Code:

SELECT date_part('century', TIMESTAMP '2020-05-19');

SELECT date_part('century', TIMESTAMP '1920-05-19');

Output:

In the above example, using the year like 1920, it will display a century as 20 and using the year like 2020, it will display the century as 21.

Example #2

Extract year from a timestamp using the date_part function.

In the below example, we have used the year as 2020 to extract the year from the timestamp.

Code:

SELECT date_part('year', TIMESTAMP '2020-05-19');

Output:

Example #3

Extract quarter from a timestamp using date_part function.

In the below example, we have used the year as 2020 to extract a quarter from the timestamp.

Code:

SELECT date_part('quarter', TIMESTAMP '2020-05-19');

Output:

Example #4

Extract month from a timestamp using date_part function.

In the below example, we have used a month as May to extract the month from the timestamp.

Code:

SELECT date_part('month', TIMESTAMP '2010-05-19');

Output:

Example #5

Extract week from a timestamp using date_part function.

In the below example, we have used the year as 2010 and the month as May to extract the week from the timestamp.

Code:

SELECT date_part(week, TIMESTAMP '2010-05-19');

Output:

Example #6

Extract day from a timestamp using the date_part function.

In the below example, we have used the year as 2020 and the month as May to extract the day from the timestamp.

Code:

SELECT date_part('day',TIMESTAMP '2020-05-19 00:00:00');

Output:

Example #7

Extract hour, minute and second from a timestamp using the date_part function.

The below example shows that Extract hour, minute and second from a timestamp using the date_part function.

Code:

SELECT date_part('hour', TIMESTAMP '2020-05-19 12:30:40') h,  date_part('minute', TIMESTAMP '2020-05-19 12:30:40') m, date_part('second',TIMESTAMP '2020-05-19 12:30:40') s;

Output:

This is a guide to PostgreSQL DATE_PART(). Here we discuss the introduction to PostgreSQL DATE_PART() with the working and examples, respectively. You may also have a look at the following articles to learn more –

1. PostgreSQL GRANT
2. PostgreSQL Trunc()
3. PostgreSQL Auto Increment
4. PostgreSQL Primary Key