阅读(2.3k) 书签赞(0) 我要纠错

OceanBase 日期时间格式化

2021-06-17 15:09 更新

日期时间格式化指定了存储在数据库中日期时间数据的格式。日期时间格式化的总长度不能超过 22 个字符。

函数中的日期时间格式化

日期时间格式化出现在下面的数据类型转换中：

将非默认格式的字符值转换为日期时间值时，需要 TO_DATE、TO_TIMESTAMP 和 TO_TIMESTAMP_TZ 函数的参数指定日期时间的格式。
将日期时间值转换为非默认格式的字符值时，需要指定 TO_CHAR 函数的参数。

您可以通过以下方式指定日期时间格式。

通过会话参数 NLS_DATE_FORMAT、NLS_TIMESTAMP_FORMAT 或 NLS_TIMESTAMP_TZ_FORMAT 显式指定。
通过会话参数 NLS_TERRITORY 隐式指定。
ALTER SESSION 语句更改会话的默认日期时间格式。

日期时间格式化

日期时间格式化由一个或多个日期时间格式化元素组成。OceanBase 数据库支持的格式化元素请查阅 日期时间格式化元素表。

在格式化字符串中，相同的格式化元素不能出现两次，表示类似信息的格式化元素不能组合。例如，您不能在一个格式化字符串中同时使用 SYYYY和 BC 元素。
所有格式化元素都可以在 TO_CHAR、TO_DATE、TO_TIMESTAMP 和 TO_TIMESTAMP_TZ 函数中使用。
日期时间格式化元素 FF、TZD、TZH、TZM 和 TZR可以出现在时间戳和间隔格式化中，但不能出现在 DATE 格式中。
许多日期时间格式元素被空白填充或用零填充至指定的长度。

注意
建议您使用 4 位数的年份元素（YYYY），较短的年份元素会影响查询优化，因为年份只能在运行时确定。

日期时间格式化元素表

元素	日期时间函数是否支持？	说明
- / ，。 ; ： “文字”	是	标点和引用的文本会在结果中复制。
AD A.D.	是	表示公元纪年法，带有或不带有点号。
AM A.M.	是	表示上午，带有或不带有点号。
BC B.C.	是	表示公元前的年份，带有或不带有点号。
D	是	星期几（1-7）。
DAY	是	一天的名称。
DD	是	每月的一天（1-31）。
DDD	是	一年中的某天（1-366）。
DL	是	只能打印类似 "Monday, January, 01, 1996" 的固定格式。
DS	是	只能打印类似 "10-10-1996" 的固定格式。
DY	是	日期的缩写，返回星期值。
FF [1..9]	是	小数秒。使用数字 1~9 来指定返回值的小数秒部分的位数。默认为日期时间数据类型指定的精度。在时间戳和间隔格式中有效，但在 `DATE` 格式中无效。
FX	是	需要字符数据和格式模型之间的精确匹配。
HHHH12	是	小时（1-12）。12 小时制
HH24	是	小时（0-23）。24 小时制
YYYY	是	包含4位数字的年份。
MI	是	分钟（0-59）。
MM	是	月（01-12；一月份表示为 01 ）。
MON	是	月份的缩写。
MONTH	是	月份名称。
PM P.M.	是	表示下午，带有或不带有点号。
Q	是	季度（1、2、3、4； 1月-3月是第 1 季度）。
RR	是	RR 匹配两位数的年份。
RRRR	是	年。接受4位或2位输入。
SS	是	秒（0-59）。
SSSSS	是	午夜后的秒（0-86400）。
TZD	是	夏令时信息。TZD 值是带有夏令时信息的缩写时区字符串。在时间戳和间隔格式中有效，但在 `DATE` 格式中无效。
TZH	是	时区小时。在时间戳和间隔格式中有效，但在 `DATE` 格式中无效。
TZM	是	时区分钟。在时间戳和间隔格式中有效，但在 `DATE` 格式中无效。
TZR	是	时区区域信息。在时间戳和间隔格式中有效，但在 `DATE` 格式中无效。
X	是	小数点，永远是 '.' 。
Y,YYY	是	带逗号的年。
YYYYSYYYY	是	4位数字的年份。 S 代表用一个负号表示公元前的日期。
YYYYYY	是	年份的后 3、2 或 1 位数字。

说明
日期时间函数指的是TO_CHAR、TO_DATE、TO_TIMESTAMP 和 TO_TIMESTAMP_TZ 。

注意，以上转化要求输入的字符串日期能够与格式元素相匹配，否则会报错，例如：

obclient> SELECT TO_DATE( '31 Aug 2020', 'DD MON YYYY' ) FROM DUAL;
+----------------------------------+
| TO_DATE('31AUG2020','DDMONYYYY') |
+----------------------------------+
| 2020-08-31 00:00:00              |
+----------------------------------+
1 row in set (0.00 sec)

注意
OceanBase 数据库中默认的日期格式为 DD-MON-RR，如果要显示为上面的格式，可以通过执行 alter session set NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'; 修改当前会话的日期时间格式。

当您的格式串漏掉了一些元素时，会得到系统的报错信息：

obclient> SELECT TO_DATE( '31 Aug 2020', 'DD MON YYY' ) FROM DUAL;
ORA-01830: date format picture ends before converting entire input string

日期格式化元素中的大写字母

拼写出来的单词、缩写词或罗马数字中的大写字母在相应的格式元素中也跟着大写。例如，日期格式元素 DAY 产生的 MONDAY 也大写，Day 和 Monday 格式一样，day 和 monday 格式一样。

obclient> SELECT TO_CHAR(sysdate,'mon')  AS nowMonth FROM DUAL;
+----------+
| NOWMONTH |
+----------+
| mar      |
+----------+
1 row in set (0.00 sec)

obclient> SELECT TO_CHAR(sysdate,'MON')  AS nowMonth FROM DUAL;
+----------+
| NOWMONTH |
+----------+
| MAR      |
+----------+
1 row in set (0.00 sec)

日期时间格式化中的标点符号和字符字面量

以下字符需要日期格式化，这些字符出现在返回值中的位置与格式化中字符的位置相同：

标点符号，例如连字符，斜杠，逗号，句号和冒号。
字符字面量，用双引号引起来。

OceanBase 数据库可以灵活的将字符串转换为日期。当您使用 TO_DATE 函数时，若输入字符串中的每个数字元素都包含格式化允许的最大位数，则格式字符串将与输入的字符串匹配。

示例 1：格式元素 MM/YY，其中 02 对应 MM，07 对应 YY。

执行以下语句：

SELECT TO_CHAR(TO_DATE('0207','MM/YY'),'MM/YY') FROM DUAL;

查询结果如下：

+------------------------------------------+
| TO_CHAR(TO_DATE('0207','MM/YY'),'MM/YY') |
+------------------------------------------+
| 02/07                                    |
+------------------------------------------+

示例 2：OceanBase 数据库允许非字母数字字符与格式化中的标点字符匹配，# 对应 /。

执行以下语句：

 SELECT TO_CHAR (TO_DATE('02#07','MM/YY'), 'MM/YY') FROM DUAL;

查询结果如下：

+-------------------------------------------+
| TO_CHAR(TO_DATE('02#07','MM/YY'),'MM/YY') |
+-------------------------------------------+
| 02/07                                     |
+-------------------------------------------+

日期格式化元素与全球化支持

在 OceanBase 数据库中，日期时间格式化元素的语言可以通过参数 NLS_DATE_LANGUAGE 和 NLS_LANGUAGE 指定。默认值是 AMERICAN，不支持修改，所以不支持全球化。

示例：日期时间格式化的语言参数默认是 American，不支持其他语言。

SELECT TO_CHAR (SYSDATE, 'DD/MON/YYYY', 'nls_date_language=''Traditional Chinese'' ') FROM DUAL;

查询结果报错，语言参数不支持。

ORA-12702: invalid NLS parameter string used in SQL function