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

copyimportoptimizationpostgresqltimestamp

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.

Best Answer

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.

Related Solutions

Postgresql – How to get the current unix timestamp from PostgreSQL

In postgres, timestamp with time zone can be abbreviated as timestamptz, and timestamp without time zone as timestamp. I will use the shorter type names for simplicity.

Getting the Unix timestamp from a postgres timestamptz like now() is simple, as you say, just:

select extract(epoch from now());

That's really all you need to know about getting the absolute time from anything of type timestamptz, including now().

Things only get complicated when you have a timestamp field.

When you put timestamptz data like now() into that field, it will first be converted to a particular timezone (either explicitly with at time zone or by converting to the session timezone) and the timezone information is discarded. It no longer refers to an absolute time. This is why you don't usually want to store timestamps as timestamp and would normally use timestamptz — maybe a film gets released at 6pm on a particular date in every timezone, that's the kind of use case.

If you only ever work in a single time zone you might get away with (mis)using timestamp. Conversion back to timestamptz is clever enough to cope with DST, and the timestamps are assumed, for conversion purposes, to be in the current time zone. Here's an example for GMT/BST:

select '2011-03-27 00:59:00.0+00'::timestamptz::timestamp::timestamptz
     , '2011-03-27 01:00:00.0+00'::timestamptz::timestamp::timestamptz;

/*
|timestamptz           |timestamptz           |
|:---------------------|:---------------------|
|2011-03-27 00:59:00+00|2011-03-27 02:00:00+01|
*/

_DBFiddle

But, note the following confusing behaviour:

set timezone to 0;

values(1, '1970-01-01 00:00:00+00'::timestamp::timestamptz)
    , (2, '1970-01-01 00:00:00+02'::timestamp::timestamptz);

/*
|column1|column2               |
|------:|:---------------------|
|      1|1970-01-01 00:00:00+00|
|      2|1970-01-01 00:00:00+00|
*/

_DBFiddle

This is because:

PostgreSQL never examines the content of a literal string before determining its type, and therefore will treat both […] as timestamp without time zone. To ensure that a literal is treated as timestamp with time zone, give it the correct explicit type…In a literal that has been determined to be timestamp without time zone, PostgreSQL will silently ignore any time zone indication

Mysql – Unix timestamp conversion

MySQL support a FROM_UNIXTIME(...) function :-)

select FROM_UNIXTIME(1433433155)

And the other way around, UNIX_TIMESTAMP

Applying it to your example, assuming timecreated and timemodified are those Unix timestamps:

SELECT
      category
      ,fullname
      ,shortname
      ,idnumber
      ,startdate
      ,visible
      , FROM_UNIXTIME (timecreated)
      , FROM_UNIXTIME (timemodified)
  ,count( my.id ) AS mydata
FROM mdl_mytable
JOIN mycontext ct ON ( ct.my = cat.id )
LEFT JOIN myjoin ra ON ( mycontext = cat.id )
WHERE ct.mynumber =10000000
GROUP BY shortname, fullname, .category
      ,.idnumber
      ,.startdate
      ,.visible
      , FROM_UNIXTIME (timecreated)
      , FROM_UNIXTIME (timemodified)
ORDER BY myorder ASC

Best Answer

Related Solutions

Postgresql – How to get the current unix timestamp from PostgreSQL

Mysql – Unix timestamp conversion

Related Question