1

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.

9
  • I think I know the answer because TIMESTAMP (6) WITH LOCAL TIME ZONE doesn't store time zone info, but I'd like confirmation that my understanding is correct.
    – jpmc26
    Commented Oct 25, 2017 at 16:55
  • You can edit your own question to put in extra information!
    – Vérace
    Commented Oct 25, 2017 at 17:37
  • Can't you import the data and then (keeping track of the new records) check. If it's correct by yoiur criteria, happy days. If not, UPDATE tbl.newtime SET newtime = newtime + 1hr WHERE tbl.just_inserted = TRUE; - not sure of Oracle syntax, but you get the drift!
    – Vérace
    Commented Oct 25, 2017 at 17:43
  • @Vérace Not without creating an entirely new database. The database's time zone is a configuration value global to the entire DB: docs.oracle.com/cd/B19306_01/server.102/b14200/functions039.htm.
    – jpmc26
    Commented Oct 25, 2017 at 17:44
  • I've always thought that databases should store UTC and UTC only! Let the client sort out what it means locally!
    – Vérace
    Commented Oct 25, 2017 at 18:53

1 Answer 1

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.