15.10 - Example: Migrating Data to a New Time Zone - Teradata Database

Teradata Database SQL Data Definition Language Syntax and Examples

prodname
Teradata Database
vrm_release
15.10
created_date
December 2015
category
Programming Reference
featnum
B035-1144-151K

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:

     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