Redshift COPY not parsing date correctly, but INSERT works

user_9472066 0
Viewed 133

I have a table in AWS Redshift with TIMESTAMP column. When I try to INSERT the value like 2/1/2022 12:00:00 AM, it works well. When I try to COPY it, the value is 2022-02-01 00:00:00.000000 +00:00, so the date part works ok, but the hour is always zero.

To reproduce, the table is:

CREATE TABLE store_sales (
  "datetime" TIMESTAMP,
  "store" VARCHAR(32),
  "count" INTEGER,
  "value" INTEGER
);

Example CSV file that I'm copying data from:

date,store,count,value
2/1/2022 12:00:00 AM,Store 1,4,2
2/1/2022 12:00:00 AM,Test Store,2-,3
2/1/2022 12:00:00 AM,MyStore,3,5

My COPY options (I'm using Python and Apache Airflow for running the actual SQL to copy from S3 to Redshift):

copy_options = [
    "REGION 'eu-central-1'",
    "CSV",
    "DELIMITER ','",
    "IGNOREHEADER 1",
    "TIMEFORMAT AS 'MM/DD/YYYY HH12:MI:SS AM'",
    "TRIMBLANKS",
]

The important bit is probably the TIMEFORMAT. I tried using 'auto', but it gave the same result.

amazon-redshift python
1 Answers

12:00:00 AM is the same as 00:00:00 in a timestamp, so if your hour is coming back as zero, that's expected. If your time was 12:00:00 PM, that would be 12:00:00 on a 24hr timestamp.

Looks like everything's working as expected here. If you disagree, please add more info to the question.

user_182997500
Related