11

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?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
dsmorey
  • 453
  • 1
  • 5
  • 16

2 Answers2

18

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!

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • you were right, it was a misunderstanding. I've seen that documentation several times and each time I somehow missed that it was a integer column. Thanks for the help. – dsmorey Dec 27 '13 at 15:37
  • I was able to get the data type changed, is there a way to default to UTC? I tried "using timestamp with time zone 'UTC'" and it's throwing an error that 'UTC' is not supported. – dsmorey Dec 27 '13 at 15:38
  • @dsmorey: That's another misunderstanding I am afraid. :) `timestamp with time zone` stores UTC times in any case. The display depends on your time zone setting. The answer I linked to provides detailed information. – Erwin Brandstetter Dec 27 '13 at 16:32
  • Thanks again for the tips. I guess what's happening now is that when I run the ALTER COLUMN the date is staying the same and taking on the local time zone (EST). So in my current table, the field is timestamp without time zone and since rails stores all dates in utc, the date/times are in UTC. However, when I run the ALTER COLUMN to change the column to a timestamp with time zone, it's assuming my local time for those date/times. I would almost have to do a conversion then when I'm running the ALTER COLUMN to keep the dates in UTC. Interesting. – dsmorey Dec 27 '13 at 20:28
  • @dsmorey: Or you can just set the time zone for the duration of the transaction or the scope of the statement. I added two solutions for that. – Erwin Brandstetter Dec 27 '13 at 20:43
  • yeah, looks like that's the way I'm going to have to go. Was hoping to avoid the addition of that statement each query, but now that I think about it, it's probably easier than changing all the fields we need to. I can't thank you enough for sticking with me here. Thanks – dsmorey Dec 27 '13 at 21:40
  • @ErwinBrandstetter I need to change a `timestamp` column to `timestamptz` on a large table, live db... will this lock the table? Postgres 9.3.9 – Anentropic Sep 09 '15 at 12:58
  • @Anentropic: Certainly. Ask questions as *question* please. Comments are not the place. – Erwin Brandstetter Sep 09 '15 at 13:00
0

It works fine for me: SQLFiddle.

I think what you have is your SQL client caching data types. I had this issue with mine until I restarted it, and type was indeed changed fine.

mvp
  • 111,019
  • 13
  • 122
  • 148