Example: Migrating Data to a New Time Zone - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Published
September 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
wgr1555383704548.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1144
lifecycle
previous
Product Category
Teradata Vantage™

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