Alter column type from int or bigint to timestamp

Question

I currently have a bigint with timestamps in millis, instead i want to change to the standard "timestamp" column type.

Using:

ALTER TABLE groupgps ALTER COLUMN date_stamp TYPE timestamp

I get:

column "date_stamp" cannot be cast automatically to type timestamp without time zone

Using:

ALTER TABLE groupgps ALTER COLUMN date_stamp TYPE timestamp with time zone USING date_stamp::timestamp with time zone

I get:

cannot cast type bigint to timestamp with time zone

Really at a loss here beyond re-making the table from scratch, but i believe i'd need to recreate all the indexes and anything that referenced the table before i deleted it.

Answer 1

ALTER ... USING statement.

Test data, from your sample.

CREATE TABLE groupgps AS
SELECT date_stamp::bigint
FROM generate_series(1,100) AS date_stamp;

With to_timestamp()

It seems you can also use the to_timestamp function. The docs pointing to to_timestamp claim that this,

SELECT TIMESTAMP WITH TIME ZONE 'epoch' + 982384720.12 * INTERVAL '1 second';

The to_timestamp function encapsulates the above conversion.

So we can use that in our ALTER TABLE ... USING too,

ALTER TABLE groupgps
    ALTER COLUMN date_stamp SET DATA TYPE timestamp with time zone
    USING to_timestamp(date_stamp);

Without using to_timestamp()

Then we adapt the example from the docs.

ALTER TABLE groupgps
    ALTER COLUMN date_stamp SET DATA TYPE timestamp with time zone
    USING
        timestamp with time zone 'epoch' + date_stamp * interval '1 second';