Postgres epoch date as varchar

Question

So I've been given a lovely little database. One of the tables in the database (several million rows large) has this column:

time_in character varying(255)

Stored in there is an epoch timestamp. What is the most sane way I can convert this to a proper epoch timestamp column without losing data?

score 1 · Answer 1 · answered Mar 19 '15 at 20:01

First off there is no separate epoch timestamp datatype so the type you want to convert to is just regular timestamp. In the PostgreSQL Documentation - ALTER TABLE there's an example that fits to your case almost perfectly (I just added a cast to integer):

ALTER TABLE foo
    ALTER COLUMN time_in SET DATA TYPE timestamp with time zone
    USING
        timestamp with time zone 'epoch' + time_in::integer * interval '1 second';

Note that the conversion might take some time and will produce an error if all of the rows are not valid epoch times.

score 0 · Answer 2 · edited May 23 '17 at 12:22

Or, quoting the manual here:

A single-argument to_timestamp function is also available; it accepts a double precision argument and converts from Unix epoch (seconds since 1970-01-01 00:00:00+00) to timestamp with time zone. (Integer Unix epochs are implicitly cast to double precision.)

ALTER TABLE foo ALTER COLUMN time_in
  SET DATA TYPE timestamptz USING to_timestamp(time_in::float8);

But first, decide whether timestamp (timestamp without time zone) or timestamptz (timestamp with time zone) is the better choice for you:

Ignoring timezones altogether in Rails and PostgreSQL

Postgres epoch date as varchar

2 Answers2