copy命令DATE_FORMAT缺少时分秒时，未按格式复制

How to reproduce it (as minimally and precisely as possible):
create table t1(col1 int, col2 date);
New file:vi /tmp/test.csv
1,2013-12-11 16:12:12
2,2010-12-12
3,2013-12-11 13:50:23

copy t1 from '/tmp/test.csv' DELIMITERS ',' DATE_FORMAT 'YY-MM-DD';
select * from t1;

What happened:
There is "hours:mins:seconds", not like the DATE_FORMAT 'YY-MM-DD' I set.
col1 | col2
-----+-----------
1 | 2013-12-11 16:12:12
2 | 2010-12-12 00:00:00
3 | 2013-12-11 13:50:23

What you expected to happen:
Copy from csv file shuold not have the time(hours:mins:seconds), like the DATE_FORMAT just have year-month-day.
col1 | col2
-----+-----------
1 | 2013-12-11
2 | 2010-12-12
3 | 2013-12-11

Hey @zdai96, Welcome to openGauss Community.
All of the projects in openGauss Community are maintained by @opengauss-bot.
That means the developers can comment below every pull request or issue to trigger Bot Commands.
Please follow instructions at https://gitee.com/opengauss/community/blob/master/contributors/command.en.md to find the details.

/assign

this issue is assigned to: @scarbor_fair.

这个原因是数据库默认是A_FORMAT,create table语句在语法解析时，直接将date类型转换成了timestamp，而在到copy的处理逻辑时，为date类型指定DATE_FORMAT设置是不起作用的：在InputFunctionCallForBulkload函数，typioparam参数输入值是TIMESTAMPOID，但是解析出来的格式存储在cstate->date_format.fmt中，因此不会设置date_time_fmt的值，最终输入的数据不会进行指定格式的转换。
文档中已经描述了，针对这种情况，需要将DATE_FORMAT改成TIMESTAMP_FORMAT，参见https://opengauss.org/zh/docs/2.1.0/docs/Developerguide/COPY.html
建议关闭该issue

验收通过，关闭此问题

openGauss=# copy t1 from '/vdb/lys0319/openGauss/data/single_node/test.csv' DELIMITERS ',' DATE_FORMAT 'YY-MM-DD';
COPY 3
openGauss=# select * from t1;
 col1 |        col2         
------+---------------------
    1 | 2013-12-11 16:12:12
    2 | 2010-12-12 00:00:00
    3 | 2013-12-11 13:50:23
(3 rows)

openGauss=# select version();
                                                                       version                                                                        
------------------------------------------------------------------------------------------------------------------------------------------------------
 (openGauss 3.0.0 build 0c8b3453) compiled at 2022-03-19 11:54:49 commit 0 last mr   on x86_64-unknown-linux-gnu, compiled by g++ (GCC) 7.3.0, 64-bit
(1 row)

GVP openGauss / openGauss-server

内容风险标识

评论 (6)

GVPopenGauss / openGauss-server

内容风险标识