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:
1 2 3 4 5 |
gaussdb=# SELECT date_part('day', TIMESTAMP '2001-02-16 20:38:40'); date_part ----------- 16 (1 row) |
1 2 3 4 5 |
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.
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 |
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. |
|
|
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 |
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.

- 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.
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot