15.10 - expression - 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

Value of the default time zone for the session to the displacement specified by the numeric value of expression in units of hours.

Teradata Database implicitly converts the expression, as needed and if allowed, to a time zone displacement, where expression represents a simple constant numeric expression.

For a list of the implicit data type conversions Teradata Database performs on numeric expression data types, see “SET TIME ZONE” in SQL Data Definition Language - Detailed Topics, B035-1184.

Example: Setting the Time Zone Using a Simple INTERVAL HOUR TO MONTH Time Zone Constant Expression Displacement String

This example sets the session default time zone displacement by specifying an INTERVAL HOUR TO MONTH time displacement string.

     SET TIME ZONE INTERVAL '08:00' HOUR TO MINUTE;

Example: Setting the Time Zone Using a Simple Constant Expression

The first example sets the time zone displacement ahead by INTERVAL HOUR '01:00' MINUTE by specifying a simple constant expression of +1.

     SET TIME ZONE +1;

If you then submit the following SELECT request with an AT clause (see SQL Functions, Operators, Expressions, and Predicates, B035-1145), it returns the date for time zone displacement INTERVAL -'08:00' HOUR TO MINUTE, or '08/05/31'.

     SELECT CAST((TIMESTAMP '2008-06-01 08:30:00' AT TIME ZONE -8) 
            AS DATE AT SOURCE TIME ZONE);

The following SELECT request demonstrates the results without an AT clause for the target data type.

     SET TIME ZONE INTERVAL '01:00' HOUR TO MINUTE;
     SELECT TIMESTAMP '2008-06-01 08:30:00'
            AT TIME ZONE INTERVAL -'08:00' HOUR TO MINUTE;
2008-06-01 08:30:00 AT TIME ZONE INTERVAL -8:00 HOUR TO MINU
------------------------------------------------------------
2008-05-31 23:30:00-08:00

If you submit the same SELECT request without an AT clause for the target data type, or with an AT LOCAL clause, the request returns '08/06/01' for the current session time zone displacement INTERVAL HOUR '01:00' MINUTE.

     SELECT CAST((TIMESTAMP '2008-06-01 08:30:00'
            AT TIME ZONE INTERVAL -'08:00' HOUR TO MINUTE) AS DATE);
2008-06-01 08:30:00 AT TIME ZONE INTERVAL -8:00 HOUR TO MINU
------------------------------------------------------------
08/06/01

The following example demonstrates the results returned without an AT clause specified with the SELECT request and with a current session time zone displacement of INTERVAL -'08:00' HOUR TO MINUTE.

     SET TIME ZONE INTERVAL -'08:00' HOUR TO MINUTE;
     SELECT CAST((CAST(TIMESTAMP '2008-06-01 08:30:00+01:00' 
            AS TIMESTAMP(0))
            AT TIME ZONE INTERVAL -'08:00' HOUR TO MINUTE) AS DATE);
2008-06-01 08:30:00+01:00 AT TIME ZONE INTERVAL -8:00 HOUR T
------------------------------------------------------------
08/05/31

This example sets the time zone displacement for the current session to INTERVAL '08:00' HOUR TO MINUTE.

     SET TIME ZONE INTERVAL '08:00' HOUR TO MINUTE;