数据类型格式化函数
数据类型转换函数¶
SeaboxSQL转换函数提供一套强大的工具用于把各种数据类型（日期/时间、整数、浮点、数字） 转换成格式化的字符串以及反过来从格式化的字符串转换成指定的数据类型。这些函数都遵循公共的调用规范：第一个参数是待格式化的值，而第二个是一个定义输出或输入格式的模板。
转换函数¶
注意
-
to_timestamp和to_date存在的目的是为了处理无法用简单造型转换的输入格式。对于大部分标准的日期/时间格式，简单地把源字符串造型成所需的数据类型是可以的，并且简单很多。类似地，对于标准的数字表示形式，to_number也是没有必要的。 -
在一个
to_char输出模板串中，一些特定的模式可以被识别并且被替换成基于给定值的被恰当地格式化的数据。任何不属于模板模式的文本都简单地照字面拷贝。同样，在一个输入模板串里（对其他函数），模板模式标识由输入数据串提供的值。如果在模板字符串中有不是模板模式的字符，输入数据字符串中的对应字符会被简单地跳过（不管它们是否等于模板字符串字符）。
还有一个单一参数的to_timestamp函数，请见"日期/时间函数"
to_char()¶
- 语法
to_char(timestamp, text),to_char(interval, text),to_char(int, text),to_char(double precision, text),to_char(numeric, text)- 返回类型
- text
- 描述
- 把时间戳转成字符串; 把间隔转成字符串; 把实数或双精度转成字符串; 把数字转成字符串
- 示例
-
``` sql seaboxsql=# select to_char(current_timestamp, 'HH12:MI:SS'); to_char
03:40:21 (1 row)seaboxsql=# select to_char(interval '15h 2m 12s', 'HH24:MI:SS'); to_char
15:02:12 (1 row)
seaboxsql=# select to_char(125, '999'); to_char
125 (1 row)
seaboxsql=# select to_char(125.8::real, '999D9'); to_char
125.8 (1 row)
seaboxsql=# select to_char(-125.8, '999D99S'); to_char
125.80- (1 row)
seaboxsql=# select to_char(current_timestamp, 'Day, DD HH12:MI:SS');
to_char
Monday , 28 03:56:00 (1 row)
seaboxsql=# select to_char(current_timestamp, 'FMDay, FMDD HH12:MI:SS'); to_char
Monday, 28 03:56:00 (1 row)
seaboxsql=# select to_char(-0.1, '99.99');
to_char
-.10 (1 row)
seaboxsql=# select to_char(-0.1, 'FM9.99');
to_char
-.1 (1 row)
seaboxsql=# select to_char(-0.1, 'FM90.99');
to_char
-0.1 (1 row)
seaboxsql=# select to_char(0.1, '0.9');
to_char
0.1 (1 row)
seaboxsql=# select to_char(12, '9990999.9');
to_char
0012.0(1 row)
seaboxsql=# select to_char(12, 'FM9990999.9');
to_char
0012. (1 row)
seaboxsql=# select to_char(485, '999');
to_char
485 (1 row)
seaboxsql=# select to_char(-485, '999');
to_char
-485 (1 row)
seaboxsql=# select to_char(485, '9 9 9');
to_char
4 8 5 (1 row)
seaboxsql=# select to_char(1485, '9,999');
to_char
1,485 (1 row)
seaboxsql=# select to_char(1485, '9G999');
to_char
1,485 (1 row)
seaboxsql=# select to_char(148.5, '999.999');
to_char
148.500 (1 row)
seaboxsql=# select to_char(148.5, 'FM999.999');
to_char
148.5 (1 row)
seaboxsql=# select to_char(148.5, 'FM999.990');
to_char
148.500 (1 row)
seaboxsql=# select to_char(148.5, '999D999');
to_char
148.500 (1 row)
seaboxsql=# select to_char(3148.5, '9G999D999');
to_char
3,148.500 (1 row)
seaboxsql=# select to_char(-485, '999S');
to_char
485- (1 row)
seaboxsql=# select to_char(-485, '999MI');
to_char
485- (1 row)
seaboxsql=# select to_char(485, '999MI');
to_char
485 (1 row)
seaboxsql=# select to_char(485, 'FM999MI');
to_char
485 (1 row)
seaboxsql=# select to_char(485, 'PL999');
to_char
- 485 (1 row)
seaboxsql=# select to_char(485, 'SG999');
to_char
+485 (1 row)
seaboxsql=# select to_char(-485, 'SG999');
to_char
-485 (1 row)
seaboxsql=# select to_char(-485, '9SG99');
to_char
4-85 (1 row)
seaboxsql=# select to_char(-485, '999PR');
to_char
<485> (1 row)
seaboxsql=# select to_char(485, 'L999');
to_char
485 (1 row)
seaboxsql=# select to_char(485, 'RN');
to_char
CDLXXXV(1 row)
seaboxsql=# select to_char(485, 'FMRN');
to_char
CDLXXXV (1 row)
seaboxsql=# select to_char(5.2, 'FMRN');
to_char
V (1 row)
seaboxsql=# select to_char(482, '999th');
to_char
482nd (1 row)
seaboxsql=# select to_char(485, '"Good number:"999');
to_char
Good number: 485 (1 row)
seaboxsql=# select to_char(485.8, '"Pre:"999" Post:" .999');
to_char
Pre: 485 Post: .800 (1 row)
seaboxsql=# select to_char(12, '99V999');
to_char
12000 (1 row)
seaboxsql=# select to_char(12.4, '99V999');
to_char
12400 (1 row)
seaboxsql=# select to_char(12.45, '99V9');
to_char
125 (1 row)
seaboxsql=# select to_char(0.0004859, '9.99EEEE');
to_char
4.86e-04 (1 row) ```
to_date()¶
- 语法
to_date(text, text)- 返回类型
- date
- 描述
- 把字符串转成日期
- 示例
- ``` sql
seaboxsql=# select to_date('05 Dec 2000', 'DD Mon YYYY');
to_date
2000-12-05 (1 row) ```
to_number()¶
- 语法
to_number(text, text)- 返回类型
- numeric
- 描述
- 把字符串转成数字
- 示例
- ``` sql
seaboxsql=# select to_number('12,454.8-', '99G999D9S');
to_number
-12454.8 (1 row) ```
to_timestamp()¶
- 语法
to_timestamp(text, text)- 返回类型
- timestamp with time zone
- 描述
- 把字符串转成时间戳
- 示例
- ``` sql
seaboxsql=# select to_timestamp('05 Dec 2000', 'DD Mon YYYY') ;
to_timestamp
2000-12-05 00:00:00+00 (1 row) ```
用于日期/时间格式化的模板模式¶
下文展示了可以用于格式化日期和时间值的模版。
- 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_time打印本地化的日和月名），示例：
TMMonth - SP suffix
- 拼写模式（未实现），示例：
DDSP
日期/时间格式化的使用须知¶
-
FM抑制前导的零或尾随的空白，否则会把它们增加到输入从而把一个模式的输出变成固定宽度。在SeaboxSQL中，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_char模板里可以有普通文本，并且它们会被照字面输出。你可以把一个子串放到双引号里强迫它被解释成一个文本，即使它里面包含模板模式也如此。例如，在'"Hello Year "YYYY'中，YYYY将被年份数据代替，但是Year中单独的Y不会。在to_date、to_number以及to_timestamp中，文本和双引号字符串会导致跳过该字符串中所包含的字符数量，例如"XX"会跳过两个输入字符（不管它们是不是XX）。 -
如果想在输出里有双引号，那么你必须在它们前面放反斜线，例如
'\"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 之间）
THorth- 序数后缀
V- 移动指定位数（参阅注解）
EEEE- 科学记数的指数
数字格式化的用法须知：
-
0指定一个总是被打印的数位，即便它包含前导/拖尾的零。9也指定一个数位，但是如果它是前导零则会被空格替换，而如果是拖尾零并且指定了填充模式则它会被删除（对于to_number()来说，这两种模式字符等效）。 -
模式字符
S、L、D以及G表示当前locale定义的负号、货币符号、小数点以及数字分隔符字符。不管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是SeaboxSQL扩展。 -
在
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