How does TIMESTAMP WITH LOCAL TIME ZONE behave when exporting data

oracleoracle-11g-r2

Consider the following table:

CREATE TABLE MY_DATA (
    MY_DATA_ID NUMBER(38,0) PRIMARY KEY,
    THE_DATA VARCHAR(10) NOT NULL,
    DATA_TIMESTAMP TIMESTAMP (6) WITH LOCAL TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL
);

(Note that TIMESTAMP without further qualification defaults to the data type used above.)

What happens if I export this data using Data Pump and then import it into another database that has a different time zone? Will the stored time be adjusted for the new time zone, or will it behave as though the off set was changed without changing the rest of the data?

For example, if

  • The original database is US Central time
  • A row contains 2017-10-25 12:14:38
  • We import the data into a database using US Eastern time

will the time be adjusted to 2017-10-25 12:14:38 -04:00 (as in replacing the offset without changing the date/time) or 2017-10-25 13:14:38 -04:00 (= 2017-10-25 12:14:38 -05:00)?

My apologies for not testing this myself. I don't have access to another database with a different time zone, but I wish to know to inform my choice of data type.

Best Answer

I made a test and exported table MY_DATA as given in your example.

Then I had a look at the dmp-File using a HEX editor. The file contains

<?xml version="1.0"?>
<ROWSET>
    <ROW>
        <STRMTABLE_T>
            <VERS_MAJOR>1</VERS_MAJOR>
            <VERS_MINOR>1 </VERS_MINOR>
            <VERS_DPAPI>3</VERS_DPAPI>
            <ENDIANNESS>2</ENDIANNESS>
            <CHARSET>WE8ISO8859P1</CHARSET>
            <NCHARSET>AL16UTF16</NCHARSET>
            <DBTIMEZONE>+01:00</DBTIMEZONE>
            <FDO>0000006001240F050B0C030C0C0504050D0609070805050505050F05050505050A050505050504050607080823472347081123081141B0470083001F07D00300000000000000000000000000000000000000000000000000000000000000000000000000</FDO>
            <OBJ_NUM>3286546</OBJ_NUM>
            <OWNER_NAME>PMDSYS</OWNER_NAME>
            <NAME>MY_DATA</NAME>
            <PROPERTY>536870912</PROPERTY>
            <COL_LIST>
                <COL_LIST_ITEM>
                    <OBJ_NUM>3286546</OBJ_NUM>
                    <COL_NUM>1</COL_NUM>
                    <INTCOL_NUM>1</INTCOL_NUM>
                    <SEGCOL_NUM>1</SEGCOL_NUM>
                    <COL_SORTKEY>1</COL_SORTKEY>
                    <BASE_INTCOL_NUM>1</BASE_INTCOL_NUM>
                    <BASE_COL_TYPE>0</BASE_COL_TYPE>
                    <PROPERTY>0</PROPERTY>
                    <NAME>MY_DATA_ID</NAME>
                    <TYPE_NUM>2</TYPE_NUM>
                    <LENGTH>22</LENGTH>
                    <PRECISION_NUM>38</PRECISION_NUM>
                    <SCALE>0</SCALE>
                    <NOT_NULL>1</NOT_NULL>
                    <CHARSETID>0</CHARSETID>
                    <CHARSETFORM>0</CHARSETFORM>
                    <CHARLENGTH>0</CHARLENGTH>
                </COL_LIST_ITEM>
                <COL_LIST_ITEM>
                    <OBJ_NUM>3286546</OBJ_NUM>
                    <COL_NUM>2</COL_NUM>
                    <INTCOL_NUM>2</INTCOL_NUM>
                    <SEGCOL_NUM>2</SEGCOL_NUM>
                    <COL_SORTKEY>2</COL_SORTKEY>
                    <BASE_INTCOL_NUM>2</BASE_INTCOL_NUM>
                    <BASE_COL_TYPE>0</BASE_COL_TYPE>
                    <PROPERTY>0</PROPERTY>
                    <NAME>THE_DATA</NAME>
                    <TYPE_NUM>1</TYPE_NUM>
                    <LENGTH>10</LENGTH>
                    <NOT_NULL>1</NOT_NULL>
                    <CHARSETID>31</CHARSETID>
                    <CHARSETFORM>1</CHARSETFORM>
                    <CHARLENGTH>10</CHARLENGTH>
                </COL_LIST_ITEM>
                <COL_LIST_ITEM>
                    <OBJ_NUM>3286546</OBJ_NUM>
                    <COL_NUM>3</COL_NUM>
                    <INTCOL_NUM>3</INTCOL_NUM>
                    <SEGCOL_NUM>3</SEGCOL_NUM>
                    <COL_SORTKEY>3</COL_SORTKEY>
                    <BASE_INTCOL_NUM>3</BASE_INTCOL_NUM>
                    <BASE_COL_TYPE>0</BASE_COL_TYPE>
                    <PROPERTY>0</PROPERTY>
                    <NAME>DATA_TIMESTAMP</NAME>
                    <TYPE_NUM>231</TYPE_NUM>
                    <LENGTH>11</LENGTH>
                    <SCALE>6</SCALE>
                    <NOT_NULL>1</NOT_NULL>
                    <CHARSETID>0</CHARSETID>
                    <CHARSETFORM>0</CHARSETFORM>
                    <CHARLENGTH>0</CHARLENGTH>
                </COL_LIST_ITEM>
            </COL_LIST>
        </STRMTABLE_T>
    </ROW>
</ROWSET>

As you can see, it contains information <DBTIMEZONE>+01:00</DBTIMEZONE>, so we can assume when you import such file into anther database running on different DBTIMEZONE the values will be properly converted.