0

My issue is that my oracle db has timestamp(6) column (without any timestamp information) but the timestamps are stored in CET/CEST (Europe/Berlin).

We need to correct the timestamps to UTC (because we already have issues when time switches from CEST to CET - e.g 28.10.2018)

I already found the following how it could be done via update script: How to update a TIMESTAMP column to TIMESTAMP WITH TIME ZONE in Oracle

As we are also planning to migrate from 11 to 12 - my question is if it could also be done during the dump exp / imp (expdp / impdp)?

max
  • 1,134
  • 10
  • 16

1 Answers1

2

The correct conversion would be this one:

 FROM_TZ(YOUR_COLUMN, 'Europe/Berlin')  AT TIME ZONE 'UTC'

this returns a TIMESTAMP WITH TIME ZONE value. If you prefer to get a TIMESTAMP then you can use

 SYS_EXTRACT_UTC(YOUR_COLUMN, 'Europe/Berlin')

However, I don't know how to include this in import/export. I assume you have to use REMAP_DATA:

CREATE OR REPLACE PACKAGE Convert_Date AS       
    FUNCTION TO_UTC(ts IN TIMESTAMP) RETURN TIMESTAMP;
END Convert_Date;
/


CREATE OR REPLACE PACKAGE BODY Convert_Date AS

FUNCTION TO_UTC(ts IN TIMESTAMP) RETURN TIMESTAMP
BEGIN
    RETURN SYS_EXTRACT_UTC(ts, 'Europe/Berlin');    
END;

END Convert_Date;
/

And then use this import parameter:

REMAP_DATA=[schema.]tablename.column_name:Convert_Date.TO_UTC
Wernfried Domscheit
  • 54,457
  • 9
  • 76
  • 110