PostgreSQL格式化函数提供一套强大的工具用于把各种数据类型 (日期/时间、整数、浮点、数字) 转换成格式化的字符串以及反过来从格式化的字符串转换成 指定的数据类型。表 9.26列出了这些函数。这些函数都遵循一个公共的调用规范: 第一个参数是待格式化的值,而第二个是一个定义输出或输入格式的模板。
表 9.26. 格式化函数
函数 描述 例子 |
|---|
根据给定的格式将时间戳转换为字符串。
|
根据给定的格式将间隔转换为字符串。
|
根据给定的格式将数字转换为字符串;适用于
|
根据给定的格式将字符串转换为日期。
|
根据给定的格式将字符串转换为数字。
|
根据给定的格式将字符串转换为时间戳。
(也请参见表 9.33中的
|
to_timestamp和to_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,
PM or pm | 正午指示器(不带句号) |
A.M., a.m.,
P.M. or p.m. | 正午指示器(带句号) |
Y,YYY | 带逗号的年(4 位或者更多位) with comma |
YYYY | 年(4 位或者更多位) |
YYY | 年的最后 3 位数字 |
YY | 年的最后 2 位数字 |
Y | 年的最后 1 位数字 |
IYYY | ISO 8601 周编号方式的年(4 位或更多位) |
IYY | ISO 8601 周编号方式的年的最后 3 位数字 |
IY | ISO 8601 周编号方式的年的最后 2 位数字 |
I | ISO 8601 周编号方式的年的最后 1 位数字 |
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 | 儒略日期(从本地午夜的公元前 4714 年 11 月 24 日开始的整数日数;参见 第 B.7 节) |
Q | 季度 |
RM | 大写形式的罗马计数法的月 (I–XII; I=一月) |
rm | 小写形式的罗马计数法的月 (i–xii; i=一月) |
TZ | 大写形式的时区缩写(仅在to_char中支持) |
tz | 小写形式的时区缩写(仅在to_char中支持) |
TZH | 时区的小时 |
TZM | 时区的分钟 |
OF | 从UTC开始的时区偏移(仅在to_char中支持) |
修饰语可以被应用于模板模式来修改它们的行为。例如,FMMonth就是带着FM修饰语的Month模式。表 9.28展示了可用于日期/时间格式化的修饰语模式。
表 9.28. 用于日期/时间格式化的模板模式修饰语
| 修饰语 | 描述 | 例子 |
|---|---|---|
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抑制尾随空格,无论是否指定FM。
to_timestamp和to_date在输入中忽略大小写;
因此,例如MON,Mon和mon都接受相同的字符串。
当使用TM修饰符时,根据函数输入排序规则执行大小写折叠(参见第 24.2 节)。
to_timestamp和to_date
跳过输入字符串开头和日期时间值周围的多个空格,除非使用FX选项。例如,
to_timestamp(' 2000 JUN', 'YYYY MON')和
to_timestamp('2000 - JUN', 'YYYY-MON')是有效的,但是
to_timestamp('2000 JUN', 'FXYYYY MON')会返回错误,
因为to_timestamp只接受单个空格。
FX必须作为模板中的第一项指定。
在to_timestamp和to_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_date,to_number和to_timestamp中,
文本和双引号字符串会导致跳过字符串中包含的字符数;例如"XX"跳过两个输入字符
(无论它们是否为XX)。
在PostgreSQL 12之前,可以使用非字母或非数字字符跳过输入字符串中的任意文本。例如,
to_timestamp('2000y6m1d', 'yyyy-MM-DD')曾经有效。现在,您只能使用字母字符来实现这一目的。例如,
to_timestamp('2000y6m1d', 'yyyytMMtDDt')和
to_timestamp('2000y6m1d', 'yyyy"y"MM"m"DD"d"')
跳过了y,m和d。
如果您想在输出中使用双引号,必须在其前面加上反斜杠,例如'\"YYYY
Month\"'。
反斜杠在双引号之外不起特殊作用。在双引号字符串内部,反斜杠会使下一个字符被直接解释,无论是什么(但除非下一个字符是双引号或另一个反斜杠,否则没有特殊效果)。
在to_timestamp和to_date中,
如果年份格式规范少于四位数字,例如YYY,并且提供的年份少于四位数字,
年份将被调整为最接近2020年的年份,例如95变为1995年。
在to_timestamp和to_date中,
负年份被视为BC纪元。如果同时写入负年份和显式的BC字段,
则再次得到AD。年份零被视为公元前1年。
在to_timestamp和to_date中,
YYYY转换在处理超过4位数字的年份时有限制。您必须在YYYY后使用一些非数字字符或模板,
否则年份总是被解释为4位数字。例如(使用年份20000):
to_date('200001130', 'YYYYMMDD')将被解释为4位年份;而应该在年份后使用非数字分隔符,如
to_date('20000-1130', 'YYYY-MMDD')或
to_date('20000Nov30', 'YYYYMonDD')。
在to_timestamp和to_date中,
如果存在YYY、YYYY或Y,YYY字段,
则会接受但忽略CC(世纪)字段。如果CC与
YY或Y一起使用,则结果将计算为指定世纪中的那一年。
如果指定了世纪但未指定年份,则假定为该世纪的第一年。
在to_timestamp和to_date中,
星期几的名称或数字(DAY,D,
以及相关字段类型)是被接受的,但在计算结果时会被忽略。同样适用于季度
(Q)字段。
在to_timestamp和to_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.3、12.30和12.300指定相同数量的毫秒。
要获得三毫秒,必须写成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) 格式化HH和HH12,
如在12小时制时钟上显示,例如零小时和36小时都输出为12,
而HH24输出完整的小时值,在interval值中可以超过23。
表 9.29展示了可以用于格式化数字值的模版模式。
表 9.29. 用于数字格式化的模板模式
| 模式 | 描述 |
|---|---|
9 | 数位(如果无意义可以被删除) |
0 | 数位(即便没有意义也不会被删除) |
. (period) | 小数点 |
, (comma) | 分组(千)分隔符 |
PR | 尖括号内的负值 |
S | 带符号的数字(使用区域) |
L | 货币符号(使用区域) |
D | 小数点(使用区域) |
G | 分组分隔符(使用区域) |
MI | 在指定位置的负号(如果数字 < 0) |
PL | 在指定位置的正号(如果数字 > 0) |
SG | 在指定位置的正/负号 |
RN | 罗马数字(输入在 1 和 3999 之间) |
TH or th | 序数后缀 |
V | 移动指定位数(参阅注解) |
EEEE | 科学记数的指数 |
数字格式化的使用注意事项:
0指定一个数字位置,即使它包含前导/尾随零,也将始终打印出来。
9也指定一个数字位置,但如果它是一个前导零,则将被替换为一个空格,
而如果它是一个尾随零并且指定了填充模式,则将被删除。
(对于to_number(),这两个模式字符是等效的。)
如果格式提供的小数位数少于被格式化的数字,则to_char()将会将数字四舍五入到指定的小数位数。
模式字符S、L、D和G表示当前区域设置定义的符号、货币符号、小数点和千位分隔符字符
(参见lc_monetary
和lc_numeric)。模式字符句点和逗号表示这些确切字符,具有小数点和千位分隔符的含义,不受区域设置影响。
如果在to_char()的模式中没有明确指定符号,将为符号保留一列,并将其锚定到(出现在)数字的左侧。
如果S出现在一些9的左侧,它也将锚定到数字。
使用SG、PL或MI格式化的符号不与数字绑定;
例如,to_char(-12, 'MI9999')会产生'- 12',
但to_char(-12, 'S9999')会产生' -12'。
(Oracle实现不允许在9之前使用MI,而是要求9在MI之前。)
TH不会转换小于零的值,也不会转换小数。
PL,SG和
TH是PostgreSQL
的扩展。
在to_number函数中,如果使用非数据模板模式,如L或TH,
则会跳过相应数量的输入字符,无论它们是否与模板模式匹配,除非它们是数据字符(即数字、符号、小数点或逗号)。
例如,TH会跳过两个非数据字符。
V与to_char一起,
将输入值乘以10^,
其中nn是跟在V后面的数字位数。
V与to_number一起以类似的方式除法。
to_char和to_number不支持与小数点结合使用的V
(例如,不允许使用99.9V99)。
EEEE(科学计数法)不能与任何其他格式模式或修饰符结合使用,除了数字和小数点模式之外,必须位于格式字符串的末尾(例如,9.99EEEE是一个有效模式)。
某些修饰语可以被应用到任何模板来改变其行为。例如,FM99.99是带有FM修饰语的99.99模式。表 9.30中展示了用于数字格式化模式修饰语。
表 9.30. 用于数字格式化的模板模式修饰语
| 修饰语 | 描述 | 例子 |
|---|---|---|
FM prefix | 填充模式(抑制拖尾零和填充的空白) | FM99.99 |
TH suffix | 大写序数后缀 | 999TH |
th suffix | 小写序数后缀 | 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' |