PostgreSQL 9.3.4 文档 | ||||
---|---|---|---|---|
Prev | Up | Chapter 9. 函数和操作符 | Next |
PostgreSQL格式化函数提供一套强大的工具用于把各种数据类型 (日期/时间、整数、浮点、数字) 转换成格式化的字符串以及反过来从格式化的字符串转换成 指定的数据类型。Table 9-20列出了这些函数。这些函数都遵循一个公共的调用习惯: 第一个参数是待格式化的值,而第二个是一个定义输出或输入格式的模板。
一个单参数的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展示了可以用于格式化日期和时间值的模版。
Table 9-21. 用于日期/时间格式化的模板模式
模式 | 描述 |
---|---|
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 年(4 位或更多位) |
IYY | ISO 年的最后 3 位 |
IY | ISO 年的最后 2 位 |
I | ISO 年的最后一位 |
BC, bc, AD or ad | era indicator (without periods) |
B.C., b.c., A.D. or 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,年的第 1 日时第一个 ISO 周的周一) |
DD | 月中的日(01-31) |
D | 周中的日,周日(1)到周六(7) |
ID | 周中的 ISO 日,周一(1)到周日(7) |
W | 月中的周(1-5)(第一周从该月的第一天开始) |
WW | 年中的周数(1-53)(第一周从该年的第一天开始) |
IW | 年中的 ISO 周数(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 | 小写形式的时区名称 |
修饰语可以被应用于模板模式来修改它们的行为。例如,FMMonth就是带着FM修饰语的Month模式。Table 9-22展示了可用于日期/时间格式化的修饰语模式。
Table 9-22. 用于日期/时间格式化的模板模式修饰语
修饰语 | 描述 | 例子 |
---|---|---|
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不包括结尾空白。
如果没有使用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 位的年份时,从字串向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 周日期(与一个格里高利日期相区别)可以用两种方法之一被指定为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指定了相同数目的毫秒。要得到三毫秒,你必须使用 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展示了可以用于格式化数字值的模版模式。
Table 9-23. 用于数字格式化的模板模式
模式 | 描述 |
---|---|
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模式。Table 9-24中展示了用于数字格式化模式修饰语。
Table 9-24. 用于数字格式化的模板模式修饰语
修饰语 | 描述 | 例子 |
---|---|---|
FM prefix | 填充模式(抑制空白和结尾处的零) | FM9999 |
TH suffix | 大写序数后缀 | 999TH |
th suffix | 小写序数后缀 | 999th |
Table 9-25展示了一些使用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' |