pgsql.cc 提供对 postgresql.org 官网内容的中文翻译，由 Pigsty 团队维护。
PostgreSQL格式化函数提供一套强大的工具用于把各种数据类型 （日期/时间、整数、浮点数、数值） 转换成格式化的字符串以及反过来从格式化的字符串转换成 指定的数据类型。表 9.21列出了这些函数。这些函数都遵循一个公共的调用规范： 第一个参数是待格式化的值，而第二个是一个定义输出或输入格式的模板。
还有一个单参数的to_timestamp函数；它接受一个 double precision参数，并把 Unix 纪元（自 1970-01-01 00:00:00+00 起的秒数） 转换为timestamp with time zone。 （Integer Unix 纪元会被隐式转换为double precision。）
表 9.21. 格式化函数
| Function | Return Type | 描述 | 示例 |
|---|---|---|---|
|
text |
将时间间隔转换为字符串 | to_char(current_timestamp, 'HH12:MI:SS') |
|
text |
将时间间隔转换为字符串 | to_char(interval '15h 2m 12s', 'HH24:MI:SS') |
|
text |
将时间间隔转换为字符串 | to_char(125, '999') |
|
text |
将时间间隔转换为字符串 | to_char(125.8::real, '999D9') |
|
text |
将时间间隔转换为字符串 | to_char(-125.8, '999D99S') |
|
date |
convert string to date | to_date('05 Dec 2000', 'DD Mon YYYY') |
|
numeric |
convert string to numeric | to_number('12,454.8-', '99G999D9S') |
|
timestamp with time zone |
把 Unix 纪元转换为时间戳 | to_timestamp('05 Dec 2000', 'DD Mon YYYY') |
|
timestamp with time zone |
把 Unix 纪元转换为时间戳 | to_timestamp(1284352323) |
在一个to_char输出模板串中，一些特定的模式可以被识别并且被替换成基于给定值的被恰当地格式化的数据。任何不属于模板模式的文本都简单地照字面拷贝。同样，在一个输入 模板串里（对其他函数），模板模式标识由输入数据串提供的值。
表 9.22展示了可以用于格式化日期和时间值的模板模式。
表 9.22. 用于日期/时间格式化的模板模式
| Pattern | 描述 |
|---|---|
HH |
一天中的小时（01-12） |
HH12 |
一天中的小时（01-12） |
HH24 |
一天中的小时（00-23） |
MI |
分钟（00-59） |
SS |
秒（00-59） |
MS |
毫秒（000-999） |
US |
微秒（000000-999999） |
SSSS |
自午夜起的秒数（0-86399） |
AM, am, PM or pm |
meridiem indicator (without periods) |
A.M., a.m., P.M. or p.m. |
meridiem indicator (with periods) |
Y,YYY |
带逗号的年（4 位或者更多位） |
YYYY |
年（4 位或者更多位） |
YYY |
年的最后 3 位数字 |
YY |
年的最后 2 位数字 |
Y |
年的最后 1 位数字 |
IYYY |
ISO 8601 周编号方式的年（4 位或更多位） |
IYY |
ISO 8601 周编号方式的年的最后 3 位数字 |
IY |
ISO 8601 周编号方式的年的最后 2 位数字 |
I |
ISO 8601 周编号方式的年的最后 1 位数字 |
BC, bc, AD or ad |
era indicator (without periods) |
B.C., b.c., A.D. or a.d. |
era indicator (with periods) |
MONTH |
大写的月份全称（空格补齐到 9 字符） |
Month |
首字母大写的月份全称（空格补齐到 9 字符） |
month |
小写的月份全称（空格补齐到 9 字符） |
MON |
简写的大写形式的月名（英文 3 字符，本地化长度可变） |
Mon |
简写的首字母大写形式的月名（英文 3 字符，本地化长度可变） |
mon |
简写的小写形式的月名（英文 3 字符，本地化长度可变） |
MM |
月份编号（01-12） |
DAY |
大写的星期全称（空格补齐到 9 字符） |
Day |
首字母大写的星期全称（空格补齐到 9 字符） |
day |
小写的星期全称（空格补齐到 9 字符） |
DY |
大写的星期简称（英语 3 字符，本地化长度可变） |
Dy |
首字母大写的星期简称（英语 3 字符，本地化长度可变） |
dy |
小写的星期简称（英语 3 字符，本地化长度可变） |
DDD |
一年中的第几天（001-366） |
IDDD |
ISO 8601 周编号年中的第几天（001-371；一年的第 1 天是第一个 ISO 周的星期一） |
DD |
月内日序数（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 日 UTC 午夜开始的整数日数） |
Q |
季度（被to_date和to_timestamp忽略） |
RM |
以大写罗马数字表示的月份（I-XII；I=一月） |
rm |
以小写罗马数字表示的月份（i-xii；i=一月） |
TZ |
大写形式的时区名称 |
tz |
小写形式的时区名称 |
修饰符可以被应用于模板模式来修改它们的行为。例如，FMMonth就是带着FM修饰符的Month模式。表 9.23展示了可用于日期/时间格式化的修饰符模式。
表 9.23. 用于日期/时间格式化的模板模式修饰符
| 修饰符 | 描述 | 示例 |
|---|---|---|
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 |
日期/时间格式化的使用注意事项：
FM抑制了在模式输出中添加前导零和尾随空格的行为，这些前导零和尾随空格本来会被添加以使输出成为固定宽度。在PostgreSQL中，FM仅修改下一个格式说明，而在Oracle中FM影响所有后续格式说明，并且重复的FM修饰符切换填充模式的开启和关闭。
TM不包含尾随空格。
除非使用FX选项，to_timestamp和to_date会跳过输入字符串中的多个空白。例如，to_timestamp('2000 JUN', 'YYYY MON')可以工作，但to_timestamp('2000 JUN', 'FXYYYY MON')会返回错误，因为to_timestamp期望只有一个空格。FX必须指定为模板中的第一项。
to_char模板中允许使用普通文本，它们会被按字面输出。可以把一个子串放在双引号中，强制把它解释为字面文本，即使它包含模式关键字。例如，在'"Hello Year "YYYY'中，YYYY会被年份数据替换，但Year中的单个Y不会。在to_date、to_number和to_timestamp中，双引号字符串会跳过与该字符串所含字符数相同的输入字符，例如"XX"跳过两个输入字符。
如果想在输出中出现双引号，必须在它前面加上反斜线，例如E'\\"YYYY Month\\"'。（需要两个反斜线是因为在使用转义字符串语法时反斜线具有特殊含义。）
从字符串到timestamp或date的YYYY转换在处理多于 4 位的年份时有一个限制：必须在YYYY后面使用某个非数字字符或模板，否则年份总是被解释为 4 位数字。例如（对于年份 20000）：to_date('200001131', 'YYYYMMDD')会被解释为 4 位年份；应在年份后使用非数字分隔符，例如to_date('20000-1131', 'YYYY-MMDD')或to_date('20000Nov31', 'YYYYMonDD')。
在从字符串到timestamp或date的转换中，如果存在YYY、YYYY或Y,YYY字段，则CC（世纪）字段被忽略。如果CC与YY或Y一起使用，则年份按(CC-1)*100+YY计算。
ISO 8601 星期编号日期（与格里高利日期不同）可以用以下两种方式之一指定给to_timestamp和to_date：
年、星期编号和星期几：例如to_date('2006-42-4', 'IYYY-IW-ID')返回日期2006-10-19。如果省略星期几，则假定为 1（星期一）。
年和年中的日：例如to_date('2006-291', 'IYYY-IDDD')也返回2006-10-19。
试图混用 ISO 8601 星期编号字段和格里高利日期字段输入日期是没有意义的，将导致错误。在 ISO 8601 星期编号年的语境下，“月”或“月中的日”概念没有意义；而在格里高利年的语境下，ISO 星期没有意义。
虽然to_date会拒绝格里高利与 ISO 星期编号日期字段的混用，但to_char不会，因为像YYYY-MM-DD (IYYY-IDDD)这样的输出格式说明可能有用。但要避免写IYYY-MM-DD这样的格式；它在接近年初时会产生令人意外的结果。（更多信息见第 9.9.1 节。）
在从字符串到timestamp的转换中，毫秒（MS）或微秒（US）值用作小数点后的秒数部分。例如to_timestamp('12:3', 'SS:MS')不是 3 毫秒，而是 300 毫秒，因为转换把它算作 12 + 0.3 秒。这意味着对于格式SS:MS，输入值12:3、12:30和12:300指定的是相同的毫秒数。要得到三毫秒，必须使用12:003，转换把它算作 12 + 0.003 = 12.003 秒。
下面是一个更复杂的例子：to_timestamp('15:12:02.020.001230', 'HH:MI:SS.MS.US')是 15 小时 12 分 2 秒 + 20 毫秒 + 1230 微秒 = 2.021230 秒。
to_char(..., 'ID')的星期编号与extract(isodow from ...)函数匹配，但to_char(..., 'D')的编号与extract(dow from ...)的星期编号不匹配。
to_char(interval)按 12 小时钟面格式化HH和HH12，即零小时和 36 小时都输出为12，而HH24输出完整的小时值，对 interval 而言可能超过 23。
表 9.24展示了可以用于格式化数值的模板模式。
表 9.24. 用于数值格式化的模板模式
| Pattern | 描述 |
|---|---|
9 |
数位（非有效位可以被省略） |
0 |
数位（即便是非有效位也不会被省略） |
. (period) |
匹配任意单个字符 |
, (comma) |
group (thousand) separator |
PR |
尖括号内的负值 |
S |
紧贴数值的正负号（使用区域设置） |
L |
货币符号（使用区域设置） |
D |
decimal point (uses locale) |
G |
分组分隔符（使用区域设置） |
MI |
分钟（00-59） |
PL |
在指定位置的正号（如果数字 > 0） |
SG |
在指定位置的正/负号 |
RN |
罗马数字（输入在 1 和 3999 之间） |
TH or th |
ordinal number suffix |
V |
移动指定位数（参阅注解） |
EEEE |
科学记数的指数 |
数值格式化的使用注意事项：
A sign formatted using SG, PL, or MI is not anchored to the number; for example, to_char(-12, 'MI9999') produces '- 12' but to_char(-12, 'S9999') produces ' -12'. Oracle 的实现不允许把MI放在9之前，而是要求9在MI之前。
9产生的值的位数与9的个数相同。如果没有可用的数字，则输出一个空格。
TH不转换小于零的值，也不转换小数。
PL，SG和 TH是PostgreSQL 的扩展。
V实际上把输入值乘以10^，其中nn是V后面数字的个数。to_char不支持把V与小数点组合使用（例如，不允许99.9V99）。
EEEE（科学记数法）不能与数字和小数点模式以外的任何其他格式模式或修饰符组合使用，并且必须位于格式字符串的末尾（例如，9.99EEEE是一个有效的模式）。
某些修饰符可以应用于任何模板模式以改变其行为。例如，FM9999就是带FM修饰符的9999模式。表 9.25展示了数值格式化的修饰符模式。
表 9.25. 用于数值格式化的模板模式修饰符
| 修饰符 | 描述 | 示例 |
|---|---|---|
FM prefix |
填充模式（抑制填充的空格和零） | FM9999 |
TH suffix |
大写序数后缀 | 999TH |
th suffix |
小写序数后缀 | 999th |
表 9.26展示了一些使用to_char函数的示例。
表 9.26. to_char示例
| 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') |
' 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' |
译文有误、术语不当或页面显示问题，请到译文仓库 pgsty/pgdoc 报告译文问题。 英文原文本身的问题，请在当前版本的对应页面向上游反馈；上游不再修订已结束维护的版本。