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(dot)klaver(at)aklaver(dot)com