SET TIME ZONE Examples | Teradata Vantage - 17.10 - SET TIME ZONE Examples - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1144-171K
Language
English (United States)

Example: Setting the Time Zone to LOCAL

This example sets the session default time zone displacement to LOCAL, which is the system default time zone.

     SET TIME ZONE LOCAL;

Example: Setting the Time Zone to USER

This example sets the session default time zone displacement to USER, which is the default time zone for the logged on user who submits the request.

     SET TIME ZONE USER;

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 GetTimeZoneDisplacement in Teradata Vantage™ - SQL Date and Time Functions and Expressions, B035-1211), 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;

Example: Setting the Time Zone Using a Time Zone String

This example sets the session default time zone displacement by specifying a simple constant time zone string expression. Vantage then passes the string to a system-defined UDF that interprets it and sets the default time zone displacement for the session based on the value of the string.

SET TIME ZONE 'America Eastern';

If the value of the time_zone_string you specify is not valid, the request aborts and Vantage returns the following error to the requestor.

*** Failure 7455 Invalid Time Zone specified.