This example migrates data from a pre-13.10 system to a new system with the time zone set to the system default and default TIMEDATEWZCONTROL = 2.
Old system: 16/17 = 0, 57=0
Following is the table definition for this example (the table is on the Block File System):
CREATE SET TABLE t1 ,NO FALLBACK , NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT, DEFAULT MERGEBLOCKRATIO ( i INTEGER, j TIMESTAMP(6)) PRIMARY INDEX ( i );
Two rows of data are inserted.
SELECT * FROM t1;
The results display as follows.
i j ----------- -------------------------- 1 2011-11-20 06:28:59.000000 2 9999-12-31 15:59:59.999999
The new system is set to this time zone: 18 = America Pacific, 57=2
Alter the table to the new time zone.
ALTER TABLE t1 FROM TIME ZONE='00:00',TIMEDATEWZCONTROL=0;
Set the time zone to Coordinated Universal Time (UTC) or Greenwich Mean Time.
SET TIME ZONE 'GMT';
Display the rows.
SELECT * FROM t1;
The converted date and timestamp information displays as follows.
i j ----------- -------------------------- 1 2011-11-20 14:28:59.000000 2 9999-12-31 23:59:59.000000
Set the time zone to the user default for the session.
SET TIME ZONE USER;
Display the rows.
SELECT * FROM t1;
The converted date and timestamp information displays as follows.
i j ----------- -------------------------- 1 2011-11-20 06:28:59.000000 2 9999-12-31 15:59:59.000000