数据类型格式化函数#

数据类型格式化函数

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

表 格式化函数

函数

返回类型

描述

例子

to_char (timestamp, text)

text

把时 间戳转成字符串

to_char (current_t stamp, HH12:MI:SS')

to_ch ar(interval, text)

text

把 间隔转成字符串

to_c har(interval ' 15h 2m 12s', ' HH24:MI:SS')

to_char (int, text)

text

把 整数转成字符串

to_char (125, '999')

to_ch ar(doubl e precision, text)

text

把实数或双 精度转成字符串

to_ char(125.8::re al, '999D9')

to_c har(numeric, text)

text

把 数字转成字符串

to_char (-125.8 , '999D99S')

t o_date(text, text)

date

把 字符串转成日期

to_date('05  Dec 2000', 'D D Mon YYYY')

to_ number(text, text)

numeric

把 字符串转成数字

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

to_tim estamp(text, text)

timestamp wi th time zone

把字 符串转成时间戳

to _timestamp('05  Dec 2000', 'D D Mon YYYY')

备注

注意

还有一个单一参数的to_timestamp函数，请见表。

小技巧

提示

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

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

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

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

模式

描述

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

大写形式的时区缩 写（仅在to_char中支持）

tz

小写形式的时区缩 写（仅在to_char中支持）

TZH

时区的小时

TZM

时区的分钟

OF

从UTC开始的时区偏 移（仅在to_char中支持）

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

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

修饰语

描述

例子

FM prefix

填充模式（抑 制前导零和填充的空格）

FMMonth

TH suffix

大写形式的序数后缀

DDTH, e.g., 12TH

th suffix

小写形式的序数后缀

DDth, e.g., 12th

FX prefix

固定的格式化 全局选项（见使用须知）

FX Month DD Day

TM prefix

翻译模式（基于lc_tim e

打印本地化的日和月名）

TMMonth

SP suffix

拼写模式（未实现）

DDSP

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

  • FM抑制前导的零或尾随的空白， 否则会把它们增加到输入从而把一个模式的输出变成固定宽度。在PostgreSQL中，FM只修改下一个声明，而在 Oracle 中，FM影响所有随后的声明，并且重复的FM修饰语将触发填充模式开和关。

  • TM不包括结尾空白。to_timestamp和to_date会忽略TM修饰语。

  • to_timestamp和to_date跳过了输入字符串开头和日期和时间值周围的多个空格，除非使用了FX选项。 例如，to_to_timestamp(' 2000         JUN', 'YYY-MON')和to_timestamp('2000 - JUN', 'YYY-MON')都能工作，但to_timestamp('2000        JUN', 'FXYYYY-MON')返回一个错误，因为to_timestamp只期望一个空格。FX必须指定为模板中的第一个项目。

  • to_timestamp和to_date的模板字符串中的分隔符（一个空格或非字母/非数字字符）与输入字符串中的任何一个分隔符相匹配，或者被跳过，除非使用了FX选项。例如，to_to_timestamp('2000JUN', 'YYY///MON')和to_timestamp('2000/JUN', 'YYY/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', 'YYY TZH')与-10匹配，但to_timestamp('2000 -10', 'YYYY  TZH')匹配10到TZH。

  • 在to_char模板里可以有普通文本，并且它们会被照字面输出。你可以把一个子串放到双引号里强迫它被解释成一个文本，即使它里面包含模板模式也如此。例如，在 '"Hello Year "YYYY'中，YYYY将被年份数据代替，但是Year中单独的Y不会。在to_date、to_number以及to_timestamp中，文本和双引号字符串会导致跳过该字符串中所包含的字符数量，例如"XX"会跳过两个输入字符（不管它们是不是XX）。

    小技巧

    提示

    在 OushuDB6.0 之前，可以使用非字母或非数字字符跳过输入字符串中的任意文本。例如，to_timestamp('2000y6m1d', 'yyyyy-MM-DD')以前是有效的。 现在，你只能使用字母字符来实现这个目的。 例如，to_timestamp(''2000y6m1d', 'yyyytMMtDDt')和to_timestamp('2000y6m1d', 'yyyyy"y"MM"m"DD"d"')跳过y、m和d。

  • 如果你想在输出里有双引号，那么你必须在它们前面放反斜线，例如 '\"YYYY Month\"'。不然，在双引号字符串外面的反斜线就不是特殊的。在双引号字符串内，反斜线会导致下一个字符被取其字面形式，不管它是什么字符（但是这没有特殊效果，除非下一个字符是一个双引号或者另一个反斜线）。

  • 在to_timestamp和to_date中，如果年份格式声明少于四位（如YYY）并且提供的年份少于四位，年份将被调整为最接近于 2020 年，例如95会变成 1995。

  • 在to_timestamp和to_date中，在处理超过4位数的年份时，YYYY转换具有限制。你必须在YYYY后面使用一些非数字字符或者模板， 否则年份总是被解释为 4 位数字。例如（对于 20000 年）：to_date('200001131', 'YYYYMMDD')将会被解释成一个 4 位数字的年份，而不是在年份后使用一个非数字分隔符，像to_date('20000-1131', 'YYYY-MMDD')或to_date('20000Nov31', 'YYYYMonDD')。

  • 在to_timestamp和to_date中，CC（世纪）字段会被接受，但是如果有YYY、YYYY或者Y,YYY字段则会忽略它。如果CC与YY或Y一起使用，则结果被计算为指定世纪中的那一年。如果指定了世纪但是没有指定年，则会假定为该世纪的第一年。

  • 在to_timestamp和to_date中，工作日名称或编号（DAY、D以及相关的字段类型）会被接受，但会为了计算结果的目的而忽略。季度（Q）字段也是一样。

  • 在to_timestamp和to_date中，一个 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的东西，那会得到在 起始年附近令人惊讶的结果（详见节）。

  • 在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会输出完整的小时值，对于间隔它可以超过 23.

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

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

模式

描述

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()来说，这两种模式字符等效）。

  • 模式字符S、L、D以及G表示当前locale定义的负号、货币符号、小数点以及数字分隔符字符（见lc_monetary和lc_numeric）。不管locale是什么，模式字符句号和逗号就表示小数点和数字分隔符。

  • 对于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会跳过两个非数据字符。

  • 带有to_char的V会把输入值乘上10^n，其中``n``是跟在V后面的位数。带有to_number的V以类似的方式做除法。to_char和to_number不支持使用结合小数点的V（例如，不允许99.9V99）。

  • EEEE（科学记数法）不能和任何其他格式化模式或修饰语（数字和小数点模式除外）组合在一起使用，并且必须位于格式化字符串的最后（例如9.99EEEE是一个合法的模式）。

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

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

修饰语

描述

例子

FM prefix

填充模式（抑制拖尾零和填充的空白）

FM99.99

TH suffix

大写序数后缀

999TH

th suffix

小写序数后缀

999th

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

表 to_char 例子

表达式

结果

to_char(current_ timestamp, 'Day, DD  HH12:MI:SS')

'Tuesday  , 06  05:39:18'

to_char(current_time stamp, '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'