Example

Teradata Vantageā„¢ Data Types and Literals

brand
Software
Teradata Vantage
prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
Programming Reference
featnum
B035-1143-162K

Consider the following statements:

SET TIME ZONE INTERVAL '01:00' HOUR TO MINUTE;
SELECT CAST(TIMESTAMP '2008-06-02 04:30:00+09:00' 
   AS DATE AT SOURCE TIME ZONE);
SELECT TIMESTAMP '2008-06-01 20:30:00+01:00' 
   AT TIME ZONE INTERVAL '09' HOUR (DATE, AT SOURCE);
SELECT TIMESTAMP '2008-06-01 20:30:00' (DATE, AT +9);

These SELECT statements return the date for time zone displacement, INTERVAL '09:00' HOUR TO MINUTE; that is, the statements return '08/06/02'. If the SELECT statements were specified without an AT clause or with an AT LOCAL clause, these statements would return '08/06/01' for the current session time zone displacement, INTERVAL '01:00' HOUR TO MINUTE.

The following shows the results of the SELECT statements if the AT clause was not specified:

SET TIME ZONE INTERVAL '01:00' HOUR TO MINUTE;
SELECT CAST(TIMESTAMP '2008-06-02 04:30:00+09:00' AS DATE);
2008-06-02 04:30:00+09:00
-------------------------
                 08/06/01
SELECT TIMESTAMP '2008-06-01 20:30:00+01:00'
   AT TIME ZONE INTERVAL '09:00' HOUR TO MINUTE;
2008-06-01 20:30:00+01:00 AT TIME ZONE INTERVAL  9:00 HOUR TO MINUTE
--------------------------------------------------------------------
                                           2008-06-02 04:30:00+09:00
SELECT TIMESTAMP '2008-06-01 20:30:00+01:00'
   AT TIME ZONE INTERVAL '09:00' HOUR TO MINUTE (DATE);
2008-06-01 20:30:00+01:00 AT TIME ZONE INTERVAL  9:00 HOUR TO MINUTE
--------------------------------------------------------------------
                                                            08/06/01
SELECT TIMESTAMP '2008-06-01 20:30:00' (DATE);
2008-06-01 20:30:00
-------------------
           08/06/01

The following shows the results of the SELECT statements if the AT clause was not specified, and the current session time zone displacement is INTERVAL '09:00' TO MINUTE.

SET TIME ZONE INTERVAL '09:00' HOUR TO MINUTE;
SELECT CAST(TIMESTAMP '2008-06-02 04:30:00+09:00' AS DATE);
2008-06-02 04:30:00+09:00
-------------------------
                 08/06/02
SELECT TIMESTAMP '2008-06-01 20:30:00+01:00'
   AT TIME ZONE INTERVAL '09:00' HOUR TO MINUTE (DATE);
2008-06-01 20:30:00+01:00 AT TIME ZONE INTERVAL  9:00 HOUR TO MINUTE
--------------------------------------------------------------------
                                                            08/06/02
SELECT CAST(TIMESTAMP '2008-06-01 20:30:00+01:00' 
   AS TIMESTAMP(0)) (DATE);
2008-06-01 20:30:00+01:00
-------------------------
                 08/06/02