Data Type Formatting Function (PostgreSQL), supported by Oracle

9.8. Data Type Formatting Function

PostgreSQLThe Formatting Function provides a set of effective tools for various data types (date/time, integer, floating point, numeric) convert to a formatted string and, in turn, convert from a formatted string to a specified data type. Table 9-20 lists these functions. These functions follow a common call habit: the first parameter is the value to be formatted, and the second is a template that defines the output or output format.

To_timestampThe function can also acceptDouble PrecisionParameter to convert it from the Unix eraTimestamp with Time Zone. (IntegerThe UNIX epochs are implicitly convertedDouble Precision.)

Table 9-20. Formatting Function

Function	Return type	Description	Example
To_char(Timestamp,Text)	Text	Converts a timestamp to a string.	To_char (current_timestamp, 'hh12: MI: ss ')
To_char(Interval,Text)	Text	Convert the time interval into a string	To_char (interval '15h 2 m 12s', 'hh24: MI: ss ')
To_char(Int,Text)	Text	Converts an integer to a string.	To_char (125, '123 ')
To_char(Double Precision,Text)	Text	Convert real numbers/double-precision numbers into strings	To_char (125.8: Real, '999d9 ')
To_char(Numeric,Text)	Text	Converts numeric to a string.	To_char (-125.8, '999d99s ')
To_date(Text,Text)	Date	Converts a string to a date.	To_date ('05 Dec 2000 ', 'dd mon yyyy ')
To_timestamp(Text,Text)	Timestamp with Time Zone	Converts a string to a timestamp.	To_timestamp ('05 Dec 2000 ', 'dd mon yyyy ')
To_timestamp(Double Precision)	Timestamp with Time Zone	Converts a Unix epoch to a timestamp.	To_timestamp (200120400)
To_number(Text,Text)	Numeric	Convert string to numeric	To_number ('1970-', '99g999d9s ')

In the output template string (To_charThis function family can recognize some specific modes and correctly format the values to be formatted into the corresponding data. Any text that does not belong to the template mode is simply copied. Similarly, in an input template string (To_charThe template mode identifies the input data string to be viewed, and searches for a value at this position.

Table 9-21 displays templates that can be used to format Date and Time values.

Table 9-21. Format for date/time formatting

Mode	Description
HH	Hours per day (01-12)
Hh12	Hours per day (01-12)
Hh24	Hours per day (00-23)
Mi	Minute (00-59)
SS	Seconds (00-59)
MS	Millisecond (000-999)
Us	Microseconds (000000-999999)
Ssss	Seconds after midnight (0-86399)
AMOrA.m.OrPMOrP. M.	Midday logo (uppercase)
AMOrA.m.OrPMOrP. M.	Midday logo (lower case)
Y, yyy	Year with commas)
Yyyy	Year (4 and more)
Yyy	The last three digits of the year
YY	The last two
Y	The last digit of the year
Iyyy	ISO year (four or more digits)
Iyy	The last three digits of the ISO year
Iy	The last two digits of the ISO year
I	The last digit of the ISO year
BCOrB .C.OrAdOrA.D.	Epoch ID (uppercase)
BCOrB .C.OrAdOrA.D.	Epoch ID (lower case)
Month	Full-length uppercase month name (blank filled with 9 characters)
Month	Full-length mixed-case month name (blank filled with 9 characters)
Month	Full-length lowercase month name (blank filled with 9 characters)
Mon	Abbreviated month name (3 characters)
Mon	Abbreviation: Mixed-case month name (3 characters)
Mon	Lowercase abbreviation month name (3 characters)
Mm	Month (01-12)
Day	Full-length uppercase Date name (blank filled with 9 characters)
Day	Full-length mixed-case Date name (blank filled with 9 characters)
Day	Full-length lowercase Date name (blank filled with 9 characters)
Dy	Abbreviation: Capital Date name (3 characters)
Dy	Abbreviation: Mixed-case Date name (3 characters)
Dy	Lowercase Date name (3 characters)
Ddd	Days in a year (001-366)
Dd	Days in a month (01-31)
D	Days in a week (1-7; Sunday is 1)
W	The number of weeks in a month (1-5) (the first week begins on the first day of the month)
WW	The number of weeks in a year (1-53) (the first week begins on the first day of the year)
IW	Week number in ISO year (first Thursday in first week)
CC	Century (second place)
J	Ruru Day (the number of days since January 1, January 1, 4712 BC)
Q	Quarter
Rm	The month of the Roman numerals (I-XII; I = Jan) (uppercase)
Rm	The month of the Roman numerals (I-XII; I = Jan) (lower case)
TZ	Time zone name (uppercase)
TZ	Time zone name (lower case)

Some modifiers can be applied to templates to modify their behavior. For example,FmmonthIs carryingFMPrefixMonthMode. Table 9-22 displays the modifier mode for date/time formatting.

Table 9-22. template pattern modifier formatted by date/time

Modifier	Description	Example
FMPrefix	Fill mode (Suppress fill blank and zero)	Fmmonth
ThSuffix	Suffix of numbers in uppercase order	Ddth
ThSuffix	Suffix of lowercase sequence numbers	Ddth
FXPrefix	Fixed format global options (see usage instructions)	> FX month dd Day
SPSuffix	Spelling mode (not implemented yet)	Ddsp

Instructions for date/time formatting:

 

 

• FMSuppress leading zero or trailing white spaces. If it is not used, the padding will be added to the output to convert the output to a fixed width mode.

• If notFXOption,To_timestampAndTo_dateMultiple white spaces are ignored during String Conversion.FXMust be the first declaration in the template. For exampleTo_timestamp ('1970 jun', 'yyyy mon ')Yes,To_timestamp ('1970 jun', 'fxyyyy mon ')An error is returned becauseTo_timestampOnly one blank space is expected.

• InTo_charThe template can contain common text, and they are output as photos. You can place a string in double quotation marks and force it to be interpreted as a text, even if it contains a pattern keyword. For example'"Hello year" YYYY',YyyyWill be replaced by year data,YearSeparateYNo.

• If you want to include double quotation marks in the output, you must put a double backslash before them, such'\ "YYYY month \\"'. (Two backslashes are required because the backslash has a special meaning in the String constant .)

• If you use a year that is longer than 4 characters long, useYyyyFrom stringTimestampOrDateThe conversion is limited. You mustYyyyThe following uses non-numeric characters or templates. Otherwise, the year is always interpreted as a four-digit number. For example (for 20000 years ):To_date ('20140901', 'yyyymmdd ')It will be interpreted as a four-digit year. It is better to use a non-digit separator after the year, likeTo_date ('1970-20000 ', 'yyyy-mmdd ')OrTo_date ('20000nov31 ', 'yyyymmdd ').

• In the string directionTimestampOrDateIfYyy,YyyyOrY, yyyField, thenCCFields are ignored. IfCCAndYYOrYUsed Together, then the year uses the formula(CC-1) * 100 + YYCalculated.

• Converts a stringTimestampIn milliseconds (MS) And microseconds (Us) Are converted using the decimal point of the string. For exampleTo_timestamp ('12: 3', 'ss: Ms ')It is not 3 ms, but 300, because the conversion treats it as 12 + 0.3 seconds. This means that for the format 'ss: Ms', the input value is12: 3Or12: 30Or12: 300The same number of milliseconds are declared. For three milliseconds, you must use12: 003, The conversion will regard it as 12 + 0.003 = 12.003 seconds.

  The following is a more complex example:To_timestamp ('15: 12: 02.00000001230 ', 'hh: MI: Ss. Ms. us ')It is 15 hours, 12 minutes, and 2 seconds + 20 milliseconds + 1230 microseconds = 2.021230 seconds.

• To_charThe day of the week (see 'D' Format mode) andExtract.

 

Table 9-23 displays the template mode that can be used for numerical formatting.

Table 9-23. template mode for numerical formatting

Mode	Description
9	Value with the specified number of digits
0	Value with leading zero
.(Period)	Decimal point
,(Comma)	Group (thousands) Separator
PR	Negative value in angle brackets
S	Signed value (use region settings)
L	Currency symbol (use region settings)
D	Decimal point (use region settings)
G	Group separator (use region settings)
Mi	Negative number at the specified position (if the number is <0)
PL	The positive number at the specified position (if the number is greater than 0)
SG	Positive/negative signs at the specified position
Rn	Roman numerals (input between 1 and 3999)
ThOrTh	Ordinal suffix
V	Move refers to positioning (decimal) (see annotation)
Eeee	Scientific notation. (Not implemented yet)

Instructions for formatting numbers:

 

 

• UseSG,PLOrMiThe generated symbol is not attached to a number. For example,To_char (-12, 's999999 ')Generate'-12', AndTo_char (-12, 'mi999999 ')Generate'-12'. The implementation in Oracle is not allowed in9Previously UsedMi, But requires9InMiFront.

• 9Description and9The number of digits with the same number. If a numeric bit does not have a number, a blank space is output.

• ThIt does not convert numeric values smaller than zero or decimal places.

• PL,SGAndThYesPostgreSQLExtension.

• VMultiply the input value conveniently10 ^N, HereNIs followedVThe following number.To_charDo not supportVUsed together with a decimal point (that is,99.9v99Is not allowed ).

 

Table 9-24 shows some usageTo_charFunction usage.

Table 9-24.To_charExample

Expression	Result
To_char (current_timestamp, 'day, DD hh12: MI: ss ')	'Tuesday, 06 05:39:18'
To_char (current_timestamp, 'fmday, fmdd hh12: MI: ss ')	'Tuesday, 6 05:39:18'
To_char (-0.1, '99. 99 ')	'-. 10'
To_char (-0.1, 'fm9. 99 ')	'-. 1'
To_char (0.1, '0. 9 ')	'123'
To_char (12, '192. 9 ')	'123'
To_char (12, 'fm9990999. 9 ')	'2017 .'
To_char (485, '123 ')	'123'
To_char (-485, '123 ')	'-485'
To_char (485, '9 9 9 ')	'48 5'
To_char (1485, '123 ')	'123'
To_char (1485, '9g999 ')	'1 100'
To_char (148.5, '192. 100 ')	'123'
To_char (148.5, 'fm999. 100 ')	'1970. 5'
To_char (148.5, 'fm999. 100 ')	'1970. 123'
To_char (148.5, '999d999 ')	'123'
To_char (3148.5, '9g999d999 ')	& Apos; 3 & apos; 148,500 & apos'
To_char (-485, '999s ')	'2017 -'
To_char (-485, '999mi ')	'2017 -'
To_char (485, '999mi ')	'123'
To_char (485, 'fm999mi ')	'123'
To_char (485, 'pl999 ')	'+ 123'
To_char (485, 'sg999 ')	'+ 123'
To_char (-485, 'sg999 ')	'-485'
To_char (-485, '9sg99 ')	'4-85'
To_char (-485, '999pr ')	'<485>'
To_char (485, 'l999 ')	'Dm 485
To_char (485, 'rn ')	'Cdlxxxv'
To_char (485, 'fmrn ')	'Cdlxxxv'
To_char (5.2, 'fmrn ')	'V'
To_char (482, '999th ')	'482nd'
To_char (485, '"good number:" 999 ')	'Good number: 8080'
To_char (485.8, '"pre:" 999 "post:". 999 ')	'Pre: 485 post:. 123'
To_char (12, '99v999 ')	'123'
To_char (12.4, '99v999 ')	'123'
To_char (12.45, '99v9 ')	'123'