Using COPY INTO with a CSV file containing a Timestamp column

Question

I'm trying to import a CSV file using COPY INTO syntax. I have the below table

test(id int, name varchar(50), expi TIMESTAMP)

Here is an extract of my CSV file:

id,name,expi
1,toto,2013-01-14T23:12:34

I did not succeed in providing the format for the timestamp column. What should I use to replace the ??? in the command below?

COPY 10 OFFSET 1 RECORDS INTO test FROM '/tmp/file.csv'(id,name,???) ON CLIENT DELIMITERS ',','\r\n','"';

I tried (id, name, expi '%Y-%m-%dT%H:%M:%S') to follow the example found in the documentation on CSV Bulk Upload but I got the following error message:

COPY INTO: 'str_to_timestamp' missing for type timestamp

Answer 1

Thanks for asking. We've fixed this some time ago, but forgot to push the changeset to the MonetDB Mercurial repository. Thanks to your question, we noticed this oversight. So, the 'str_to_timestamp' missing error is fixed here: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=7fb446b3c98c. It'll be included in the next bugfix release.

However, I needed to make some changes to your above queries.

1. you also need to add WITH TIME ZONE into CREATE TABLE statement, because strftime (the underlying library function) gives us the time in UTC, and a (SQL) TIMESTAMP is local time.

2. the OFFSET should be 2

So, the following query works on Linux (note the '\n'):

create table test(id int, name varchar(50), expi TIMESTAMP WITH TIME ZONE);
COPY 10 OFFSET 2 RECORDS INTO test FROM '/tmp/file.csv'(id,name,expi '%Y-%m-%dT%H:%M:%S') ON CLIENT DELIMITERS ',','\n','"';