Bulk upload - transforming UNIX epoch time to Vertica TIMESTAMP column

Well, since no one replied to this one, I had to come up with a solution for this myself! So here it is....

My database table earlier had just 1 column ('hour') of type TIMESTAMP & I was trying to upload a UNIX epoch timestamp data from a file into it. (FYI - UNIX epoch timestamp data looks like an INTEGER).

But I realized Vertica's bulk upload utility does not directly convert UNIX epoch timestamp in the file to Vertica's TIMESTAMP in database. So I created one more column in my table of type INTEGER. The intent being to first upload the UNIX epoch timestamp data an integer to this column & then transform it to TIMESTAMP & load it onto the new column that I created.

So now with this setup, when I fire below statement I realized it works! It basically inserts the UNIX epoch timestamp data from the file as an INTEGER in the column 'hourAsInteger' & then it transforms it on the fly (during upload) to my TIMESTAMP column 'hour'.


COPY SchemaA.TableA ( hourAsInteger,  hour AS TO_TIMESTAMP(hourAsInteger)) FROM '/media/data/hour.csv' ABORT ON ERROR;

Hi Sandeep,
If you dont need the integer time in the table, you can avoid creating the extra column by using fillers. Here's a modified version of your copy statement:

COPY SchemaA.TableA ( hourAsInteger filler (int),  hour AS TO_TIMESTAMP(hourAsInteger)) FROM '/media/data/hour.csv' ABORT ON ERROR;

You can find more details on fillers at https://my.vertica.com/docs/6.1.x/HTML/index.htm#12431.htm.

/Sajan

Thanks Sajan.

I actually figured this out yesterday after posting my response. I went through some Vertica documentation (above) & some external Vertica forums (e.g.: www.vertica-forums.com) where I was able to find some samples that helped me solve this with fillers.

Thanks anyway; appreciate you replying to my post.

Sandeep