9.8. 数据类型格式化函数#
PostgreSQL格式化函数提供了一套功能强大的工具,用于将各种数据类型(日期/时间、整数、浮点数、数字)转换为格式化的字符串,以及将格式化的字符串转换为特定数据类型。表 9.26列出了这些函数。这些函数都遵循一个通用的调用约定:第一个参数是要格式化的值,第二个参数是定义输出或输入格式的模板。
表 9.26. 格式化函数
函数 说明 示例 |
---|
根据给定的格式将时间戳转换为字符串。
|
根据给定的格式将间隔转换为字符串。
|
根据给定的格式将数字转换为字符串;可用于
|
根据给定的格式将字符串转换为日期。
|
根据给定的格式将字符串转换为数字。
|
根据给定的格式将字符串转换为时间戳。(另请参阅 表 9.33 中的
|
提示
to_timestamp
和to_date
用于处理无法通过简单转换转换的输入格式。对于大多数标准日期/时间格式,只需将源字符串强制转换为所需的数据类型即可,并且简单得多。类似地,对于标准数字表示,to_number
是不必要的。
在to_char
输出模板字符串中,某些模式会被识别并替换为基于给定值适当格式化后的数据。任何不是模板模式的文本都会原样复制。类似地,在输入模板字符串中(对于其他函数),模板模式标识由输入数据字符串提供的数值。如果模板字符串中存在不是模板模式的字符,输入数据字符串中的相应字符将被跳过(无论它们是否等于模板字符串字符)。
表 9.27显示了用于格式化日期和时间值的模板模式。
表 9.27。日期/时间格式化模板模式
模式 | 说明 |
---|---|
HH | 小时(01–12) |
HH12 | 小时(01–12) |
HH24 | 小时(00–23) |
MI | 分钟(00–59) |
SS | 秒(00–59) |
MS | 毫秒(000–999) |
US | 微秒(000000–999999) |
FF1 | 十分之一秒(0–9) |
FF2 | 百分之一秒(00–99) |
FF3 | 毫秒(000–999) |
FF4 | 十分之一毫秒(0000–9999) |
FF5 | 百分之一毫秒(00000–99999) |
FF6 | 微秒(000000–999999) |
SSSS , SSSSS | 午夜后的秒数(0–86399) |
AM , am , PM 或 pm | 午夜指示符(无句点) |
A.M. , a.m. , P.M. 或 p.m. | 午夜指示符(带句点) |
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 或 ad | 年代指示符(无句点) |
B.C. , b.c. , A.D. 或 a.d. | 纪元指示符(带时期) |
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;该年的第一天是第一周的星期一) |
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 日当地午夜以来的整数天数;请参阅 第 B.7 节) |
Q | 季度 |
RM | 大写罗马数字月份(I–XII;I=1 月) |
rm | 小写罗马数字月份(i–xii;i=1 月) |
TZ | 大写时区缩写(仅在 to_char 中受支持) |
tz | 小写时区缩写(仅在 to_char 中受支持) |
TZH | 时区小时 |
TZM | 时区分钟 |
OF | UTC 时区偏移(仅在 to_char 中受支持) |
修饰符可以应用于任何模板模式以改变其行为。例如,FMMonth
是带FM
修饰符的Month
模式。表 9.28显示了日期/时间格式化的修饰符模式。
表 9.28。日期/时间格式化的模板模式修饰符
修饰符 | 说明 | 示例 |
---|---|---|
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
禁止尾随空格,无论是否指定了FM
。to_timestamp
和to_date
忽略输入中的字母大小写;例如,MON
、Mon
和mon
都接受相同的字符串。使用TM
修饰符时,根据函数输入校对规则进行大小写折叠(请参阅 第 24.2 节)。to_timestamp
和to_date
跳过输入字符串开头和日期和时间值周围的多个空格,除非使用了FX
选项。例如,to_timestamp(' 2000 JUN', 'YYYY MON')
和to_timestamp('2000 - JUN', 'YYYY-MON')
有效,但to_timestamp('2000 JUN', 'FXYYYY MON')
返回错误,因为to_timestamp
仅期望一个空格。FX
必须指定为模板中的第一个项目。在
to_timestamp
和to_date
的模板字符串中的分隔符(空格或非字母/非数字字符)匹配输入字符串中的任何单个分隔符或被跳过,除非使用了FX
选项。例如,to_timestamp('2000JUN', 'YYYY///MON')
和to_timestamp('2000/JUN', 'YYYY MON')
有效,但to_timestamp('2000//JUN', 'YYYY/MON')
返回错误,因为输入字符串中的分隔符数量超过了模板中的分隔符数量。如果指定了
FX
,模板字符串中的分隔符与输入字符串中的一个字符完全匹配。但请注意,输入字符串字符不必与模板字符串中的分隔符相同。例如,to_timestamp('2000/JUN', 'FXYYYY MON')
有效,但to_timestamp('2000/JUN', 'FXYYYY MON')
返回错误,因为模板字符串中的第二个空格消耗了输入字符串中的字母J
。TZH
模板模式可以匹配带符号的数字。如果没有FX
选项,减号可能会引起歧义,并可能被解释为分隔符。此歧义解决方法如下:如果模板字符串中TZH
之前的分隔符数量少于输入字符串中减号之前的分隔符数量,则减号被解释为TZH
的一部分。否则,减号被视为值之间的分隔符。例如,to_timestamp('2000 -10', 'YYYY TZH')
将-10
匹配到TZH
,但to_timestamp('2000 -10', 'YYYY TZH')
将10
匹配到TZH
。普通文本允许在
to_char
模板中使用,并将按字面意思输出。您可以将子字符串放在双引号中,以强制将其解释为文本字面值,即使它包含模板模式。例如,在'"Hello Year "YYYY'
中,YYYY
将被年数据替换,但Year
中的单个Y
不会被替换。在to_date
、to_number
和to_timestamp
中,文本字面值和双引号字符串会导致跳过字符串中包含的字符数;例如,"XX"
跳过两个输入字符(无论它们是否是XX
)。提示
在 PostgreSQL 12 之前,可以使用非字母或非数字字符跳过输入字符串中的任意文本。例如,
to_timestamp('2000y6m1d', 'yyyy-MM-DD')
曾经可以使用。现在,您只能为此目的使用字母字符。例如,to_timestamp('2000y6m1d', 'yyyytMMtDDt')
和to_timestamp('2000y6m1d', 'yyyy"y"MM"m"DD"d"')
跳过y
、m
和d
。如果您希望在输出中显示双引号,则必须在双引号前加上反斜杠,例如
'\"YYYY Month\"'
。反斜杠在双引号字符串之外没有其他特殊之处。在双引号字符串中,反斜杠会导致下一个字符被逐字采用,无论它是什么(但除非下一个字符是双引号或另一个反斜杠,否则不会产生特殊效果)。在
to_timestamp
和to_date
中,如果年份格式规范少于四位数字,例如YYY
,并且提供的年份少于四位数字,则年份将被调整为最接近 2020 年的年份,例如95
变成 1995 年。在
to_timestamp
和to_date
中,负年份被视为表示公元前。如果您同时写了一个负年份和一个显式的BC
字段,则会再次得到公元。输入年份零被视为公元前 1 年。在
to_timestamp
和to_date
中,YYYY
转换在处理超过 4 位数字的年份时有一个限制。您必须在YYYY
后使用一些非数字字符或模板,否则年份始终被解释为 4 位数字。例如(年份为 20000):to_date('200001131', 'YYYYMMDD')
将被解释为 4 位数字年份;相反,在年份后使用非数字分隔符,例如to_date('20000-1131', 'YYYY-MMDD')
或to_date('20000Nov31', 'YYYYMonDD')
。在
to_timestamp
和to_date
中,如果存在YYY
、YYYY
或Y,YYY
字段,则会接受CC
(世纪)字段,但会忽略它。如果将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 周编号字段和公历日期字段的混合来输入日期是没有意义的,并且会导致错误。在 ISO 8601 周编号年份的上下文中,““月份””或““月份中的日期””的概念没有意义。在公历年份的上下文中,ISO 周没有意义。
警告
虽然
to_date
会拒绝公历和 ISO 周编号日期字段的混合,但to_char
不会,因为输出格式规范(如YYYY-MM-DD (IYYY-IDDD)
)可能很有用。但避免编写类似IYYY-MM-DD
的内容;这会在年初附近产生令人惊讶的结果。(有关更多信息,请参见 第 9.9.1 节。)在
to_timestamp
中,毫秒 (MS
) 或微秒 (US
) 字段用作小数点后的秒数位。例如,to_timestamp('12.3', 'SS.MS')
不是 3 毫秒,而是 300,因为转换将其视为 12 + 0.3 秒。因此,对于格式SS.MS
,输入值12.3
、12.30
和12.300
指定相同数量的毫秒。要获得 3 毫秒,必须编写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。
表 9.29显示了可用于格式化数值的模板模式。
表 9.29. 数值格式化的模板模式
模式 | 说明 |
---|---|
9 | 数字位置(如果无关紧要,可以省略) |
0 | 数字位置(即使无关紧要,也不会省略) |
. (句点) | 小数点 |
, (逗号) | 组(千位)分隔符 |
PR | 尖括号中的负值 |
S | 固定到数字的符号(使用区域设置) |
L | 货币符号(使用区域设置) |
D | 小数点(使用区域设置) |
G | 组分隔符(使用区域设置) |
MI | 指定位置的减号(如果数字 < 0) |
PL | 指定位置的加号(如果数字 > 0) |
SG | 指定位置的正/负号 |
RN | 罗马数字(输入介于 1 和 3999 之间) |
TH 或 th | 序数后缀 |
V | 移动指定数量的数字(请参阅注释) |
EEEE | 科学计数法的指数 |
数值格式化的使用说明
0
指定始终打印的数字位置,即使它包含前导/尾随零。9
也指定数字位置,但如果它是一个前导零,它将被空格替换,而如果它是一个尾随零并且指定了填充模式,它将被删除。(对于to_number()
,这两个模式字符是等效的。)如果格式提供的分数位数少于要格式化的数字,
to_char()
将把数字舍入到指定的分数位数。模式字符
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
,而是要求9
位于MI
之前。)TH
不转换小于零的值,也不转换分数。PL
、SG
和TH
是 PostgreSQL 扩展。在
to_number
中,如果使用了非数据模板模式(例如L
或TH
),则会跳过相应数量的输入字符,无论它们是否与模板模式匹配,除非它们是数据字符(即数字、符号、小数点或逗号)。例如,TH
会跳过两个非数据字符。V
与to_char
一起使用会将输入值乘以10^
,其中n
n
是V
后面的数字位数。V
与to_number
一起使用会以类似的方式进行除法。to_char
和to_number
不支持将V
与小数点结合使用(例如,99.9V99
不被允许)。EEEE
(科学计数法)不能与任何其他格式化模式或修饰符(数字和小数点模式除外)结合使用,并且必须位于格式字符串的末尾(例如,9.99EEEE
是一个有效的模式)。
某些修饰符可以应用于任何模板模式以改变其行为。例如,FM99.99
是具有FM
修饰符的99.99
模式。表 9.30显示了数字格式化的修饰符模式。
表 9.30. 数字格式化的模板模式修饰符
修饰符 | 说明 | 示例 |
---|---|---|
FM 前缀 | 填充模式(抑制尾随零和填充空格) | FM99.99 |
TH 后缀 | 大写序数后缀 | 999TH |
th 后缀 | 小写序数后缀 | 999th |
表 9.31显示了to_char
函数使用的一些示例。
表 9.31。to_char
示例
表达式 | 结果 |
---|---|
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' |