1.8数据类型格式化函数

只因后端
148 阅读8分钟

9.8. 数据类型格式化函数

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

表 9.23.格式化函数

功能返回类型描述例
to_char(timestamp, text)text将时间戳转换为字符串to_char(current_timestamp, 'HH12:MI:SS')
to_char(interval, text)text将间隔转换为字符串to_char(interval '15h 2m 12s', 'HH24:MI:SS')
to_char(int, text)text将整数转换为字符串to_char(125, '999')
to_char(double precision, text)text将实数/双精度转换为字符串to_char(125.8::real, '999D9')
to_char(numeric, text)text将数字转换为字符串to_char(-125.8, '999D99S')
to_date(text, text)date将字符串转换为日期to_date('05 Dec 2000', 'DD Mon YYYY')
to_number(text, text)numeric将字符串转换为数字to_number('12,454.8-', '99G999D9S')
to_timestamp(text, text)timestamp with time zone将字符串转换为时间戳to_timestamp('05 Dec 2000', 'DD Mon YYYY')

注意

还有一个单参数函数;见表9.30。to_timestamp

提示

to_timestamp并且存在以处理无法通过简单强制转换转换的输入格式。对于大多数标准日期/时间格式，只需将源字符串转换为所需的数据类型即可，并且要容易得多。同样，对于标准数值表示形式也是不必要的。to_date``to_number

在输出模板字符串中，存在某些模式，这些模式被识别并替换为基于给定值的适当格式的数据。任何不是模板模式的文本都只是逐字复制。同样，在输入模板字符串（对于其他函数）中，模板模式标识要由输入数据字符串提供的值。如果模板字符串中存在不是模板模式的字符，则只需跳过输入数据字符串中的相应字符（无论它们是否等于模板字符串字符）。to_char

表 9.24 显示了可用于格式化日期和时间值的模板模式。

表 9.24.日期/时间格式设置的模板模式

模式描述
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``pm子午线指示器（无句点）
A.M.或a.m.``P.M.``p.m.子午线指示器（带周期）
Y,YYY年份（4 位或更多数字），带逗号
YYYY年份（4 位或更多数字）
YYY一年的最后 3 位数字
YY一年的最后 2 位数字
Y年份的最后一位数字
IYYYISO 8601 周编号年份（4 位或更多数字）
IYYISO 3 周编号年份的最后 8601 位数字
IYISO 2 周编号年份的最后 8601 位数字
IISO 8601 周编号年份的最后一位数字
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）
IDDDISO 8601 周编号年份的日期（001-371;一年的第 1 天是第一个 ISO 周的星期一）
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儒略历日期（自公元前 24 年 4714 月 7 日当地午夜以来的整数日;见第 B.<> 节)
Q季度
RM月份大写罗马数字（I-XII;I=一月）
rm小写罗马数字的月份（i-xii;i=一月）
TZ大写时区缩写（仅在to_char)
tz小写时区缩写（仅在to_char)
TZH时区时间
TZM时区分钟
OF与 UTC 的时区偏移量（仅在to_char)

修饰符可以应用于任何模板模式以更改其行为。例如，是带有修饰符的模式。表 9.25 显示了日期/时间格式的修饰符模式。FMMonth``Month``FM

表 9.25.用于日期/时间格式设置的模板模式修饰符

修饰语描述例
FM前缀填充模式（取消前导零和填充空白）FMMonth
TH后缀大写序号后缀DDTH，例如，12TH
th后缀小写序数后缀DDth，例如，12th
FX前缀固定格式全局选项（请参阅使用说明）FX Month DD Day
TM前缀翻译模式（根据lc_time打印本地化的日和月名称)TMMonth
SP后缀拼写模式（未实现）DDSP

日期/时间格式的使用说明：

  • FM取消前导零和尾随空白，否则将添加这些空白以使模式的输出具有固定宽度。在PostgreSQL中，仅修改下一个规范，而在Oracle中影响所有后续规范，重复修饰符打开和关闭填充模式。FM``FM``FM

  • TM不包括尾随空白。 并忽略修饰符。to_timestamp``to_date``TM

  • to_timestamp并跳过输入字符串中的多个空格，除非使用该选项。例如，有效，但返回错误，因为只需要一个空格。 必须指定为模板中的第一项。to_date``FX``to_timestamp('2000    JUN', 'YYYY MON')``to_timestamp('2000    JUN', 'FXYYYY MON')``to_timestamp``FX

  • 模板中允许使用普通文本，并将按字面意思输出。您可以将子字符串放在双引号中，以强制将其解释为文字文本，即使它包含模板模式也是如此。例如，在 中，将替换为年份数据，但单个 in 将不会被替换。在 、 和 中，文字文本和双引号字符串会导致跳过字符串中包含的字符数;例如，跳过两个输入字符（无论它们是否为 ）。to_char``'"Hello Year "YYYY'``YYYY``Y``Year``to_date``to_number``to_timestamp``"XX"``XX

  • 如果要在输出中使用双引号，则必须在它前面加上反斜杠，例如。反斜杠在双引号字符串之外并不特殊。在双引号字符串中，反斜杠会导致下一个字符按字面意思理解，无论它是什么（但这没有特殊效果，除非下一个字符是双引号或其他反斜杠）。'"YYYY Month"'

  • 在 和 中，如果年份格式规范小于四位数，例如，提供的年份小于四位数，则将年份调整为最接近 2020 年，例如，变为 1995 年。to_timestamp``to_date``YYY``95

  • 在和中，负年份被视为表示公元前。如果您同时写入负年份和显式字段，则会再次获得 AD。零年的输入被视为公元前 1 年。to_timestamp``to_date``BC

  • 在 和 中，转换在处理超过 4 位数字的年份时有限制。您必须在 之后使用一些非数字字符或模板，否则年份始终被解释为 4 位数字。例如（年份为 20000）：将被解释为 4 位数的年份;而是在年份后使用非数字分隔符，例如 或 .to_timestamp``to_date``YYYY``YYYY``to_date('200001131', 'YYYYMMDD')``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

  • 在 和 中，可以通过以下两种方式之一指定 ISO 8601 周编号日期（不同于公历日期）：to_timestamp``to_date

    • 年、周数和工作日：例如返回日期。如果省略工作日，则假定为 1（星期一）。to_date('2006-42-4', 'IYYY-IW-ID')``2006-10-19
    • 年和一年中的某天：例如还返回 .to_date('2006-291', 'IYYY-IDDD')``2006-10-19

    尝试混合使用 ISO 8601 周编号字段和公历日期字段输入日期是荒谬的，并且会导致错误。在ISO 8601周编号年的上下文中，“月”或“月中的某天”的概念没有任何意义。在公历年的背景下，ISO周没有任何意义。

    谨慎

    虽然将拒绝混合使用公历和 ISO 周编号日期字段，但不会，因为输出格式规范等可能很有用。但避免写类似 ;这将在年初产生令人惊讶的结果。（有关详细信息，请参见第 9.9.1 节。to_date``to_char``YYYY-MM-DD (IYYY-IDDD)``IYYY-MM-DD

  • 在 中，毫秒 （） 或微秒 （） 字段用作小数点后的秒位。例如，不是 3 毫秒，而是 300，因为转换将其视为 12 + 0.3 秒。因此，对于格式、输入值 、 和 指定相同的毫秒数。要获得三毫秒，必须写入 ，转换将其视为 12 + 0.003 = 12.003 秒。to_timestamp``MS``US``to_timestamp('12.3', 'SS.MS')``SS.MS``12.3``12.30``12.300``12.003

    下面是一个更复杂的示例：是 15 小时 12 分 2 秒 + 20 毫秒 + 1230 微秒 = 2.021230 秒。to_timestamp('15:12:02.020.001230', 'HH24:MI:SS.MS.US')

  • to_char(..., 'ID')的星期几编号与函数匹配，但 的日期编号与 的日期编号不匹配。extract(isodow from ...)``to_char(..., 'D')``extract(dow from ...)

  • to_char(interval)格式和如 12 小时制上所示，例如零小时和 36 小时都输出为 ，而输出整小时值，该值可以超过 23。HH``HH12``12``HH24``interval

表 9.26 显示了可用于格式化数值的模板模式。

表 9.26.数字格式的模板模式

模式描述
9数字位置（如果无关紧要，可以删除）
0数字位置（即使微不足道也不会被删除）
.（期间）小数点
,（逗号）组（千）分离器
PR尖括号中的负值
S锚定到数字的符号（使用区域设置）
L货币符号（使用区域设置）
D小数点（使用区域设置）
G组分隔符（使用区域设置）
MI指定位置的减号（如果数字< 0）
PL加号登录指定位置（如果数字> 0）
SG加/减号在指定位置
RN罗马数字（输入在 1 到 3999 之间）
TH或th序数后缀
V移位指定的位数（请参阅注释）
EEEE科学记数法的指数

数字格式的使用说明：

  • 0指定将始终打印的数字位置，即使它包含前导/尾随零。 还指定了一个数字位置，但如果它是前导零，那么它将被一个空格替换，而如果它是一个尾随零并且指定了填充模式，那么它将被删除。（对于 ，这两个模式字符是等效的。9``to_number()
  • 模式字符 、 、 和 表示由当前区域设置定义的符号、货币符号、小数点和千位分隔符字符（请参见lc_monetary和lc_numeric）。模式字符句点和逗号表示这些确切的字符，具有小数点和千位分隔符的含义，而不考虑区域设置。S``L``D``G
  • 如果没有对登录 的模式进行显式设置，则将为该标志保留一列，并将其锚定到数字（显示在数字的左侧）。如果出现在一些 's 的左边，它同样会锚定到数字上。to_char()``S``9
  • 使用 、 或未锚定到数字的格式的符号;例如，生产但产生.（Oracle 实现不允许使用 before ，而是要求在 之前使用 。SG``PL``MI``to_char(-12, 'MI9999')``'-  12'``to_char(-12, 'S9999')``'  -12'``MI``9``9``MI
  • TH不转换小于零的值，也不转换小数。
  • PL、 和 是 PostgreSQL 扩展。SG``TH
  • 在 中，如果使用非数据模板模式（如 或），则无论输入字符是否与模板模式匹配，都将跳过相应数量的输入字符，除非它们是数据字符（即数字、符号、小数点或逗号）。例如，将跳过两个非数据字符。to_number``L``TH``TH
  • Vwith 将输入值乘以 ，其中 n 是后面的位数。 以类似的方式划分。 并且不支持使用与小数点组合（例如，不允许）。to_char``10^n``V``V``to_number``to_char``to_number``V``99.9V99
  • EEEE（科学记数法）不能与除数字和小数点模式以外的任何其他格式模式或修饰符结合使用，并且必须位于格式字符串的末尾（例如，是有效的模式）。9.99EEEE

某些修饰符可以应用于任何模板模式以更改其行为。例如，是带有修饰符的模式。表 9.27 显示了数字格式的修饰符模式。FM99.99``99.99``FM

表 9.27.用于数字格式设置的模板模式修饰符

修饰语描述例
FM前缀填充模式（禁止尾随零和填充空白）FM99.99
TH后缀大写序号后缀999TH
th后缀小写序数后缀999th

表 9.28 显示了使用该函数的一些示例。to_char

表 9.28.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'
avatar
文章
阅读
粉丝