Re: Tools to convert timestamp data to another time zone in PostgreSQL

On Mon, Jun 13, 2022 at 09:52:00PM +0000, Joel Rabinovitch wrote:
> Hi,
>
>
>
> We have recently modified our application to work with PostgreSQL
> databases and schemas. We also support Oracle and SQL Server Databases.
[skipped]

>
> As a result, we have hit an issue where we need to convert data in
> timestamp columns in existing records to reflect that the time is in UTC.
> The timezone is not specified in our timestamp columns (i.e. they are
> defined as timezone without time zone). We need to do this for

btw, it's not specified in timestamptz either.
timestamptz always stores time in UTC microseconds, and displays
it in timezone according to the session settings.

> interoperability between the database engines we support.

It's better to use timestamptz type

https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use_timestamp_.28without_time_zone.29

and I think it's a good time to do that change.

[skipped]

Setting timezone in session to 'America/New_York' and converting
column to timestamptz should do it fine

set timezone = 'America/New_York';
ALTER TABLE <tablename> ALTER COLUMN create_stamp TYPE timestamptz;

should do it just fine (on a reasonably sized tables).
Other possibilities, like creating a new column and renaming it
after the proper feel in, are possible.

>
> set create_stamp = (create_stamp at time zone 'America/New_York' at
> time zone 'UTC')

>
> where client_code = 'HOANA';
>
>
>