sqlpostgresqltimezoneddlalter-table

Change column type from timestamp WITHOUT time zone to timestamp WITH time zone

I found this ALTER COLUMN statement in the PostgreSQL 9.3 ALTER TABLE manual page:

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

I cannot seem to get this to work. I'm getting this error:

ERROR: operator does not exist: timestamp without time zone * interval
SQL state: 42883
Hint: No operator matches the given name and argument type(s).
You might need to add explicit type casts.

The ALTER TABLE statement looks very straight-foward. But I've tried all kinds of casts and converting column-types, but can not get this to work. What am I missing?

Solution

  • The example in the Postgres manual (as well as the working fiddle by mvp) transform an integer column (representing a UNIX epoch) to timestamptz.

    The error message, as well as your title, clearly indicate you are trying to convert a timestamp to timestamptz. This just works automatically, without explicit cast.

    ALTER TABLE test ALTER created_at TYPE timestamptz;

    fiddle
    Old sqlfiddle

    About timestamp vs. timestamptz:

    timestamp values are always interpreted according to the time zone setting of your session. To assume the time zone UTC for the conversion:

    BEGIN;
SET LOCAL timezone='UTC';
ALTER TABLE test ALTER created_at TYPE timestamptz;
COMMIT;

    Or use the AT TIME ZONE construct:

    ALTER TABLE test ALTER created_at TYPE timestamptz
USING created_at AT TIME ZONE 'UTC';

    You can assume any time zone this way. But consider this for Postgres 12 or newer!