9.8. 数据类型格式化函数 · PostgreSQL 中文文档 9.3

# 9.8\. 数据类型格式化函数 PostgreSQL格式化函数提供一套有效的工具用于把各种数据类型转换成格式化的字符串以及反过来从格式化的字符串转换成指定的数据类型。 [Table 9-20](#calibre_link-2204)列出了这些函数。这些函数都遵循一个公共的调用习惯：第一个参数是待格式化的值，而第二个是定义输出或输入格式的模板。单参数`to_timestamp`函数也可以使用；它接受一个`double precision` 参数，并且从Unix纪元（秒自1970-01-01 00:00:00+00）转换为`timestamp with time zone` 类型。（`Integer`类型的Unix纪元隐含的转换为`double precision`类型。） **Table 9-20\. 格式化函数** | 函数 | 返回类型 | 描述 | 例子 | | --- | --- | --- | --- | | ``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')` | | ``to_timestamp(```double precision`) | `timestamp with time zone` | 把 Unix 纪元转换成时间戳 | `to_timestamp(1284352323)` | 在`to_char`输出模板字符串里，该函数族可以识别一些特定的模式，并且把给定的数值正确地格式化成相应的数据。任何不属于模板模式的文本都简单地逐字拷贝。同样，在一个输入模板字符串里(对其他函数)，模板模式标识数值由输入数据字符串提供。 [Table 9-21](#calibre_link-2205)显示了可以用于格式化日期和时间值的模版。 **Table 9-21\. 用于日期/时间格式化的模式** | 模式 | 描述 | | --- | --- | | `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` | 年的后三位 | | `YY` | 年的后两位 | | `Y` | 年的最后一位 | | `IYYY` | ISO年(4 位或更多位) | | `IYY` | ISO年的最后三位 | | `IY` | ISO年的最后两位 | | `I` | ISO年的最后一位 | | `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一年里的日（001-371；年的第一天是第一个ISO周的星期一） | | `DD` | 一个月里的日(01-31) | | `D` | 一周里的日，星期日（`1`）到星期六（`7`） | | `ID` | ISO一周里的日，星期一（`1`）到星期日（`7`） | | `W` | 一个月里的周数(1-5)(第一周从该月第一天开始) | | `WW` | 一年里的周数(1-53)(第一周从该年的第一天开始) | | `IW` | ISO一年里的周数(01-53；第一个星期四在第一周里) | | `CC` | 世纪(2 位)(20 世纪从 2001-01-01 开始) | | `J` | 儒略日(自公元前 4714 年 11 月 24日午夜来的天数) | | `Q` | 季度（`to_date`和`to_timestamp`忽略此项） | | `RM` | 罗马数字的月份(I-XII ；I=January)(大写) | | `rm` | 罗马数字的月份(i-xii ；i=January)(小写) | | `TZ` | 时区名(大写) | | `tz` | 时区名(小写) | 有一些修饰词可以应用于模板来修改它们的行为。比如，`FMMonth` 就是带着`FM`前缀的`Month`模式。 [Table 9-22](#calibre_link-1068) 显示了用于日期/时间格式化的修饰词模式。 **Table 9-22\. 日期/时间格式化的模板模式修饰词** | 修饰词 | 描述 | 例子 | | --- | --- | --- | | `FM` 前缀 | 填充模式(抑制填充空白和尾随零) | `FMMonth` | | `TH` 后缀 | 大写顺序数后缀 | `DDTH`, 例如, `12TH` | | `th` 后缀 | 小写顺序数后缀 | `DDth`, 例如, `12th` | | `FX` 前缀 | 固定格式全局选项(见用法须知) | `FX Month DD Day` | | `TM` 前缀 | 翻译模式(基于[lc_time](#calibre_link-2206)显示本地化的日期和月份名) | `TMMonth` | | `SP` 后缀 | 拼写模式(未实现) | `DDSP` | 日期/时间格式化的用法须知： * `FM` suppresses leading zeroes and trailing blanks that would otherwise be added to make the output of a pattern be fixed-width. In PostgreSQL, `FM` modifies only the next specification, while in Oracle `FM` affects all subsequent specifications, and repeated `FM` modifiers toggle fill mode on and off. `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"`忽略两个输入字符。 * 如果你想在输出里有双引号，那么你必须在它们前面放反斜杠，比如`'\"YYYY Month\"'`。 * 如果年份的格式规范少于四个字节，例如`YYY`，并且提供的年份少于四个字节，那么年份将调整为接近于2020，例如`95`成为1995。 * 如果你使用的年份长于4位字符，那么用`YYYY`从字符串向`timestamp` 或`date`做转换时要受到限制。你必须在`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`一起使用，那么年份用指定的世纪计算。如果指定了世纪而没有指定年，那么假设使用这个世纪的第一年。 * ISO周时间（有别于公历日期）可以用下面的两种方法之一声明为 `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周和公历日期字段混合构造日期是没有意义的，并且将导致一个错误。在ISO年的范围，"月"和"月中的天"的概念没有意义。在公历年的范围，ISO周没有意义。用户应该避免混合公历和ISO日期规范。 * 将字符串转化为`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', '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)`将`HH`和`HH12`格式化为12小时，也就是零时和36时输出是`12`，而`HH24`可以输出完整的小时数，时间间隔可以超过23。 [Table 9-23](#calibre_link-1067)显示了用于数值格式化的模板模式。 **Table 9-23\. 数值格式化的模版模式** | 模式 | 描述 | | --- | --- | | `9` | 带有指定数值位数的值 | | `0` | 带前导零的值 | | `.` (句点) | 小数点 | | `,` (逗号) | 分组(千) 分隔符 | | `PR` | 尖括号内负值 | | `S` | 带符号的数值(使用区域设置) | | `L` | 货币符号(使用区域设置) | | `D` | 小数点(使用区域设置) | | `G` | 分组分隔符(使用区域设置) | | `MI` | 在指明的位置的负号(如果数字 < 0) | | `PL` | 在指明的位置的正号(如果数字 > 0) | | `SG` | 在指明的位置的正/负号 | | `RN` | 罗马数字(输入在 1 和 3999 之间) | | `TH` or `th` | 序数后缀 | | `V` | 移动指定位(小数)(参阅注解) | | `EEEE` | 指数为科学记数法 | 数字格式化的用法须知： * 使用`SG`, `PL`, `MI` 生成的符号并不挂在数字上面；比如，`to_char(-12, 'MI9999')` 生成`'- 12'`；但是`to_char(-12, 'S9999')` `' -12'`。Oracle里的实现不允许在`9` 前面使用`MI`，而是要求`9`在`MI`前面。 * `9`声明和`9`的个数相同的数字位数的数值。如果某个数值位没有数字，则输出一个空白。 * `TH`不会转换小于零的数值，也不会转换小数。 * `PL`, `SG`, `TH` 是PostgreSQL扩展。 * `V`方便地把输入值乘以 `10^``_n_`，这里`_n_`是跟在`V`后面的数字。 `to_char`不支持把`V` 与一个小数点组合在一起使用(也就是说`99.9V99`是不允许的)。 * `EEEE`（科学记数法）不能和任何其他格式化的模式或修饰符以外的数字和小数点模式混合使用，并且必须在格式化字符串的后面（例如，`9.99EEEE`是合法的模式）。一定的修饰符可以应用于任何模板模式来改变其行为。例如，`FM9999` 是`9999`模式和`FM`修饰符。 [Table 9-24](#calibre_link-2207)显示了数字格式的修饰符模式。 **Table 9-24\. 数字格式的模板模式修饰符** | 修饰符 | 描述 | 示例 | | --- | --- | --- | | `FM` 前缀 | 填充模式 (抑制填充空白和尾随零) | `FM9999` | | `TH` 后缀 | 大写顺序数后缀 | `999TH` | | `th` 后缀 | 小写顺序数后缀 | `999th` | [Table 9-25](#calibre_link-2208)显示了一些 `to_char`函数的用法。 **Table 9-25\. `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, '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'` |