PostgreSQL 7.4 Document
Prev Fast Backward Chapter 9. Function and Operator Fast Forward Next
-------------------------------------------------- ------------------------------
9.7. Data type format function
The PostgreSQL format function provides a set of valid tools for converting various data types (date / time, integer, floating point, numeric) to formatted strings and in turn to convert from the formatted string into designated data. Types of. TABLE 9-20 lists these functions. These functions follow a public call habit: The first parameter is a value to be formatted, and the second is a template defining the output format.
Table 9-20. Format Function
Function Return Type Description Example
TO_CHAR (TIMESTAMP, TEXT) TEXT converts timestamp to string to_char (current_timestamp, 'hh12: mi: ss')
TO_CHAR (INTERVAL, TEXT) TEXT converts time intervals into strings to_char (Interval '15h 2M 12S', 'HH24: MI: SS')
TO_CHAR (INT, TEXT) TEXT converts integers into strings to_Char (125, '999')
TO_CHAR (DOUBLE PRECISION, TEXT) TEXT converts real / double precision to a string to_Char (125.8, '999d9)
TO_CHAR (NUMERIC, TEXT) TEXT converts NUMERIC to string to_char (numeric '-125.8', '999d99s')
TO_DATE (TEXT, TEXT) DATE converts strings into date to_date ('05 DEC 2000 ',' DD MON YYYY ')
TO_TIMESTAMP (TEXT, TEXT) DATE Converts strings to timestamp TO_TIMESTAMP ('05 DEC 2000 ',' DD MON YYYY ')
TO_NUMBER (Text, Text) NUMERIC converts strings into Numeric to_Number ('12, 454.8- ',' 99G999D9S ')
WARNING: To_char (interval, text) has been discarded, should not be used again in the new code. It will be deleted in the next version.
In the output template string (for TO_CHAR), the function family can identify some specific modes and correctly format the value 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 (anything to TO_CHAR), the template mode identifies the input data string to view and will look at the value in this location.
Table 9-21 shows a template that can be used to format the date and time value.
Table 9-21. Templates for Date / Time Format
Template description
HH day number of hours (01-12)
HH12 day number of hours (01-12)
HH24 a day number (00-23)
Mi minutes (00-59)
SS Second (00-59)
MS milliseconds (000-999)
US microseconds (000000-999999)
SSSS seconds after midnight (0-86399)
AM or A.M. or PM or P.M. Noon logo (uppercase)
AM or A.M. or PM or P.M. Noon logo (lowercase)
Y, YYY year (4 and more)
YYYY year (4 and more)
After YYY years
After YY years
The last one of Ye
BC or B.c. or AD or A.D. Era ID (uppercase)
BC or B.c. or AD or A.D. Era ID (lowercase)
Month Full-length Word Month Name (Blank Fill is 9 characters)
Month Full length mixed case month (blank padding 9 characters)
Month full length lowercase name (blank filled with 9 characters)
MON capitalization shrink month (3 characters)
Mon abbreviation hybrid case month (3 characters)
MON lowercase diagram (3 characters)
MM Moon (01-12)
DAY full length written date name (blank filled with 9 characters)
DAY full length hybrid case date name (blank fill is 9 characters)
DAY full length lowercase date name (blank filled with 9 characters)
DY abbreviation capital date name (3 characters)
DY abbreviation hybrid case date (3 characters)
DY abbreviation lowercase date name (3 characters)
DDD days in the year (001-366)
DD a month (01-31)
D. Day in the week (1-7; Sunday = 1)
W One month (1-5) (1-5) (first day of the first day)
WW One year (1-53) (1-53) (first week from the first day of the year)
IW iso's week in the year (the first Thursday in the first week)
CC Century (2)
J Confucian Day (from January 1, 4712 BC)
Q quarter
RM Roman Numbers (i-xii; i = jan) (uppercase)
RM Roman digital month (i-xii; i = jan) (lowercase)
TZ time zone string (uppercase)
TZ time zone (lowercase)
Some modifiers can be applied to templates to modify their behavior. For example, Fmmonth is a Month mode with an FM prefix. Table 9-22 shows a modified word pattern for date / time formatting.
Table 9-22. Date / Time Format Template Model Memories
Decorative word description example
FM prefix fill mode (suppression fill blank and zero) FMMONTH
TH suffix capital sequence numerical suffix DDTH
TH suffix lowercase sequence number suffix DDTH
FX prefix fixed format global option (see note)> FX MONTH DD DAY
SP SUFFIX spelling mode (still not implemented) DDSP
Date / time formatting usage notice:
FM suppresses the zero or trailing blank of the preamble. If there is no use, add these fills in the output finally turn the output into a fixed width mode.
If the FX option is not used, TO_TIMESTAMP and TO_DATE ignore multiple blank when the string is converted. FX must be a first declaration in the template. For example, to_timestamp ('2000 jun', 'YYYY MON') is correct, to_timestamp ('2000 jun', 'fxyyy ") will return an error, because TO_TimeStamp only expects a blank. There may be ordinary text in the To_Char template, and they will be used as illustrated. You can put a string into a double quotation number to force it to explain into a text, even if it contains a mode keyword. For example, in '"Hello Year", YYYY will be replaced by the year data, but Year's separate Y will not.
If you want to have a double quotation in the output, you must put double reverselaxing in front of them, such as '// "YYYY MONTH ///"'. (Requires two backslash because the backslash in the string constant There is already a special meaning.)
If you use the year longer than 4 characters, you should be restricted when you use YYYY to switch from the string to TimeStamp or Date. You must use some non-digital characters or templates later behind YYYY, otherwise always interprets 4 digits. For example (for 20000): to_date ('200001131', 'YYYYMMDD') will be interpreted as a 4-digit year, it is best to use a non-digital separator after the year, like to_date ('20000-1131' , 'YYYY-MMDD') or to_date ('20000nov31', 'YYYYMONDD').
When the string is converted to TimeSTAMP, milliseconds (MS) and microseconds (US) are partially converted back from the decimal point of the string. For example, to_timestamp ('12: 3 ',' s: ms') is not 3 milliseconds, but 300, because the conversion looks 12 0.3 seconds. This means that the input value is 12: 3 or 12:30 or 12: 300 in milliseconds for the input value of 12: 3 or 12:30 or 12: 300. For three milliseconds, you have to use 12: 003, then the conversion will see it as 12 0.003 = 12.003 seconds.
Here is a more complex example: to_timestamp ('15: 12: 02.020.001230', 'hh: mi: ss.ms.us') is 15 hours, 12 minutes, and 2 seconds 20 milliseconds 1230 microsecond = 2.021230 seconds.
Table 9-23 shows a template that can be used in numerical formatting.
Table 9-23. Template mode for numeric formatting
Template description
9 with a value specified by the specified numerical bit
0 values with front guide zero
(A fiction) decimal point
, (Comma) group (thousand) separator
PR anglenenel
S band symbol value (use area setting)
L Currency symbol (use area settings)
D decimal point (use area setting)
G Packet Division (Use area setting)
Mi is negative in the indicated position (if the number <0)
PL of the positive number (if the number> 0) SG in the specified position is the positive / negative number of the indicated position
RN Rome Digital (input between 1 and 3999)
TH or TH sequence suffix
V Moves northern positioning (decimal) (see annotation)
Eeee scientific count. (Not yet implemented)
Usage of digital formatting:
The symbols generated using SG, PL or MI do not hang on the top; for example, to_CHAR (-12, 'S9999') generates '-12', and to_CHAR (-12, 'MI9999') generates '- 12'. The implementation of Oracle does not allow Using MI in front of 9, but requires 9 in front of MI.
9 Declaration and 9 of the number of digits of the same number of digits. If a value bit is no number, you will output a blank.
TH will not convert a value of less than zero, nor does it convert the decimal.
PL, SG and TH are PostgreSQL extensions.
V Multiply the input value by 10 ^ n, where n is a number followed behind V. TO_CHAR does not support the use of V with a decimal point (that is, 99.9v99 is not allowed).
Table 9-24 shows some usage using the To_Char function.
TABLE 9-24. TO_CHAR example
Expression results
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') '0.1'
TO_CHAR (12, '9990999.9)' 0012.0 '
TO_CHAR (12, 'FM9990999.9)' 0012. '
TO_CHAR (485, '999') '485'
TO_CHAR (-485, '999)' -485 '
TO_CHAR (485, '9 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, 'FM999.999') '148.5'
TO_CHAR (148.5, 'FM999.990') '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, 'FM999MI') '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:" 999 "POST:" .999') 'pre: 485 post: .800'
TO_CHAR (12, '99v999') '12000'
TO_CHAR (12.4, '99v999') '12400'
To_Char (12.45, '99v9') '125'
-------------------------------------------------- ------------------------------
Prev Home next
Mode matching UP time / date function and operator
