9.8. 数据类型格式化功能
PostgreSQL 格式化功能提供了一套功能强大的工具，用于将各种数据类型(日期/时间，整数，浮点数，数字)转换为格式化字符串，以及从格式化字符串转换为特定数据类型。 Table 9.23列出它们。这些函数都遵循一个通用的调用约定：第一个参数是要格式化的值，第二个参数是定义输出或 Importing 格式的模板。
表 9.23. 格式化功能
Note
还有一个单参数to_timestamp函数；参见Table 9.30。
Tip
存在to_timestamp和to_date来处理无法通过简单转换进行转换的 Importing 格式。对于大多数标准日期/时间格式，只需将源字符串强制转换为所需的数据类型即可，并且非常容易。同样，对于标准的数字表示形式，to_number是不必要的。
在to_char输出模板字符串中，根据给定值识别并替换为某些格式的数据的某些模式。非模板模式的任何文本都将被逐字复制。同样，在 Importing 模板字符串(用于其他功能)中，模板模式标识 Importing 数据字符串要提供的值。
Table 9.24显示了可用于格式化日期和时间值的模板模式。
表 9.24. 日期/时间格式的模板模式
| Pattern | Description |
|---|---|
HH |
一天中的小时(01-12) |
HH12 |
一天中的小时(01-12) |
HH24 |
一天中的小时(00-23) |
MI |
minute (00-59) |
SS |
second (00-59) |
MS |
millisecond (000-999) |
US |
microsecond (000000-999999) |
SSSS |
午夜之后的秒数(0-86399) |
AM，am，PM或pm |
子午线 Metrics(无期限) |
A.M.，a.m.，P.M.或p.m. |
子午线 Metrics(带句号) |
Y,YYY |
年份(4 个或更多数字)，带逗号 |
YYYY |
年(4 个或更多数字) |
YYY |
年份的后三位数 |
YY |
年份的后两位数字 |
Y |
年份的最后一位 |
IYYY |
ISO 8601 周编号年份(4 个或更多数字) |
IYY |
ISO 8601 周编号年份的后 3 位数字 |
IY |
ISO 8601 周编号年份的后两位数字 |
I |
ISO 8601 周编号年的最后一位数字 |
BC，bc，AD或ad |
时代 Metrics(无期限) |
B.C.，b.c.，A.D.或a.d. |
时代 Metrics(带句号) |
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 位数字)(二十一世纪始于 2001-01-01) |
J |
朱利安日(自公元前 4714 年 11 月 24 日午夜 UTC 以来的整数天) |
Q |
quarter |
RM |
大写罗马数字表示月份(I-XII； I =一月) |
rm |
小写罗马数字表示月份(i-xii； i =一月) |
TZ |
大写的时区缩写(仅在to_char中受支持) |
tz |
小写的时区缩写(仅在to_char中受支持) |
OF |
与 UTC 的时区偏移量(仅to_char支持) |
可以将修饰符应用于任何模板模式以更改其行为。例如，FMMonth是具有FM修饰符的Month模式。 Table 9.25显示日期/时间格式的修饰符模式。
表 9.25. 用于日期/时间格式的模板模式修改器
| Modifier | Description | Example |
|---|---|---|
FM前缀 |
填充模式(抑制前导零和填充空白) | FMMonth |
TH后缀 |
大写字母序数后缀 | DDTH，例如12TH |
th后缀 |
小写序数后缀 | DDth，例如12th |
FX前缀 |
固定格式的全局选项(请参阅使用说明) | FX Month DD Day |
TM前缀 |
翻译模式(根据lc_time打印本地化的日期和月份名称) | TMMonth |
SP后缀 |
拼写模式(未实现) | DDSP |
日期/时间格式的使用说明：
-
FM抑制前导零和尾随空白，否则将其添加以使模式的输出为固定宽度。在 PostgreSQL 中，FM仅修改下一个规范，而在 Oracle 中，FM影响所有后续规范，重复的FM修饰符可打开和关闭填充模式。 -
TM不包含尾随空格。to_timestamp和to_date忽略TM修饰符。 -
除非使用
FX选项，否则to_timestamp和to_date会跳过 Importing 字符串中的多个空格。例如，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中，双引号字符串会跳过字符串中包含的 Importing 字符数，例如"XX"跳过两个 Importing 字符。 -
如果要在输出中使用双引号，则必须在其前面加上反斜杠，例如
'\"YYYY Month\"'。 -
在
to_timestamp和to_date中，如果年份格式规范小于四位数，例如YYY，并且所提供的年份少于四位数，则该年份将被调整为最接近 2020 年，例如95变成 1995 年。 -
在
to_timestamp和to_date中，当处理多于 4 位数字的年份时，YYYY转换受到限制。您必须在YYYY之后使用一些非数字字符或模板，否则年份始终被解释为 4 位数字。例如(年份为 20000)：to_date('200001131', 'YYYYMMDD')将解释为 4 位数字的年份；而是在年份后使用非数字分隔符，例如to_date('20000-1131', 'YYYY-MMDD')或to_date('20000Nov31', 'YYYYMonDD')。 -
在
to_timestamp和to_date中，接受CC(世纪)字段，但是如果存在YYY，YYYY或Y,YYY字段则将其忽略。如果将CC与YY或Y一起使用，则将结果计算为指定世纪中的那一年。如果指定了世纪但未指定年份，则假定为世纪的第一年。 -
在
to_timestamp和to_date中，接受工作日名称或数字(DAY，D和相关的字段类型)，但出于计算结果的目的而将其忽略。四分之一(Q)字段也是如此。 -
在
to_timestamp和to_date中，可以通过以下两种方式之一指定 ISO 8601 周编号日期(与公历日期不同)： -
年，周号和工作日：例如
to_date('2006-42-4', 'IYYY-IW-ID')返回日期2006-10-19。如果省略工作日，则假定为 1(星期一)。- 年中的年月日：例如
to_date('2006-291', 'IYYY-IDDD')还返回2006-10-19。
- 年中的年月日：例如
尝试使用 ISO 8601 周编号字段和公历日期字段的混合 Importing 日期是没有意义的，并且会导致错误。在 ISO 8601 周编号年份的上下文中，“月”或“月中的某天”的概念没有意义。在公历年份中，ISO 周没有意义。
Caution
to_date将拒绝使用公历日期和 ISO 周编号日期字段的混合，而to_char则不会，因为YYYY-MM-DD (IYYY-IDDD)之类的输出格式规范可能会有用。但是避免写类似IYYY-MM-DD的东西；在今年年初将产生令人惊讶的结果。 (有关更多信息，请参见Section 9.9.1。)
- 在
to_timestamp中，毫秒(MS)或微秒(US)字段用作小数点后的秒位数。例如to_timestamp('12.3', 'SS.MS')不是 3 毫秒，而是 300，因为转换将其视为 12 0.3 秒。因此，对于格式SS.MS，Importing 值12.3，12.30和12.300指定相同的毫秒数。要获得三毫秒，必须写12.003，转换将其视为 12 0.003 = 12.003 秒。
这是一个更复杂的示例：to_timestamp('15:12:02.020.001230', 'HH24: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)格式化HH和HH12，如 12 小时制所示，例如零小时和 36 小时都输出为12，而HH24输出完整的小时值，在interval值中可以超过 23.
Table 9.26显示了可用于格式化数值的模板模式。
表 9.26. 用于数字格式的模板模式
| Pattern | Description |
|---|---|
9 |
数字位置(如果不重要，可以将其删除) |
0 |
数字位置(即使微不足道，也不会丢失) |
.(句号) |
decimal point |
,(逗号) |
组(千)分隔符 |
PR |
尖括号中的负值 |
S |
锚定到数字的符号(使用语言环境) |
L |
货币符号(使用语言环境) |
D |
小数点(使用语言环境) |
G |
组分隔符(使用语言环境) |
MI |
指定位置的减号(如果数字<0) |
PL |
在指定位置加号(如果数字> 0) |
SG |
加/减号在指定位置 |
RN |
罗马数字(1 到 3999 之间的 Importing) |
TH或th |
序数后缀 |
V |
移位指定的位数(请参阅 Comments) |
EEEE |
科学计数法的指数 |
数字格式的使用说明：
-
0指定将始终打印的数字位置，即使它包含前导/后缀零。9还指定了数字位置，但是如果它是前导零，则将替换为空格，而如果是尾随零并且指定了填充模式，则将其删除。 (对于to_number()，这两个模式字符是等效的.) -
模式字符
S，L，D和G表示符号，货币符号，小数点和由当前语言环境定义的数千个分隔符(请参见lc_monetary和lc_numeric)。模式字符的句点和逗号表示这些确切的字符，无论其语言环境如何，均具有小数点和千位分隔符的含义。 -
如果没有为
to_char()的模式中的符号做出明确规定，则将为该符号保留一列，并将其固定在该数字的左侧(显示在该数字的左侧)。如果S出现在某些9的左侧，则同样会锚定到该数字。 -
使用
SG，PL或MI格式化的符号未固定到该数字；例如，to_char(-12, 'MI9999')产生'- 12'但to_char(-12, 'S9999')产生' -12'。 (Oracle 实现不允许在9之前使用MI，而是要求在MI之前使用9.) -
TH不转换小于零的值，也不转换小数。 -
PL，SG和TH是 PostgreSQL 扩展。 -
V和to_char将 Importing 值乘以10^n，其中*n*是V后的位数。V和to_number的划分方式相似。to_char和to_number不支持将V与小数点结合使用(例如，不允许99.9V99)。 -
EEEE(科学记号)不能与数字和小数点模式以外的任何其他格式设置模式或修饰符结合使用，并且必须位于格式字符串的末尾(例如9.99EEEE是有效模式)。
可以将某些修饰符应用于任何模板模式以更改其行为。例如，FM99.99是具有FM修饰符的99.99模式。 Table 9.27显示用于数字格式的修饰符模式。
表 9.27. 用于数字格式的模板模式修饰符
| Modifier | Description | Example |
|---|---|---|
FM前缀 |
填充模式(禁止尾随零和填充空格) | FM99.99 |
TH后缀 |
大写字母序数后缀 | 999TH |
th后缀 |
小写序数后缀 | 999th |
Table 9.28显示了使用to_char函数的一些示例。
表 9.28. 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, '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' |