9.8. 数据类型格式化函数

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

表 9.25. 格式化函数

函数

描述

例子

to_char ( timestamp, text ) → text

to_char ( timestamp with time zone, text ) → text

根据给定的格式将时间戳转换为字符串。

to_char(timestamp '2002-04-20 17:31:12.66', 'HH12:MI:SS')05:31:12

to_char ( interval, text ) → text

根据给定的格式将间隔转换为字符串。

to_char(interval '15h 2m 12s', 'HH24:MI:SS')15:02:12

to_char ( numeric_type, text ) → text

根据给定的格式将数字转换为字符串;适用于 integerbigintnumericrealdouble precision

to_char(125, '999')125

to_char(125.8::real, '999D9')125.8

to_char(-125.8, '999D99S')125.80-

to_date ( text, text ) → date

根据给定的格式将字符串转换为日期。

to_date('05 Dec 2000', 'DD Mon YYYY')2000-12-05

to_number ( text, text ) → numeric

根据给定的格式将字符串转换为数字。

to_number('12,454.8-', '99G999D9S')-12454.8

to_timestamp ( text, text ) → timestamp with time zone

根据给定的格式将字符串转换为时间戳。 (也请参见表 9.32中的to_timestamp(double precision) 。)

to_timestamp('05 Dec 2000', 'DD Mon YYYY')2000-12-05 00:00:00-05


提示

to_timestampto_date存在的目的是为了处理无法用简单造型转换的输入格式。对于大部分标准的日期/时间格式,简单地把源字符串造型成所需的数据类型是可以的,并且简单很多。类似地,对于标准的数字表示形式,to_number也是没有必要的。

在一个to_char输出模板串中,一些特定的模式可以被识别并且被替换成基于给定值的被恰当地格式化的数据。任何不属于模板模式的文本都简单地照字面拷贝。同样,在一个输入 模板串里(对其他函数),模板模式标识由输入数据串提供的值。如果在模板字符串中有不是模板模式的字符,输入数据字符串中的对应字符会被简单地跳过(不管它们是否等于模板字符串字符)。

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

表 9.26. 用于日期/时间格式化的模板模式

模式描述
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 位数字
IYYYISO 8601 周编号方式的年(4 位或更多位)
IYYISO 8601 周编号方式的年的最后 3 位数字
IYISO 8601 周编号方式的年的最后 2 位数字
IISO 8601 周编号方式的年的最后 1 位数字
BC, bc, ADad纪元指示器(不带句号)
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)
ID周中的 ISO 8601 日,周一 (1) 到周日 (7)
W月中的周 (1–5) (第一周从该月的第一天开始)
WW年中的周数 (1–53) (第一周从该年的第一天开始)
IWISO 8601 周编号方式的年中的周数 (01–53; 新的一年的第一个周四在第一周)
CC世纪(2 位数)(21 世纪开始于 2001-01-01)
J儒略日(从午夜 UTC 的公元前 4714 年 11 月 24 日开始的整数日数)
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.27展示了可用于日期/时间格式化的修饰语模式。

表 9.27. 用于日期/时间格式化的模板模式修饰语

修饰语描述例子
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

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

表 9.28展示了可以用于格式化数字值的模版模式。

表 9.28. 用于数字格式化的模板模式

模式描述
9数位(如果无意义可以被删除)
0数位(即便没有意义也不会被删除)
. (period)小数点
, (comma)分组(千)分隔符
PR尖括号内的负值
S带符号的数字(使用区域)
L货币符号(使用区域)
D小数点(使用区域)
G分组分隔符(使用区域)
MI在指定位置的负号(如果数字 < 0)
PL在指定位置的正号(如果数字 > 0)
SG在指定位置的正/负号
RN罗马数字(输入在 1 和 3999 之间)
TH or th序数后缀
V移动指定位数(参阅注解)
EEEE科学记数的指数

数字格式化的用法须知:

某些修饰语可以被应用到任何模板来改变其行为。例如,FM99.99是带有FM修饰语的99.99模式。表 9.29中展示了用于数字格式化模式修饰语。

表 9.29. 用于数字格式化的模板模式修饰语

修饰语描述例子
FM prefix填充模式(抑制拖尾零和填充的空白)FM99.99
TH suffix大写序数后缀999TH
th suffix小写序数后缀999th

表 9.30展示了一些使用to_char函数的例子。

表 9.30. 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'