Parse a unix/epoch value as a `TIMESTAMP` in PostgreSQL

Question

PostgreSQL's COPY command is useful to quickly import lots of data, and the data must be in the textual representation for the datatype.

I am importing lots of data, includes a timestamp column, but it's stored as "unix time", i.e. seconds since the epoch. I can convert it to ISO 8601 (e.g. 2010-01-01 00:00:00, and PostgreSQL accept that for a timestamp. It does not accept the raw epoch value integer.

Is it possible to get postgres to accept the epoch integer value and interpret/convert that to a timestamp? That would make my code simpiler (and maybe faster).

This works with psql:

create temporary table test1 ( v1 timestamp );
copy test1 from stdin ;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself, or an EOF     signal.
>> 2010-01-01 00:00:00
>> \.
COPY 1

But these don't:

copy test1 from stdin ;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself, or an EOF signal.
>> 946684800
>> \.
ERROR:  date/time field value out of range: "946684800"
HINT:  Perhaps you need a different "datestyle" setting.
CONTEXT:  COPY test1, line 1, column v1: "946684800"
copy test1 from stdin ;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself, or an EOF signal.
>> epoch + 946684800
>> \.
ERROR:  time zone displacement out of range: "epoch + 946684800"
CONTEXT:  COPY test1, line 1, column v1: "epoch + 946684800"

The to_timestamp function is able to accept an 'integer' or the string text of that integer. I can't see an option like this for DateStyle setting. I have tried with the column being a timestamp with time zone type, with the same results.

I'm currently runing PostgreSQL 10 on Ubuntu Linux, but upgrading to 11 (or 12 soon) is possible if needed.

Answer 1

There is no way to do that with COPY alone. You either have to pre-process or post-process the data.

Perhaps UNLOGGED tables can make this a little bit faster:

/* no WAL logging for better performance */
CREATE UNLOGGED TABLE test1 (v1 double precision);

COPY test1 FROM STDIN;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself, or an EOF signal.
>> 946684800
>> \.

/* this will rewrite the table */
ALTER TABLE test1
   ALTER v1 TYPE timestamp without time zone
      USING (to_timestamp(v1) AT TIME ZONE 'UTC');

/* this will dump the table to the transaction log */
ALTER TABLE test1 SET LOGGED;

This will allow you to keep the intermediate manipulations out of the transaction log, which is good for performance.