Skip to content

9.8. 数据类型格式化函数#

PostgreSQL格式化函数提供了一套功能强大的工具,用于将各种数据类型(日期/时间、整数、浮点数、数字)转换为格式化的字符串,以及将格式化的字符串转换为特定数据类型。表 9.26列出了这些函数。这些函数都遵循一个通用的调用约定:第一个参数是要格式化的值,第二个参数是定义输出或输入格式的模板。

表 9.26. 格式化函数

函数

说明

示例

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

根据给定的格式将数字转换为字符串;可用于 integerbigintnumericrealdouble 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

根据给定的格式将字符串转换为时间戳。(另请参阅 表 9.33 中的 to_timestamp(double precision)。)

to_timestamp('05 Dec 2000', 'DD Mon YYYY')2000-12-05 00:00:00-05

提示

to_timestampto_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, PMpm午夜指示符(无句点)
A.M., a.m., P.M.p.m.午夜指示符(带句点)
Y,YYY年份(4 位或更多位数)带逗号
YYYY年份(4 位或更多位数)
YYY年份最后 3 位数
YY年份最后 2 位数
Y年份最后 1 位数
IYYYISO 8601 周编号年份(4 位或更多位数)
IYYISO 8601 周编号年份最后 3 位数
IYISO 8601 周编号年份最后 2 位数
IISO 8601 周编号年份最后 1 位数
BC, bc, ADad年代指示符(无句点)
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)
IDDDISO 8601 周编号年份中的天数(001–371;该年的第一天是第一周的星期一)
DD月份中的天数(01–31)
D星期几,星期日 (1) 至星期六 (7)
IDISO 8601 星期几,星期一 (1) 至星期日 (7)
W月份中的星期(1–5)(第一周从该月的第一天开始)
WW年份中的星期数(1–53)(第一周从该年的第一天开始)
IWISO 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时区分钟
OFUTC 时区偏移(仅在 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_timestampto_date 忽略输入中的字母大小写;例如,MONMonmon 都接受相同的字符串。使用 TM 修饰符时,根据函数输入校对规则进行大小写折叠(请参阅 第 24.2 节)。

  • to_timestampto_date 跳过输入字符串开头和日期和时间值周围的多个空格,除非使用了 FX 选项。例如,to_timestamp(' 2000 JUN', 'YYYY MON')to_timestamp('2000 - JUN', 'YYYY-MON') 有效,但 to_timestamp('2000 JUN', 'FXYYYY MON') 返回错误,因为 to_timestamp 仅期望一个空格。FX 必须指定为模板中的第一个项目。

  • to_timestampto_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_dateto_numberto_timestamp 中,文本字面值和双引号字符串会导致跳过字符串中包含的字符数;例如,"XX" 跳过两个输入字符(无论它们是否是 XX)。

    提示

    PostgreSQL 12 之前,可以使用非字母或非数字字符跳过输入字符串中的任意文本。例如,to_timestamp('2000y6m1d', 'yyyy-MM-DD') 曾经可以使用。现在,您只能为此目的使用字母字符。例如,to_timestamp('2000y6m1d', 'yyyytMMtDDt')to_timestamp('2000y6m1d', 'yyyy"y"MM"m"DD"d"') 跳过 ymd

  • 如果您希望在输出中显示双引号,则必须在双引号前加上反斜杠,例如 '\"YYYY Month\"'。反斜杠在双引号字符串之外没有其他特殊之处。在双引号字符串中,反斜杠会导致下一个字符被逐字采用,无论它是什么(但除非下一个字符是双引号或另一个反斜杠,否则不会产生特殊效果)。

  • to_timestampto_date 中,如果年份格式规范少于四位数字,例如 YYY,并且提供的年份少于四位数字,则年份将被调整为最接近 2020 年的年份,例如 95 变成 1995 年。

  • to_timestampto_date 中,负年份被视为表示公元前。如果您同时写了一个负年份和一个显式的 BC 字段,则会再次得到公元。输入年份零被视为公元前 1 年。

  • to_timestampto_date 中,YYYY 转换在处理超过 4 位数字的年份时有一个限制。您必须在 YYYY 后使用一些非数字字符或模板,否则年份始终被解释为 4 位数字。例如(年份为 20000):to_date('200001131', 'YYYYMMDD') 将被解释为 4 位数字年份;相反,在年份后使用非数字分隔符,例如 to_date('20000-1131', 'YYYY-MMDD')to_date('20000Nov31', 'YYYYMonDD')

  • to_timestampto_date 中,如果存在 YYYYYYYY,YYY 字段,则会接受 CC(世纪)字段,但会忽略它。如果将 CCYYY 一起使用,则结果将计算为指定世纪中的该年份。如果指定了世纪但未指定年份,则假定为该世纪的第一年。

  • to_timestampto_date 中,接受星期几名称或数字(DAYD 和相关的字段类型),但出于计算结果的目的而忽略它们。季度 (Q) 字段也是如此。

  • to_timestampto_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.312.3012.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) 格式化 HHHH12,如 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 之间)
THth序数后缀
V移动指定数量的数字(请参阅注释)
EEEE科学计数法的指数

数值格式化的使用说明

  • 0 指定始终打印的数字位置,即使它包含前导/尾随零。 9 也指定数字位置,但如果它是一个前导零,它将被空格替换,而如果它是一个尾随零并且指定了填充模式,它将被删除。(对于 to_number(),这两个模式字符是等效的。)

  • 如果格式提供的分数位数少于要格式化的数字, to_char() 将把数字舍入到指定的分数位数。

  • 模式字符 SLDG 表示当前区域设置定义的符号、货币符号、小数点和千位分隔符字符(请参阅 lc_monetarylc_numeric)。模式字符句点和逗号表示那些确切的字符,无论区域设置如何,它们都表示小数点和千位分隔符的含义。

  • 如果未为 to_char() 的模式做出明确规定,则会保留一列用于符号,并且该符号将锚定在数字的左侧(紧靠数字左侧)。如果 S 恰好出现在某些 9 的左侧,它同样会锚定在该数字上。

  • 使用 SGPLMI 格式化的符号不会锚定在数字上;例如,to_char(-12, 'MI9999') 会生成 '- 12',但 to_char(-12, 'S9999') 会生成 ' -12'。(Oracle 实现不允许在 9 之前使用 MI,而是要求 9 位于 MI 之前。)

  • TH 不转换小于零的值,也不转换分数。

  • PLSGTHPostgreSQL 扩展。

  • to_number 中,如果使用了非数据模板模式(例如 LTH),则会跳过相应数量的输入字符,无论它们是否与模板模式匹配,除非它们是数据字符(即数字、符号、小数点或逗号)。例如,TH 会跳过两个非数据字符。

  • Vto_char 一起使用会将输入值乘以 10^n,其中 nV 后面的数字位数。Vto_number 一起使用会以类似的方式进行除法。to_charto_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'