Thread: Tools to convert timestamp data to another time zone in PostgreSQL
Hi,
We have recently modified our application to work with PostgreSQL databases and schemas. We also support Oracle and SQL Server Databases.
Along with adding support for PostgreSQL, we have upgraded our infrastructure such that all environments are configured to use the UTC time zone. Previously, the environments were configured to use the time zone where the database server and application server were installed.
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 interoperability between the database engines we support.
After a bit of searching, we found we can write an SQL similar to the one below to do the conversion:
update client
set create_stamp = (create_stamp at time zone 'America/New_York' at time zone 'UTC')
where client_code = 'HOANA';
This does work correctly. However, we have some limitations in terms using SQL statements like this.
- We would need to identify the timestamp columns that would be affected across many tables and multiple schemas.
- We also store date-only information in timestamp without time zone columns. This was done as a result of migrating our application from Oracle where the DATE data type was used at the time (Oracle now supports timestamp columns).
I was wondering if you are aware of any open source and/or commercial tools that could allow us to easily identify the affected columns, exclude columns if necessary, and apply the necessary conversion. If not, we would have to write a utility that does this for us, which could be a lengthy process.
Thanks,
Joel
On 6/13/22 2:52 PM, 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.
>
> Along with adding support for PostgreSQL, we have upgraded our
> infrastructure such that all environments are configured to use the UTC
> time zone. Previously, the environments were configured to use the time
> zone where the database server and application server were installed.
>
> 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
> interoperability between the database engines we support.
>
> After a bit of searching, we found we can write an SQL similar to the
> one below to do the conversion:
>
> update client
>
> set create_stamp = (create_stamp at time zone 'America/New_York' at
> time zone 'UTC')
>
> where client_code = 'HOANA';
>
> This does work correctly. However, we have some limitations in terms
> using SQL statements like this.
>
> - We would need to identify the timestamp columns that would be affected
> across many tables and multiple schemas.
select table_schema, table_name, column_name from
information_schema.columns where data_type = 'timestamp without time zone';
>
> - We also store date-only information in timestamp without time zone
> columns. This was done as a result of migrating our application from
> Oracle where the DATE data type was used at the time (Oracle now
> supports timestamp columns).
A date stored in a timestamp field is going to be a timestamp at midnight:
timestamp_test
Table "public.timestamp_test"
Column | Type | Collation | Nullable | Default
--------+-----------------------------+-----------+----------+---------
ts | timestamp without time zone | | |
tsz | timestamp with time zone |
insert into timestamp_test values (current_date, current_date);
ts | tsz
-------------------------+----------------------------
2022-06-13 00:00:00 | 2022-06-13 00:00:00-07
>
> I was wondering if you are aware of any open source and/or commercial
> tools that could allow us to easily identify the affected columns,
> exclude columns if necessary, and apply the necessary conversion. If
> not, we would have to write a utility that does this for us, which could
> be a lengthy process.
>
> Thanks,
>
> Joel
>
--
Adrian Klaver
adrian.klaver@aklaver.com
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'; > > >