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: 
`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`](https://www.postgresql.org/docs/current/static/functions-datetime.html) 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](https://www.postgresql.org/docs/current/static/sql-altertable.html).

```
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';

```
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/200207251134.16658.dev@archonet.com>