文档首页> 云数据库 RDS> 常见问题> 数据库基本使用> RDS for PostgreSQL增强版时间数据类型
更新时间:2022-04-27 GMT+08:00
分享

RDS for PostgreSQL增强版时间数据类型

GUC参数“NLS_TIMESTAMP_FORMAT”定义了数据类型(Date、Timestamp)的IO格式和函数(to_timestamp(text)、to_date(text))校验时间字符串格式时所使用的通配符,默认值为“DD-MON-RR HH.MI.SS.FF AM”。使用show NLS_TIMESTAMP_FORMAT可查看参数值。

由于时间数据类型的输入格式会根据NLS_TIMESTAMP_FORMAT参数值进行校验,因此如果业务中使用的时间字符串与参数值格式不一致,例如YYYY-MM-DD HH24:MI:SS (ISO时间格式),则需要调整参数“NLS_TIMESTAMP_FORMAT”进行匹配或者调整业务中的时间字符串进行匹配,否则就会出现时间字符串校验失败报错。

下面举例说明同一命令(时间字符串"2021-03-26 19:10:45")在参数默认值不同的情况下SQL的执行结果不同。

场景一:NLS_TIMESTAMP_FORMAT=DD-MON-RR HH.MI.SS.FF AM

SQL语句中的时间字符串格式与NLS_TIMESTAMP_FORMAT的默认值格式不一致,因此下列语句执行失败。

  • 将时间字符串显式转换为时间数据类型,执行结果失败
    rds_pg=# select cast('2021-03-26 19:10:45' as timestamp) from dual;
    ERROR:  invalid value "03-" for "MON"
    LINE 1: select cast('2021-03-26 19:10:45' as timestamp) from dual;                
    DETAIL:  The given value did not match any of the allowed values for this field.
  • 将时间字符串通过格式化函数转换为时间数据类型,执行结果失败
    rds_pg=# select to_timestamp('2021-03-26 19:10:45') from dual;
    ERROR:  invalid value "03-" for "MON"
    DETAIL:  The given value did not match any of the allowed values for this field.
    CONTEXT:  SQL function "to_timestamp" statement 1
  • 将时间字符串插入到字段为时间数据类型的表中,执行结果失败
    rds_pg=# create table test_timestamp_io(order_date timestamp);
    CREATE TABLE
    rds_pg=# insert into test_timestamp_io(order_date) values('2021-03-26 19:10:45');
    ERROR:  invalid value "03-" for "MON"
    LINE 1: insert into test_timestamp_io(order_date) values('2021-03-26...
  • 将时间字符串通过隐式转换作为入参调用函数,执行结果失败
    rds_pg=# select timestamp_pl_interval('2021-03-26 19:10:45', '1 day') from dual;
    ERROR:  invalid value "03-" for "MON"
    LINE 1: select timestamp_pl_interval('2021-03-26 19:10:45', '1 day')...
    DETAIL:  The given value did not match any of the allowed values for this field.
  • 时间数据类型的输出格式为DD-MON-RR HH.MI.SS.FF AM
    rds_pg=# select current_date from dual;
    localtimestamp
    ------------------------------
    15-MAR-21 11.33.42.169502 AM
    (1 row)
    rds_pg=# select to_timestamp('2021-03-26 19:10:45', 'YYYY-MM-DD HH24:MI:SS') from dual;
    to_timestamp_without_tz
    ------------------------------
    26-MAR-21 07.10.45.000000 PM
    (1 row)

场景二:NLS_TIMESTAMP_FORMAT=YYYY-MM-DD HH24:MI:SS

SQL语句中的时间字符串格式与NLS_TIMESTAMP_FORMAT的默认值格式一致,下列语句执行成功。

  • 将时间字符串显式转换为时间数据类型,执行成功
    rds_pg=# select cast('2021-03-26 19:10:45' as timestamp) from dual;
    timestamp
    ---------------------
    2021-03-26 19:10:45
    (1 row)
  • 将时间字符串通过格式化函数转换为时间数据类型,执行成功
    rds_pg=# select to_timestamp('2021-03-26 19:10:45') from dual;
    to_timestamp
    ---------------------
    2021-03-26 19:10:45
    (1 row)
  • 将时间字符串插入到字段为时间数据类型的表中,执行成功
    rds_pg=# create table test_timestamp_io(order_date timestamp);
    CREATE TABLE
    rds_pg=# insert into test_timestamp_io(order_date) values('2021-03-26 19:10:45');
    INSERT 0 1
  • 将时间字符串通过隐式转换作为入参调用函数,执行成功
    rds_pg=# select timestamp_pl_interval('2021-03-26 19:10:45', '1 day') from dual;
    timestamp_pl_interval
    -----------------------
    2021-03-27 19:10:45
    (1 row)
  • 时间数据类型的输出格式为YYYY-MM-DD HH24:MI:SS
    rds_pg=# select current_date from dual;
    localtimestamp
    ---------------------
    2021-03-15 11:41:12
    (1 row)
    rds_pg=# select to_timestamp('2021-03-26 19:10:45', 'YYYY-MM-DD HH24:MI:SS') from dual;
    to_timestamp_without_tz
    -------------------------
    2021-03-26 19:10:45
    (1 row)

参数修改

根据参数生效的作用域不同,支持通过实例级或会话级修改参数。实例级修改对当前整个实例生效,会话级修改则只对当前创建连接的session会话生效。

在RDS for PostgreSQL增强版中,参数NLS_TIMESTAMP_FORMAT与NLS_DATE_FORMAT作用域相同,对于数据类型Date和Timestamp的IO格式,建议统一使用NLS_TIMESTAMP_FORMAT进行调整。

实例级:通过管理控制台修改

  1. 登录云数据库RDS控制台
  2. 单击管理控制台左上角的,选择区域和项目。
  3. “实例管理”页面,选择指定的实例,单击实例名称。
  4. 在左侧导航栏中选择“参数修改”,在“参数”页签修改NLS_TIMESTAMP_FORMAT的参数值。

    • 单击“保存”,在弹出框中单击“确定”,保存修改。
    • 单击“取消”,放弃本次设置。
    • 单击“预览”,可对比参数修改前和修改后的值。

会话级:通过SQL语句修改

  1. 通过客户端连接数据库,详情参见通过psql连接源数据库
  2. 执行如下sql,修改参数默认值。

    ALTER SESSION SET NLS_TIMESTAMP_FORMAT = '***';

分享:

数据库基本使用所有常见问题

more

close