CODEWITHSUNDEEP
mysqlpostgresql
Broak
Broak

Reputation: 4187

Alter column type from int or bigint to timestamp

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.

Upvotes: 3

Views: 5588

Answers (2)

Evan Carroll
Evan Carroll

Reputation: 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';

Upvotes: 5

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522109

I don't believe there is a direct cast from bigint to timestamp in Postgres. But you can create a new timestamp column and populate it with your bigint column, going through text.

ALTER TABLE groupgps ADD COLUMN time_stamp timestamp with time zone;
UPDATE groupgps
SET time_stamp = '1970-01-01 00:00:00 GMT'::timestamp +
                 ((date_stamp)::text)::interval;

Reference: https://www.postgresql.org/message-id/[email protected]

Upvotes: 0

Related Questions