9.8. 数据类型格式化函数

PostgreSQL格式化函数提供一套强大的工具用于把各种数据类型 （日期/时间、整数、浮点、数字） 转换成格式化的字符串以及反过来从格式化的字符串转换成 指定的数据类型。表 9-21列出了这些函数。这些函数都遵循一个公共的调用习惯： 第一个参数是待格式化的值，而第二个是一个定义输出或输入格式的模板。

一个单参数的to_timestamp函数也能使用。它接受一个double precision参数， 把它从 Unix 纪元（从 1970-01-01 00:00:00+00 开始的秒数）转换成 timestamp with time zone（Integer Unix 纪元被隐式地造型成double precision）。

在一个to_char输出模板串中，一些特定的模式可以被识别并且被替换成基于给定值的被恰当地格式化的数据。任何不属于模板模式的文本都简单地照字面拷贝。同样，在一个输入 模板串里（对其他函数），模板模式标识由输入数据串提供的值。

表 9-22展示了可以用于格式化日期和时间值的模版。

修饰语可以被应用于模板模式来修改它们的行为。例如，FMMonth就是带着FM修饰语的Month模式。表 9-23展示了可用于日期/时间格式化的修饰语模式。

日期/时间格式化的使用须知：

• 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-24展示了可以用于格式化数字值的模版模式。

数字格式化的用法须知：

• 使用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-25中展示了用于数字格式化模式修饰语。

表 9-26展示了一些使用to_char函数的例子。