"Invalid DATE/TIME in type conversion" when trying to load the parquet

Help Connectors and Pipelines
1

When I am trying to load the parquet data into my table, I am getting error as “Query 1 ERROR at Line 17: : Invalid DATE/TIME in type conversion for column ‘event_time’”

Following is my query:

LOAD DATA S3 's3://bucket-name/files/'
CONFIG '{"region" : "{region}"}' 
CREDENTIALS '{"aws_access_key_id" : "{aws_access_key_id}", "aws_secret_access_key": "{aws_secret_access_key}"}' 
INTO TABLE example_table
    (id <- id,
    event_time <- event_time,
    category <- category,
    data <- data)
  FORMAT PARQUET;

When I checked the format of the event_time using the duckdb, it looks correct like following:
“2024-12-02 13:24:52”

Not sure what is wrong here

2

Hi Bhaskar,

Please follow the examples in the docs for loading date time

and share if it helped resolve your issue

3

This didnt work.

I am getting following error:

Query 2 ERROR at Line 7: : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘INTERVAL @ts MICROSECOND)’ at line 1

LOAD DATA S3 's3://bucket-name/files/'
CONFIG '{"region" : "{region}"}' 
CREDENTIALS '{"aws_access_key_id" : "{aws_access_key_id}", "aws_secret_access_key": "{aws_secret_access_key}"}' 
INTO TABLE example_table
    (id <- id,
    @ts <- event_time,
    category <- category,
    data <- data)
  FORMAT PARQUET
SET event_time = TIMESTAMPADDDATE_ADD(MICROSECOND, @ts/1000'1970-01-01', from_unixtime(0));
                                INTERVAL @ts MICROSECOND);

This was the query.

Following is the format of data in parquet :
2024-12-05T12:34:56.123Z
2024-12-05T15:45:12.987Z
etc

Schema says optional int64 event_time (TIMESTAMP(MILLIS,true)); in parquet file

4

Ok so following worked if it helps anybody:

LOAD DATA S3 's3://bucket-name/files/filename.parquet'
CONFIG '{"region" : "{region}"}' 
CREDENTIALS '{"aws_access_key_id" : "{aws_access_key_id}", "aws_secret_access_key": "{aws_secret_access_key}"}' 
INTO TABLE example_table
    (id <- id,
    @ts <- event_time,
    category <- category,
    data <- data)
  FORMAT PARQUET
SET event_time = DATE_ADD('1970-01-01 00:00:00', INTERVAL @ts * 1000 MICROSECOND);
5

Yes, you are correct. Using the DATE_ADD function as you’ve shown above is the best approach.

We’ve updated the docs to replace TIMESTAMPADD with DATE_ADD (and remove the version that inadvertently mixed TIMESTAMPADD and DATE_ADD).

(As a nit, and for future readers, I do believe there is an extra = before DATE_ADD in the query above.)

6

Thanks updated that.