Example - Teradata Vantage - Analytics Database

Consider the following statements:

SET TIME ZONE INTERVAL '10:00' HOUR TO MINUTE;
SELECT CAST((TIMESTAMP '2008-06-01 18:30:00+01:00' AT '05:45')
   AS DATE AT SOURCE);
SELECT CAST((TIMESTAMP '2008-06-01 18:30:00+01:00' AT 5.75)
   AS DATE AT SOURCE);
SELECT TIMESTAMP '2008-06-01 23:15:00+05:45' 
   (DATE, AT SOURCE TIME ZONE);
SELECT TIMESTAMP '2008-06-02 03:30:00' (DATE, AT '05:45');
SELECT TIMESTAMP '2008-06-02 03:30:00' (DATE, AT 5.75);

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

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

SET TIME ZONE INTERVAL '10:00' HOUR TO MINUTE;
SELECT TIMESTAMP '2008-06-01 18:30:00+01:00'
   AT TIME ZONE INTERVAL '05:45' HOUR TO MINUTE;

Result:

2008-06-01 18:30:00+01:00 AT TIME ZONE INTERVAL  5:45 HOUR TO MINUTE
--------------------------------------------------------------------
                                           2008-06-01 23:15:00+05:45

SELECT CAST((TIMESTAMP '2008-06-01 18:30:00+01:00'
   AT TIME ZONE INTERVAL '05:45' HOUR TO MINUTE) AS DATE);

Result:

2008-06-01 18:30:00+01:00 AT TIME ZONE INTERVAL  5:45 HOUR TO MINUTE
--------------------------------------------------------------------
                                                            08/06/02

SELECT TIMESTAMP '2008-06-01 23:15:00+05:45' (DATE);

Result:

2008-06-01 23:15:00+05:45
-------------------------
                 08/06/02

SELECT TIMESTAMP '2008-06-02 03:30:00' (DATE);

Result:

2008-06-02 03:30:00
-------------------
           08/06/02

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 '05:45' HOUR TO MINUTE.

SET TIME ZONE INTERVAL '05:45' HOUR TO MINUTE;
SELECT CAST((TIMESTAMP '2008-06-01 18:30:00+01:00'
   AT TIME ZONE INTERVAL'05:45' HOUR TO MINUTE) AS DATE);

Result:

2008-06-01 18:30:00+01:00 AT TIME ZONE INTERVAL  5:45 HOUR TO MINUTE
--------------------------------------------------------------------
                                                            08/06/01

SELECT TIMESTAMP '2008-06-01 23:15:00+05:45' (DATE);

Result:

2008-06-01 23:15:00+05:45
-------------------------
                 08/06/01

SELECT CAST(TIMESTAMP '2008-06-02 03:30:00+10:00'
   AS TIMESTAMP(0)) (DATE);

Result:

2008-06-02 03:30:00+10:00
-------------------------
                 08/06/01