Introduction to PostgreSQL DATE_PART()
PostgreSQL DATE_PART() function is mainly used to return the part of the date and time, date_part function in PostgreSQL will subtract the subfield from the date and time value. PostgreSQL date_part function will allow to retrieve subfields e.g. week, month and year from date and time value. Basically there are two parameter we have pass with date_part function i.e. field and source, field determines that which field we have extracting from the source.
Syntax:
Given below is the syntax of 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 argument with date_part function as text and timestamp in PostgreSQL.
- Text: Text is first parameter which we have used with date_part function in PostgreSQL.
- Timestamp: This is second parameter of date_part function in PostgreSQL. We have used to define the timestamp value with date_part function in PostgreSQL.
- Interval: Interval is used with same as timestamp value in PostgreSQL. We can use interval instead of timestamp value in PostgreSQL.
- Unit: Unit type in date_part function is interval such as minute, hour and month. Unit is very important parameter in date_part function.
- Date: This is defined as date, timestamp, time or interval value which is used to be part executed.
- Field: This is parameter of date_part function in PostgreSQL used to which field to extract from the source.
- Source: Source is defined in date_part function as second parameter. This is same as interval parameter in 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 date_part function. Using 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 date_part function.
In PostgreSQL unit type have following form used in date_part function. Unit type is nothing but a day, month, minute and hour.
- Century: It uses the Gregorian calendar in which first century will start from the ‘0001-01-01 00:00:00 AD’.
- Day: This is defined as 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 day of the week from Sunday to Saturday.
- Day of year: This is defined as day of the year from January to December.
- Epoch: This is defined as number of interval value in date_part function.
- Hour: This is defined as hour of the day from 0 to 23.
- Millennium: This is defined as millennium value in date_part function.
- Milliseconds: This is defined as second multiply with 1000.
- Microseconds: This is defined as second multiply with 100000.
- Minute: This is defined as 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 date_part function.
- Year: This is defined as year in four digit value.
To extract the second, minute and hour from timestamp by using 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 timestamp using date_part function.
In below example we have used year as 2020 and 1920 to extract century from timestamp.
Code:
SELECT date_part('century', TIMESTAMP '2020-05-19');
SELECT date_part('century', TIMESTAMP '1920-05-19');
Output:
In above example using year as 1920 it will display century as 20 and using year as 2020 it will display century as 21.
Example #2
Extract year from timestamp using date_part function.
In below example we have used year as 2020 to extract year from timestamp.
Code:
SELECT date_part('year', TIMESTAMP '2020-05-19');
Output:
Example #3
Extract quarter from timestamp using date_part function.
In below example we have used year as 2020 to extract quarter from timestamp.
Code:
SELECT date_part('quarter', TIMESTAMP '2020-05-19');
Output:
Example #4
Extract month from timestamp using date_part function.
In below example we have used month as May to extract month from timestamp.
Code:
SELECT date_part('month', TIMESTAMP '2010-05-19');
Output:
Example #5
Extract week from timestamp using date_part function.
In below example we have used year as 2010 and month as May to extract week from timestamp.
Code:
SELECT date_part(week, TIMESTAMP '2010-05-19');
Output:
Example #6
Extract day from timestamp using date_part function.
In below example we have used year as 2020 and month as May to extract day from timestamp.
Code:
SELECT date_part('day',TIMESTAMP '2020-05-19 00:00:00');
Output:
Example #7
Extract hour, minute and second from timestamp using date_part function.
Below example shows that Extract hour, minute and second from timestamp using 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 –
