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:
Recommended Articles
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 –
