To_char function Reference (RPM)

Source: Internet
Author: User
Tags month name

The Postgres Format function provides a set of effective tools for converting various data types (date/time, int,float,numeric) into formatted strings and, in turn, converting from formatted strings to raw data types.

Note: The second parameter of all formatting functions is the template used for the transformation.

table 5-7. Formatting functions

function return Description Example
To_char (timestamp, text) Text Convert Timestamp to String To_char (timestamp ' Now ', ' HH12:MI:SS ')
To_char (int, text) Text Convert Int4/int8 to String To_char (125, ' 999 ')
To_char (Float, text) Text Convert Float4/float8 to String To_char (125.8, ' 999d9 ')
To_char (Numeric, text) Text Convert numeric to String To_char (Numeric ' -125.8 ', ' 999d99s ')
To_date (text, text) Date Convert String to date To_date (' Dec ', ' DD Mon YYYY ')
To_timestamp (text, text) Date Convert String to Timestamp To_timestamp (' Dec ', ' DD Mon YYYY ')
To_number (text, text) Numeric Convert string to Numeric To_number (' 12,454.8-', ' 99g999d9s ')

table 5-8. Templates for Date/time conversions

Templates Description
HH Number of hours in the day (01-12)
HH12 Number of hours in the day (01-12)
HH24 Number of hours in the day (00-23)
MI Minutes (00-59)
Ss Seconds (00-59)
SSSS Seconds after midnight (0-86399)
AM or a.m. or PM or p.m. Noon SIGN (uppercase)
Am or a.m. or PM or p.m. Noon Logo (lowercase)
Y,yyy Year with commas (4 and more bits)
YYYY Year (4 and more bits)
YYY The latter three digits of the year
Yy The latter two of the years
Y The last one of the years
BC or B.C. or AD or A.D. Year identifier (uppercase)
BC or B.C. or ad or A.D. Year logo (lowercase)
MONTH Full-length uppercase month name (9 characters)
Month Full-length mixed-Case Month name (9 characters)
Month Full-length lowercase month name (9 characters)
MON Capital Abbreviation month name (3 characters)
Mon Abbreviated mixed Case month name (3 characters)
Mon lowercase abbreviation month name (3 characters)
Mm Month (01-12)
Day Full capitalization date name (9 characters)
Day Full-length mixed-Case Date name (9 characters)
Day Full-Length Lowercase date name (9 characters)
Dy Abbreviated Capital Date name (3 characters)
Dy Abbreviated mixed case date name (3 characters)
Dy Abbreviated Lowercase date name (3 characters)
Ddd The days of the Year (001-366)
Dd One months of life (01-31)
D Days of the Week (1-7;sun=1)
W Number of weeks in one months
Ww Number of weeks in a year
Cc Century (2-bit)
J Julian Date (dates from January 1, 4712 BC)
Q Quarter
Rm Roman numerals Month (I-xii;i=jan)-Uppercase
Rm Roman numerals Month (I-xii;i=jan)-lowercase

All templates allow the use of prefix and suffix modifiers. The modifier is always allowed in the template. The prefix 'FX' is just a global modifier.

table 5-9. Suffix for date/time template to_char()

suffix Description Example
Fm Fill mode prefix Fmmonth
TH Uppercase Order number suffix Ddth
Th lowercase order number suffix Ddth
Fx Fixed mode global options (see below) FX Month DD Day
Sp Spelling mode (not yet implemented) Ddsp

Notes on usage:

    • If you do not use the FX option,to_timestamp and to_date ignore whitespace. FX must be declared as the first entry in a template.
    • The backslash ("\") must be used as a double backslash ("\ \"), such as ' \\HH\\MI\\SS '.
    • The string between the double quotation marks (' "') is ignored and is not parsed. If you want to write double quotes to the output, you must place a double backslash (' \ \ ') in front of the double quotation mark, for example ' \ \ ' YYYY month\\'.
    • To_char supports text without leading double quotation marks (' "'), but any string between double quotes is processed quickly and is also guaranteed not to be interpreted as a template keyword (for example:'" Hello Year: "YYYY ').

table 5-10. Templates for To_char(numeric)

Templates Description
9 Value with the specified number of digits
0 The value of the leading zero
. Period decimal Point
, (comma) Grouping (Thousand) separators
PR Negative value inside angle brackets
S Negative value with minus sign (using localization)
L Currency symbol (using localization)
D decimal point (using localization)
G Grouping separators (using localization)
MI Minus sign at the indicated position (if the number < 0)
Pl A plus sign at the indicated position (if the number > 0)
SG Positive/negative sign at the indicated position
Rn Roman numerals (input between 1 and 3999)
th or th Convert to Ordinal
V Move n bits ( decimal ) (see annotations)
Eeee Science counts. Not supported now.

Notes on usage:

    • using ' SG ', ' PL ' or ' MI ' signed words are not attached to numbers, for example, To_char ( -12, ' S9999 ') generates   ' -12 ' , and  < Strong>to_char ( -12, ' MI9999 ') generates &NBSP; '-N ' . Oracle   implementations are not allowed in   9   Front use   MI , but requires   9   in   MI   front.
    • PL , SG , and   TH   is  postgres  extension.
    • 9   Indicates the same number of digits as in   9   string. If no numbers are available, use a blank (space).
    • TH   do not convert values less than 0, and do not convert decimal . TH   is a  postgres  extension.
    • V   Conveniently multiply the input value by   10^ n , here   n   is followed   V   subsequent digits. To_char   Do not support the   V   is tied to a decimal point (for example. "99.9v99" is not allowed.)

table 5-11. To_char Example

input Output
To_char (Now (), ' Day, HH12:MI:SS ') ' Tuesday, 05:39:18 '
To_char (Now (), ' Fmday, HH12:MI:SS ') ' Tuesday, 05:39:18 '
To_char (-0.1, ' 99.99 ') '-.10 '
To_char ( -0.1, ' FM9.99 ') '-.1 '
To_char (0.1, ' 0.9 ') ' 0.1 '
To_char (12, ' 9990999.9 ') ' 0012.0 '
To_char (' FM9990999.9 ') ' 0012 '
To_char (485, ' 999 ') ' 485 '
To_char (-485, ' 999 ') '-485 '
To_char (485, ' 9 9 9 ') ' 4 8 5 '
To_char (1485, ' 9,999 ') ' 1,485 '
To_char (1485, ' 9g999 ') ' 1 485 '
To_char (148.5, ' 999.999 ') ' 148.500 '
To_char (148.5, ' 999d999 ') ' 148,500 '
To_char (3148.5, ' 9g999d999 ') ' 3 148,500 '
To_char ( -485, ' 999S ') ' 485-'
To_char ( -485, ' 999MI ') ' 485-'
To_char (485, ' 999MI ') ' 485 '
To_char (485, ' PL999 ') ' +485 '
To_char (485, ' SG999 ') ' +485 '
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:485 '
To_char (485.8, ' "Pre-decimal:" 999 "Post-decimal:". 999 ') ' pre-decimal:485 post-decimal:. 800 '
To_char (' 99v999 ') ' 12000 '
To_char (12.4, ' 99v999 ') ' 12400 '
To_char (12.45, ' 99v9 ') ' 125 '

To_char function Reference (RPM)

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.