date_part

date_part('field', source)

Description: The date_part function is implemented based on the traditional Ingres function (this function is equivalent to the SQL standard function extract).

Parameter: The field parameter is a character string instead of a name. Valid field parameters are the same as those in EXTRACT. For details, see EXTRACT.

Example:

    
         gaussdb=# SELECT date_part('day', TIMESTAMP '2001-02-16 20:38:40');
 date_part 
-----------
        16
(1 row)

    
         gaussdb=# SELECT date_part('hour', INTERVAL '4 hours 3 minutes');
 date_part 
-----------
         4
(1 row)

Table 1 Formats for formatting date and time shows the formats that can be used to format date/time values.

**Table 1** Formats for formatting date and time
Category	Format	Description
Hour	HH	Number of hours in one day (01–12)
	HH12	Number of hours in one day (01–12)
	HH24	Number of hours in one day (00–23)
Minute	MI	Minute (00–59)
Second	SS	Second (00–59)
	FF	Microsecond (000000–999999)
	FF1	Microsecond (0–9)
	FF2	Microsecond (00–99)
	FF3	Microsecond (000–999)
	FF4	Microsecond (0000–9999)
	FF5	Microsecond (00000–99999)
	FF6	Microsecond (000000–999999)
	SSSSS	Second after midnight (0–86399)
Morning and afternoon	AM or A.M.	Morning identifier
Morning and afternoon	PM or P.M.	Afternoon identifier
Year	Y,YYY	Year with a comma (with four digits or more)
	SYYYY	Year with four digits BC
	YYYY	Year (with four digits or more)
	YYY	Last three digits of a year
	YY	Last two digits of a year
	Y	Last one digit of a year
	IYYY	ISO year (with four digits or more)
	IYY	Last three digits of an ISO year
	IY	Last two digits of an ISO year
	I	Last one digit of an ISO year
	RR	Last two digits of a year (A year of the 20th century can be stored in the 21st century.)
	RRRR	Capable of receiving a year with four digits or two digits. If there are 2 digits, the value is the same as the returned value of RR. If there are 4 digits, the value is the same as YYYY.
	BC or B.C. AD or A.D.	Era indicator BC or AD
Month	MONTH	Full name of a month in uppercase (9 characters are filled in if the value is empty.)
	MON	Month in abbreviated format in uppercase (with three characters)
	MM	Month (01–12)
	RM	Month in Roman numerals (I–XII; I=JAN) and uppercase
Day	DAY	Full name of a date in uppercase (9 characters are filled in if the value is empty.)
	DY	Day in abbreviated format in uppercase (with three characters)
	DDD	Day in a year (001–366)
	DD	Day in a month (01–31)
	D	Day in a week (1–7).
Week	W	Week in a month (1–5) (The first week starts from the first day of the month.)
	WW	Week in a year (1–53) (The first week starts from the first day of the year.)
	IW	Week in an ISO year (The first Thursday is in the first week.)
Century	CC	Century (with two digits) (The 21st century starts from 2001-01-01.)
Julian date	J	Julian date (starting from January 1 of 4712 BC)
Quarter	Q	Quarter

When a_format_version is set to 10c and a_format_dev_version is set to s1 in an ORA-compatible database, the date and time will be formatted in the specified format.

**Table 2** New formats for formatting date and time
Item	Format	Description
Century	SCC	Century. A hyphen (-) will be displayed before BC years.
Year	SYYYY	Returns a numeric year. A hyphen (-) will be displayed before BC years.
	RR	Returns the two-digit year of a date.
	RRRR	Returns the four-digit year of a date.
	YEAR	Returns the year of the character type.
	SYEAR	Returns the year of the character type. A hyphen (-) will be displayed before BC years.
Date Format	DL	Returns the date in the specified long date format.
	DS	Returns the date in the specified short date format.
	TS	Returns the time in the specified time format.
Second	FF7	Microsecond (0000000–9999990)
	FF8	Microsecond (00000000–99999900)
	FF9	Microsecond (000000000–999999000)

In the table, the rules for RR to calculate years are as follows:

If the range of the input two-digit year is between 00 and 49:
If the last two digits of the current year are between 00 and 49, the first two digits of the returned year are the same as the first two digits of the current year.

If the last two digits of the current year are between 50 and 99, the first two digits of the returned year equal to the first two digits of the current year plus 1.
If the range of the input two-digit year is between 50 and 99:
If the last two digits of the current year are between 00 and 49, the first two digits of the returned year are equal to the first two digits of the current year minus 1.

If the last two digits of the current year are between 50 and 99, the first two digits of the returned year are the same as the first two digits of the current year.

In the scenario where this function is in an ORA-compatible database, the value of a_format_version is 10c, and that of a_format_dev_version is s1:

The to_date and to_timestamp functions support the FX pattern (the input strictly corresponds to a pattern) and the X pattern (decimal point).
The input pattern can appear only once, indicating that the patterns of the same information cannot appear at the same time. For example, SYYYY and BC cannot be used together.
The pattern is case-insensitive.
You are advised to use a separator between the input and the pattern. Otherwise, the behavior may not be compatible with database O.

Parent topic: Date and Time Processing Functions and Operators

Previous topic: EXTRACT

Next topic: Type Conversion Functions