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') |
注意: 还有一个单一参数的
to_timestamp函数， 请见表 9-30。
在一个to_char输出模板串中，一些特定的模式可以被识别并且被替换成基于给定值的被恰当地格式化的数据。任何不属于模板模式的文本都简单地照字面拷贝。同样，在一个输入 模板串里（对其他函数），模板模式标识由输入数据串提供的值。
表 9-24展示了可以用于格式化日期和时间值的模版。
表 9-24. 用于日期/时间格式化的模板模式
| 模式 | 描述 |
|---|---|
| HH | 一天中的小时 （01-12） |
| HH12 | 一天中的小时 （01-12） |
| HH24 | 一天中的小时 （00-23） |
| MI | 分钟 （00-59）minute (00-59) |
| SS | 秒（00-59） |
| MS | 毫秒（000-999） |
| US | 微秒（000000-999999） |
| SSSS | 午夜后的秒（0-86399） |
| AM, am, PM or pm | 正午指示器（不带句号） |
| A.M., a.m., P.M. or p.m. | 正午指示器（带句号） |
| Y,YYY | 带逗号的年（4 位或者更多位） |
| YYYY | 年（4 位或者更多位） |
| YYY | 年的后三位 |
| YY | 年的后两位 |
| Y | 年的最后一位 |
| IYYY | ISO 8601 周编号方式的年（4 位或更多位） |
| IYY | ISO 8601 周编号方式的年的最后 3 位 |
| IY | ISO 8601 周编号方式的年的最后 2 位 |
| I | ISO 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） |
| 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；新的一年的第一个周四在第一周） |
| CC | 世纪（2 位数）（21 世纪开始于 2001-01-01） |
| J | 儒略日（从午夜 UTC 的公元前 4714 年 11 月 24 日开始的整数日数） |
| Q | 季度（to_date和to_timestamp会忽略） |
| RM | 大写形式的罗马计数法的月（I-XII；I 是 一月） |
| rm | 小写形式的罗马计数法的月（i-xii；i 是 一月） |
| TZ | 大写形式的时区名称 |
| tz | 小写形式的时区名称 |
| OF | 时区偏移量 |
修饰语可以被应用于模板模式来修改它们的行为。例如，FMMonth就是带着FM修饰语的Month模式。表 9-25展示了可用于日期/时间格式化的修饰语模式。
表 9-25. 用于日期/时间格式化的模板模式修饰语
| 修饰语 | 描述 | 例子 |
|---|---|---|
| FM prefix | 填充模式（抑制前导零以及填充的空格） | FMMonth |
| TH suffix | 大写形式的序数后缀 | DDTH, e.g., 12TH |
| th suffix | 小写形式的序数后缀 | DDth, e.g., 12th |
| FX prefix | 固定的格式化全局选项（见使用须知） | FX Month DD Day |
| TM prefix | 翻译模式（基于lc_time打印本地化的日和月名） | TMMonth |
| SP suffix | 拼写模式（未实现） | DDSP |
日期/时间格式化的使用须知：
FM抑制前导的零或尾随的空白， 否则会把它们增加到输入从而把一个模式的输出变成固定宽度。在PostgreSQL中，FM只修改下一个声明，而在 oracle 中，FM影响所有随后的声明，并且重复的FM修饰语将触发填充模式开和关。
TM不包括结尾空白。
to_timestamp和to_date忽略TM修饰符。如果没有使用FX选项，
to_timestamp和to_date会跳过输入字符串中的多个空白。例如，to_timestamp('2000 JUN', 'YYYY MON')是正确的，但to_timestamp('2000 JUN', 'FXYYYY MON')会返回一个错误，因为to_timestamp只期望一个空白。FX必须被指定为模板中的第一个项。to_timestamp和to_date的存在是为了 处理无法被简单转换的输入格式。这些函数会从字面上解释输入，并做一点点错误检查。当 它们产生有效输出时，该转换可能得到意料之外的结果。例如，这些函数的输入没有被限制 在正常的范围内，因此to_date('20096040','YYYYMMDD')会返回 2014-01-17而不是报错。而造型不会有这样的行为。在
to_char模板里可以有普通文本，并且它们会被照字面输出。你可以把一个子串放到双引号里强迫它被解释成一个文本，即使它里面包含模式关键字也如此。例如，在 '"Hello Year "YYYY'中，YYYY将被年份数据代替，但是Year中单独的Y不会。在to_date、to_number和to_timestamp中，双引号字符串会跳过包含在字符串中字符个数的字符，例如"XX"跳过两个输入字符。如果你想在输出里有双引号，那么你必须在它们前面放反斜线，例如 '\"YYYY Month\"'。
如果年份格式声明少于四位（如YYY）并且提供的年份少于四位，年份将被调整为最接近于 2020 年，例如95会变成 1995。
在处理长于 4 位的年份时，从字串向timestamp或者date的YYYY转换有一个限制。你必须在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 8601 周编号的日期（与一个格里高利日期相区别）可以用两种方法之一被指定为
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 8601 周编号和格里高利日期的域来输入一个日期是无意义的，并且将导致一个错误。在一个 ISO 周编号的年的环境下，一个"月"或"月中的日"的概念没有意义。在一个格里高利年的环境下，ISO 周没有意义。用户应当避免混合格里高利和 ISO 日期声明。
小心 虽然
to_date将会拒绝混合使用格里高利和 ISO 周编号日期的域，to_char却不会，因为YYYY-MM-DD (IYYY-IDDD) 这种输出格式也会有用。但是避免写类似IYYY-MM-DD的东西，那会得到在 起始年附近令人惊讶的结果（详见第 9.9.1 节）。 -
在从字符串到timestamp的转换中，毫秒（MS）和微秒（US）值都被用作小数点后的秒位。例如to_timestamp('12:3', 'SS:MS')不是 3 毫秒, 而是 300，因为该转换把它看做 12 + 0.3 秒。这意味着对于格式SS:MS而言，输入值12:3、12:30和12:300指定了相同数目的毫秒。要得到三毫秒，你必须使用 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.
表 9-26展示了可以用于格式化数字值的模版模式。
表 9-26. 用于数字格式化的模板模式
| 模式 | 描述 |
|---|---|
| 9 | 带有指定位数的值 |
| 0 | 带前导零的值 |
| . (period) | 小数点 |
| , (comma) | 分组（千）分隔符 |
| 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是带有FM修饰语的9999模式。表 9-27中展示了用于数字格式化模式修饰语。
表 9-27. 用于数字格式化的模板模式修饰语
| 修饰语 | 描述 | 例子 |
|---|---|---|
| FM prefix | 填充模式（抑制前导零和空白） | FM9999 |
| TH suffix | 大写序数后缀 | 999TH |
| th suffix | 小写序数后缀 | 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, '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' |