How do I convert timestamp and offset columns to a single timestamptz column?

Question

Image I have a table containing the following two columns:

• timestampwithouttimezone (of type TIMESTAMP WITHOUT TIME ZONE)
• utcoffset (of type INTEGER)

I want to convert those two column to a single one of type TIMESTAMP WITH TIME ZONE. Can this be achieved using a ALTER TABLE ALTER COLUMN [column] SET DATE TYPE TIMESTAMP WITH TIME ZONE query and the additional USING clause?

Or do I need a separate UPDATE query that takes the offset and sets the timezone of the timestamps? If that's the case, what would that query be? I can't find any examples that show how to update the timezone using an integer.

Answer 1

You could do that like this, assuming the offset is in hours:

ALTER TABLE mytab
   ALTER timestampwithouttimezone
      TYPE timestamp with time zone
      USING CAST (timestampwithouttimezone::text || ' '
                     || to_char(utcoffset, 'S00FM')
                  AS timestamp with time zone),
   DROP utcoffset;