Bulk Load Timestamp Formatting

Question

We have a database that produces this timestamp:

YYYY-MM-DD HH24:MI:SS

In order to load the csvs with this timestamp, is there a way to reliably do this? I have tried a number of formatting options hoping it just adds .00.00 to it and meets the criteria but no luck. Am I missing something?

I am expecting something like this maybe? As part of the copy into statement or file format?

TIMESTAMP_FORMAT = (TYPE='CSV' TIMESTAMP_FORMAT='YYYY-MM-DD HH24:MI:SS America/Los_Angeles')

Table in question:

In the table in question it is like this:

 CREATE OR REPLACE TABLE UsageValues (
     UsageTypeName VARCHAR(40000)  NULL, 
     UsageTotal NUMBER(38,0)  NULL, 
     etl_uuid VARCHAR(40000)  NULL, 
     etl_deviceServer VARCHAR(40000)  NULL, 
     etl_timestamp TIMESTAMP  NULL
);

The target column is already a timestamp. I altered it to meet the different req still get

'Timestamp '2020-04-09 23:01:07' is not recognized'

This is the session parameter as it is right now

TIMESTAMP_INPUT_FORMAT YYYY-MM-DD HH24:MI:SS AUTO SESSION  

Answer 1

I tried to reproduce the issue, created a simple file to load. Here's the content:

2020-02-20 23,Gokhan
2010-02-03 11,Test

I created a stage and loaded the file into the stage:

CREATE STAGE mystage;
put file://test.csv @mystage;

I created a table and run the copy command:

create table load_test (t1 timestamp, v1 varchar );
copy into LOAD_TEST from @mystage;

Because the type of the target column is timestamp, it's automatically parsed and inserted. Here's the result:

select * from LOAD_TEST;
+-------------------------+--------+                                            
| T1                      | V1     |
|-------------------------+--------|
| 2020-02-20 23:00:00.000 | Gokhan |
| 2010-02-03 11:00:00.000 | Test   |
+-------------------------+--------+