15.00 - TIME - Teradata Database

Teradata Database SQL Functions, Operators, Expressions, and Predicates

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1145-015K
Language
English (United States)

TIME

Purpose  

Returns the current time.

Syntax  

where:

 

Syntax element …

Specifies …

AT LOCAL

that the value returned is constructed from the session time and session time zone if the DBS Control flag TimeDateWZControl is enabled.

If TimeDateWZControl is disabled, the value returned is constructed from the time value local to the Teradata Database server and the session time zone.

AT [TIME ZONE] expression

that the time zone displacement defined by expression is used. The data type of expression should be INTERVAL HOUR(2) TO MINUTE or it must be a data type that can be implicitly converted to INTERVAL HOUR(2) TO MINUTE. For details, see “AT LOCAL and AT TIME ZONE Time Zone Specifiers” on page 827.

AT [TIME ZONE] time_zone_string

that time_zone_string is used to determine the time zone displacement. For details, see “AT LOCAL and AT TIME ZONE Time Zone Specifiers” on page 827.

ANSI Compliance

This is a Teradata extension to the ANSI SQL:2011 standard.

For the ANSI-compliant syntax and behavior for the equivalent function, see “CURRENT_TIME” on page 355.

The AT clause is ANSI SQL:2011 compliant.

As an extension to ANSI, you can specify the time zone displacement using additional expressions besides an INTERVAL expression.

Usage Notes

TIME returns the current time when the request started. If TIME is invoked more than once during the request, the same time is returned. The time returned does not change during the duration of the request.

If you specify TIME without the AT clause or TIME AT LOCAL, then the value returned depends on the setting of the DBS Control flag TimeDateWZControl as follows:

  • If the TimeDateWZControl flag is enabled, TIME returns a time constructed from the session time and session time zone.
  • If the TimeDateWZControl flag is disabled, TIME returns a time constructed from the time value local to the Teradata Database server and the session time zone.
  • For more information, see “DBS Control (dbscontrol)” in Utilities: Volume 1 (A-K).

    TIME data is stored internally in UTC, which can affect how TIME result values sort. For more information, see “ORDER BY Clause” in SQL Data Manipulation Language.

    TIME returns a value that is adjusted to account for the start and end of daylight saving time (DST) only in the following cases:

  • TIME is specified with AT [TIME ZONE] time_zone_string, where time_zone_string follows different DST and standard time zone displacements.
  • TIME is specified with AT LOCAL or without an AT clause and the session time zone was defined with a time zone string that follows different DST and standard time zone displacements.
  • For more information about time zone strings, see “AT LOCAL and AT TIME ZONE Time Zone Specifiers” on page 827.

    TIME cannot appear as the first argument in a user-defined method invocation.

    Result Type and Attributes

    The data type, format, and title for TIME are as follows:

     

    Data Type

    Format

    Title

    FLOAT

    HHMMSS.CC (hours, minutes, seconds, hundredths of a second)

    Time

    TIME versus CURRENT_TIME

    TIME is deprecated. Use the ANSI SQL:2011 compliant CURRENT_TIME function instead. See “CURRENT_TIME” on page 355.

    Example  

    If the DBS Control flag TimeDateWZControl is enabled, the following statements request the current time based on the current session time and time zone.

       SELECT TIME;
       SELECT TIME AT LOCAL;

    The result is similar to:

        Time
    --------
    16:20:20

    If the session time zone was defined with a time zone string that follows different DST and standard time zone displacements, then the time returned is automatically adjusted to account for the start and end of daylight saving time. Otherwise, no adjustment for daylight saving time is done.

    Example  

    The following queries return the current time at the time zone displacement based on the time zone string, 'America Pacific'. The time returned is automatically adjusted to account for the start and end of daylight saving time.

       SELECT TIME AT TIME ZONE 'America Pacific';
       SELECT TIME AT 'America Pacific';

    Example  

    The hundredths of a second are not displayed by the default format, but you can use the FORMAT phrase to display it:

       SELECT TIME (FORMAT '99:99:99.99');

    The system responds with something like the following:

           Time
    -----------
    16:26:30.19

    Example  

    The following example inserts a row in a hypothetical table in which the column InsertTime has data type FLOAT and records the time that the row was inserted:

       INSERT INTO HypoTable (ColumnA, ColumnB, InsertTime) 
       VALUES ('Abcde', 12345, TIME);