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

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