CODEWITHSUNDEEP
postgresqltimestamptimestamp-with-timezone
MrBolton
MrBolton

Reputation: 47

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

Image I have a table containing the following two columns:

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.

Upvotes: 0

Views: 113

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247280

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;

Upvotes: 1

Related Questions