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

Reply via email to