Postgresql 中文操作指南
9.8. Data Type Formatting Functions #
PostgreSQL 格式化函数提供了一组功能强大的工具，用于将各种数据类型（日期/时间、整数、浮点数、数字）转换为格式化字符串，并用于将格式化字符串转换为特定数据类型。 Table 9.26 中列出了它们。这些函数都遵循一个通用的调用约定：第一个自变量是要格式化的值，第二个自变量是定义输出或输入格式的模板。
Table 9.26. Formatting Functions
Function Description Example(s) |
to_char ( timestamp , text ) → text to_char ( timestamp with time zone , text ) → text 根据给定的格式将时间戳转换为字符串。 to_char(timestamp '2002-04-20 17:31:12.66', 'HH12:MI:SS') → 05:31:12 |
to_char ( interval , text ) → text 根据给定的格式将间隔转换为字符串。 to_char(interval '15h 2m 12s', 'HH24:MI:SS') → 15:02:12 |
to_char ( numeric_type , text ) → text 根据给定的格式将数字转换为字符串；可用于 integer , bigint , numeric , real , double precision 。 to_char(125, '999') → 125 to_char(125.8::real, '999D9') → 125.8 to_char(-125.8, '999D99S') → 125.80- |
to_date ( text , text ) → date 根据给定的格式将字符串转换为日期。 to_date('05 Dec 2000', 'DD Mon YYYY') → 2000-12-05 |
to_number ( text , text ) → numeric 根据给定的格式将字符串转换为数字。 to_number('12,454.8-', '99G999D9S') → -12454.8 |
to_timestamp ( text , text ) → timestamp with time zone 根据给定的格式将字符串转换为时间戳。（另请参阅 Table 9.33 中的 to_timestamp(double precision) 。） to_timestamp('05 Dec 2000', 'DD Mon YYYY') → 2000-12-05 00:00:00-05 |
Tip
to_timestamp 和 to_date 用于处理无法通过简单转换转换的输入格式。对于大多数标准的日期/时间格式，只需将源字符串强制转换为所需的数据类型即可，并且更容易。同样，to_number 对于标准数字表示也是不必要的。
在 to_char 输出模板字符串中，某些模式会被识别并根据给定值替换为格式适当的数据。任何不是模板模式的文本都将原样复制。类似地，在输入模板字符串（对于其他函数）中，模板模式标识由输入数据字符串提供的值。如果模板字符串中存在不是模板模式的字符，则输入数据字符串中对应的字符将被简单地跳过（无论它们是否等于模板字符串字符）。
Table 9.27 显示了可用于格式化日期和时间值的模板模式。
Table 9.27. Template Patterns for Date/Time Formatting
Pattern |
Description |
HH |
hour of day (01–12) |
HH12 |
hour of day (01–12) |
HH24 |
hour of day (00–23) |
MI |
minute (00–59) |
SS |
second (00–59) |
MS |
millisecond (000–999) |
US |
microsecond (000000–999999) |
FF1 |
tenth of second (0–9) |
FF2 |
hundredth of second (00–99) |
FF3 |
millisecond (000–999) |
FF4 |
十分之一毫秒 (0000-9999) |
FF5 |
百分之一毫秒 (00000-99999) |
FF6 |
microsecond (000000–999999) |
SSSS, SSSSS |
seconds past midnight (0–86399) |
AM 、 am 、 PM 或 pm |
meridiem indicator (without periods) |
A.M. 、 a.m. 、 P.M. 或 p.m. |
meridiem indicator (with periods) |
Y,YYY |
带逗号的年份（4 位数或更多） |
YYYY |
年份（4 位数或更多） |
YYY |
年份最后 3 位 |
YY |
年份最后 2 位 |
Y |
last digit of year |
IYYY |
ISO 8601 周编号年份（4 位数或更多） |
IYY |
ISO 8601 周编号年份最后 3 位 |
IY |
ISO 8601 周编号年份最后 2 位 |
I |
ISO 8601 周编号年份最后 1 位 |
BC 、 bc 、 AD 或 ad |
era indicator (without periods) |
B.C. 、 b.c. 、 A.D. 或 a.d. |
era indicator (with periods) |
MONTH |
完整的全部大写月份名称（空白填充至 9 个字符） |
Month |
完整的首字母大写月份名称（空白填充至 9 个字符） |
month |
完整的全部小写月份名称（空白填充至 9 个字符） |
MON |
全部大写的缩写月份名称（英文为 3 个字符，其他语言的长度会有所不同） |
Mon |
首字母大写缩写月份名称（英文为 3 个字符，其他语言的长度会有所不同） |
mon |
英文中缩写的小写月份名称（3 个字符，本地化长度可能有所不同） |
MM |
month number (01–12) |
DAY |
完整的全大写星期名称（用空格填充至 9 个字符） |
Day |
完整的首字母大写的星期名称（用空格填充至 9 个字符） |
day |
完整的全小写星期名称（用空格填充至 9 个字符） |
DY |
英文中缩写的全大写星期名称（3 个字符，本地化长度可能有所不同） |
Dy |
英文中缩写的首字母大写的星期名称（3 个字符，本地化长度可能有所不同） |
dy |
英文中缩写的小写星期名称（3 个字符，本地化长度可能有所不同） |
DDD |
day of year (001–366) |
IDDD |
ISO 8601 周编号年的日期（001–371；本年的第 1 天是第一个 ISO 周的星期一） |
DD |
day of month (01–31) |
D |
每周中的日期，从星期日（ 1 ）到星期六（ 7 ） |
ID |
ISO 8601 周中的日期，从星期一（ 1 ）到星期日（ 7 ） |
W |
每月第几周（1-5）（第一周始于每月的第一天） |
WW |
每年的第几周（1-53）（第一周始于本年的第一天） |
IW |
ISO 8601 周编号年的第几周（01-53；本年的第一个星期四属于第 1 周） |
CC |
世纪（2 位数字）（21 世纪始于 2001-01-01） |
J |
儒略日（自公元前 4714 年 11 月 24 日当地午夜起的整数日数；参见 Section B.7 ） |
Q |
quarter |
RM |
采用罗马数字大写字母表示的月份（I-XII；I=1 月） |
rm |
采用罗马数字小写字母表示的月份（i-xii；i=1 月） |
TZ |
采用全大写字母表示的时区缩写（仅在 to_char 中受支持） |
tz |
采用全小写字母表示的时区缩写（仅在 to_char 中受支持） |
TZH |
time-zone hours |
TZM |
time-zone minutes |
OF |
与 UTC 的时区偏移（仅在 to_char 中受支持） |
修改符可应用于任何模板模式以更改其行为。例如，FMMonth 是 Month 模式，带有 FM 修改符。 Table 9.28 显示了日期/时间格式化的修改符模式。
Table 9.28. Template Pattern Modifiers for Date/Time Formatting
Modifier |
Description |
Example |
FM prefix |
填充模式（抑制前导零和填充空白） |
FMMonth |
TH suffix |
大写序号后缀 |
DDTH, e.g., 12TH |
th suffix |
小写序号后缀 |
DDth, e.g., 12th |
FX prefix |
固定格式全局选项（参见用法说明） |
FX Month DD Day |
TM prefix |
翻译模式（基于 lc_time 使用本地区每日和每月名称） |
TMMonth |
SP suffix |
spell mode (not implemented) |
DDSP |
日期/时间格式化的使用说明：
Tip
在 PostgreSQL 12 之前，可以使用非字母或非数字字符跳过输入字符串中的任意文本。例如，to_timestamp('2000y6m1d', 'yyyy-MM-DD') 用于操作。现在，你只能为此目的使用字母字符。例如，to_timestamp('2000y6m1d', 'yyyytMMtDDt') 和 to_timestamp('2000y6m1d', 'yyyy"y"MM"m"DD"d"') 跳过 y、m 和 d。
Caution
虽然 to_date 会拒绝格里高利历和 ISO 星期几编号日期字段的混合使用，但 to_char 不会拒绝，因为像 YYYY-MM-DD (IYYY-IDDD) 这样的输出格式规范可能有用。但避免编写类似 IYYY-MM-DD 的内容；在年初附近，这会产生出人意料的结果。（有关更多信息，请参见 Section 9.9.1。）
Table 9.29 显示了可用于格式化数字值的模板模式。
Table 9.29. Template Patterns for Numeric Formatting
Pattern |
Description |
9 |
数字位置（如果无关紧要，可以省略） |
0 |
数字位置（即使无关紧要，也不会省略） |
. (period) |
decimal point |
, (comma) |
group (thousands) separator |
PR |
尖括号中的负值 |
S |
符号锚定至数字（使用区域设置） |
L |
currency symbol (uses locale) |
D |
decimal point (uses locale) |
G |
group separator (uses locale) |
MI |
指定位置的减号（如果数字 < 0） |
PL |
指定位置的加号（如果数字 > 0） |
SG |
指定位置的加/减号 |
RN |
罗马数字（输入介于 1 和 3999 之间） |
TH or th |
ordinal number suffix |
V |
移动指定数量的数字（参见说明） |
EEEE |
exponent for scientific notation |
数字格式化的使用说明：
某些修改符可应用于任何模板模式以更改其行为。例如，FM99.99 是 99.99 模式，带有 FM 修改符。 Table 9.30 显示了数字格式化的修改符模式。
Table 9.30. Template Pattern Modifiers for Numeric Formatting
Modifier |
Description |
Example |
FM prefix |
填充模式（抑制后缀零和填充空白） |
FM99.99 |
TH suffix |
大写序号后缀 |
999TH |
th suffix |
小写序号后缀 |
999th |
Table 9.31 显示了 to_char 函数使用的一些示例。
Table 9.31. to_char Examples
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, 'FM90.99') |
'-0.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') |
' 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' |
to_char(0.0004859, '9.99EEEE') |
' 4.86e-04' |