Importing csv into Postgres database with improper date value

Question

I have a query which has a date field with values that look like this in the query results window:

2013-10-01 00:00:00

However, when I save the results to csv, it gets saved like this:

2013-10-01T00:00:00

This is causing a problem when I'm trying to COPY the csv into a table in Redshift, where it gives me an error stating that the value is not a valid timestamp (the field I'm importing to is a timestamp field).

How can I get it so that it either strips out the time component completely, leaving just the date, or at least that the "T" is removed from the results?

I'm exporting results to csv using Aginity SQL Workbench for Redshift.

hd1 · Accepted Answer · 2014-08-16 01:04:15Z

2

According to this knowledgebase article:

After import, add new TIMESTAMP columns and use the CAST() function to populate them:

ALTER TABLE events ADD COLUMN received_at TIMESTAMP DEFAULT NULL;
UPDATE events SET received_at = CAST(received_at_raw as timestamp);

ALTER TABLE events ADD COLUMN generated_at TIMESTAMP DEFAULT NULL; UPDATE events SET generated_at = CAST(generated_at_raw as timestamp); Finally, if you forsee no more imports to this table, the raw VARCHAR timestamp columns may be removed. If you forsee importing more events from S3, do not remove these columns. To remove the columns, run:

ALTER TABLE events DROP COLUMN received_at_raw; ALTER TABLE events
DROP COLUMN generated_at_raw;

Hope that helps...

answered Aug 16, 2014 at 1:04

hd1

34.9k5 gold badges83 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Importing csv into Postgres database with improper date value

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related