首页 » PostgreSQL/GaussDB » 如何处理PostgreSQL中timestamp列写入字符串以”Z”结尾的值？

如何处理PostgreSQL中timestamp列写入字符串以”Z”结尾的值？

最近在做从Mogdb(openGauss)导入Highgo(PostgreSQL)的PG模式数据时，timestamp类型的数据库入库错误，使用的是MTK逻辑查询是insert的方式，错误内容如下：

anbob=# insert into test9(t) values ('20240101 13:01:01Z');
ERROR: datetime format picture ends before converting entire input string
LINE 1: insert into test9(t) values ('20240101 13:01:01Z');

CharGPT 答复

The error you’re encountering happens because the format of the datetime string you’re trying to insert does not match the expected format for the timestamp or timestamptz column in PostgreSQL.
By default, PostgreSQL expects timestamps to be in a standard format like ‘YYYY-MM-DD HH:MI:SS’ for timestamp or ‘YYYY-MM-DD HH:MI:SS+TZ’ for timestamptz.
In your case, you are trying to insert a string with the format ‘YYYYMMDD HH:MI:SSZ’, where the Z indicates UTC, but PostgreSQL may not automatically recognize this format.

下面记录一下，如何导入这样的值。
— env Highgo DB v9.0

anbob=# create table test9(t timestamp(0),t1 timestamp(6), t2 timestamp with  time zone,t3 timestamp,t4 timestamp without time zone);
CREATE TABLE
anbob=# \d test9
                         Table "public.test9"
 Column |            Type             | Collation | Nullable | Default
--------+-----------------------------+-----------+----------+---------
 t      | timestamp(0)                |           |          |
 t1     | timestamp(6)                |           |          |
 t2     | timestamp(6) with time zone |           |          |
 t3     | timestamp(6)                |           |          |
 t4     | timestamp(6)                |           |          |


anbob=# insert into test9(t) values ('2024-01-01 13:01:01Z');
ERROR:  datetime format picture ends before converting entire input string
LINE 1: insert into test9(t) values ('2024-01-01 13:01:01Z');
                                     ^
anbob=# insert into test9(t) values ('2024-01-01 13:01:01');         --------- 1
INSERT 0 1


anbob=# insert into test9(t1) values ('2024-01-01 13:01:01Z');
ERROR:  datetime format picture ends before converting entire input string
LINE 1: insert into test9(t1) values ('2024-01-01 13:01:01Z');
                                      ^
anbob=# insert into test9(t2) values ('2024-01-01 13:01:01Z');
ERROR:  invalid value "Z" for "TZH"
LINE 1: insert into test9(t2) values ('2024-01-01 13:01:01Z');
                                      ^
DETAIL:  Value must be an integer.
anbob=# insert into test9(t3) values ('2024-01-01 13:01:01Z');
ERROR:  datetime format picture ends before converting entire input string
LINE 1: insert into test9(t3) values ('2024-01-01 13:01:01Z');
                                      ^
anbob=# insert into test9(t4) values ('2024-01-01 13:01:01Z');    
ERROR:  datetime format picture ends before converting entire input string
LINE 1: insert into test9(t4) values ('2024-01-01 13:01:01Z');

检查timestamp format参数

anbob=# \! sh show.sh timestamp
 nls_timestamp_format                   | YYYY-MM-DD HH24:MI:| Compatible Oracle NLS parameter for timestamp type.
 nls_timestamp_tz_format                | YYYY-MM-DD HH24:MI:| Compatible Oracle NLS parameter for timestamp with time zone type.
 track_commit_timestamp                 | off                | Collects transaction commit time.
anbob=# show nls_timestamp_tz_format;
      nls_timestamp_tz_format
-----------------------------------
 YYYY-MM-DD HH24:MI:SS.FF6 TZH:TZM
(1 row)

Note:
主要有两个参数nls_timestamp_format 和nls_timestamp_tz_format

尝试修改参数 –错误

$ psql
psql (14.10)
IvorySQL-pro-3.2.0
Type "help" for help.

highgo=# alter system set nls_timestamp_tz_format='YYYY-MM-DD HH24:MI:SSZ';
ALTER SYSTEM
highgo=# select pg_reload_conf();
 pg_reload_conf
----------------
 t
(1 row)

$ psql anbob system -p 1521
psql (14.10)
IvorySQL-pro-3.2.0
Type "help" for help.

anbob=# insert into test9(t4) values ('2024-01-01 13:01:01Z');
ERROR:  datetime format picture ends before converting entire input string
LINE 1: insert into test9(t4) values ('2024-01-01 13:01:01Z');
                                      ^
anbob=# insert into test9(t3) values ('2024-01-01 13:01:01Z');
ERROR:  datetime format picture ends before converting entire input string
LINE 1: insert into test9(t3) values ('2024-01-01 13:01:01Z');
                                      ^
anbob=# insert into test9(t2) values ('2024-01-01 13:01:01Z');
ERROR:  date format not recognized
LINE 1: insert into test9(t2) values ('2024-01-01 13:01:01Z');

anbob=# insert into test9(t2) values (timestamp'2024-01-01 13:01:01Z');   ---2 
INSERT 0 1
anbob=# insert into test9(t2) values (timestamptz'2024-01-01 13:01:01Z');  ----3 
INSERT 0 1

anbob=# select * from test9;
ERROR:  date format not recognized
anbob=# show nls_timestamp_tz_format
anbob-# ;
 nls_timestamp_tz_format
-------------------------
 YYYY-MM-DD HH24:MI:SSZ
(1 row)

Note:
强置类型转换是可以的，如果xxxformat参数修改值错误不会提示，但查询时报错。“Z”实际值是个字符。

再次修改参数

highgo=# alter system set nls_timestamp_tz_format='YYYY-MM-DD HH24:MI:SS"Z"';
ALTER SYSTEM
highgo=# select pg_reload_conf();
 pg_reload_conf
----------------
 t
(1 row)

Note:
值在有效变量格式后增加Z字符，用双引号保住。

查看之前的值

anbob=# select * from test9;
             t              | t1 |          t2          | t3 | t4
----------------------------+----+----------------------+----+----
 2024-01-01 13:01:01.000000 |    |                      |    |
                            |    | 2024-01-01 21:01:01Z |    |
                            |    | 2024-01-01 21:01:01Z |    |
(3 rows)


anbob=# insert into test9(t2) values ('2024-01-01 13:01:01Z');   --- 4
INSERT 0 1

anbob=# select * from test9;
             t              | t1 |          t2          | t3 | t4
----------------------------+----+----------------------+----+----
 2024-01-01 13:01:01.000000 |    |                      |    |
                            |    | 2024-01-01 21:01:01Z |    |
                            |    | 2024-01-01 21:01:01Z |    |
                            |    | 2024-01-01 13:01:01Z |    |
(4 rows)


anbob=# \! sh show.sh timezone
 log_timezone                           | Asia/Shanghai      | Sets the time zone to use in log messages.
 timezone_abbreviations                 | Default            | Selects a file of time zone abbreviations.

Note:
显示对于timestamp with time zone的t2列增加了Z的显示，并且我们时间+8， 此时对于timestamp with time zone的字符值以Z结尾就可以正常导入。 对于timestamp 同样的方法。

anbob=# show nls_timestamp_tz_format;
 nls_timestamp_tz_format
--------------------------
 YYYY-MM-DD HH24:MI:SS"Z"
(1 row)

anbob=# insert into test9(t2) values ('2024-01-01 13:01:01');   -- 5
INSERT 0 1
anbob=# insert into test9(t1) values ('2024-01-01 13:01:01');   -- 6 
INSERT 0 1

anbob=# insert into test9(t1) values ('2024-01-01 13:01:01Z');
ERROR:  datetime format picture ends before converting entire input string
LINE 1: insert into test9(t1) values ('2024-01-01 13:01:01Z');

anbob=# show nls_timestamp_format;
   nls_timestamp_format
---------------------------
 YYYY-MM-DD HH24:MI:SS.FF6
(1 row)

--

highgo=# alter system set nls_timestamp_format='YYYY-MM-DD HH24:MI:SS"Z"';
ALTER SYSTEM
highgo=# select pg_reload_conf();
 pg_reload_conf
----------------
 t
(1 row)

anbob=# insert into test9(t1) values ('2024-01-01 13:01:01Z');   -- 7
INSERT 0 1
anbob=# select * from test9;
          t           |          t1          |          t2          | t3 | t4
----------------------+----------------------+----------------------+----+----
 2024-01-01 13:01:01Z |                      |                      |    |
                      |                      | 2024-01-01 21:01:01Z |    |
                      |                      | 2024-01-01 21:01:01Z |    |
                      |                      | 2024-01-01 13:01:01Z |    |
                      |                      | 2024-01-01 13:01:01Z |    |
                      | 2024-01-01 13:01:01Z |                      |    |
                      | 2024-01-01 13:01:01Z |                      |    |
(7 rows)

anbob=# insert into test9(t1) values ('2024-01-01 13:01:01');    --8
INSERT 0 1
anbob=# select * from test9;
          t           |          t1          |          t2          | t3 | t4
----------------------+----------------------+----------------------+----+----
 2024-01-01 13:01:01Z |                      |                      |    |
                      |                      | 2024-01-01 21:01:01Z |    |
                      |                      | 2024-01-01 21:01:01Z |    |
                      |                      | 2024-01-01 13:01:01Z |    |
                      |                      | 2024-01-01 13:01:01Z |    |
                      | 2024-01-01 13:01:01Z |                      |    |
                      | 2024-01-01 13:01:01Z |                      |    |
                      | 2024-01-01 13:01:01Z |                      |    |
(8 rows)

Note:
带”Z”或不带Z都可以insert成功。

恢复参数

highgo=# alter system set nls_timestamp_tz_format='YYYY-MM-DD HH24:MI:SS.FF6 TZH:TZM'
highgo-# ;
ALTER SYSTEM
highgo=# alter system set nls_timestamp_format='YYYY-MM-DD HH24:MI:SS.FF6';
ALTER SYSTEM
highgo=# select pg_reload_conf();
 pg_reload_conf
----------------
 t
(1 row)

Note:
建议session 级修改。

打赏

目前这篇文章还没有评论(Rss)

我要评论

上一篇：

下一篇：