15.00 - CURRENT_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)
Last Update
2018-09-24

CURRENT_TIME

Purpose  

Returns the current time.

Syntax  

where:

 

Syntax element …

Specifies …

fractional_precision

an optional precision range for the returned time value.

The valid range is 0 through 6, inclusive.

The default is 0.

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

CURRENT_TIME and the AT clause are 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

CURRENT_TIME returns the current time when the request started. If CURRENT_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 CURRENT_TIME without the AT clause or CURRENT_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, CURRENT_TIME returns a time constructed from the session time and session time zone.
  • If the TimeDateWZControl flag is disabled, CURRENT_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).

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

  • CURRENT_TIME is specified with AT [TIME ZONE] time_zone_string, where time_zone_string follows different DST and standard time zone displacements.
  • CURRENT_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.

    Note: If CURRENT_TIME is used in a stored procedure, the procedure must be recompiled whenever the DBS Control fields System TimeZone Hour or System TimeZone Minute are changed. Recompiling stored procedures is not necessary if a time zone string is set using the tdlocaledef utility. For details, see “CREATE PROCEDURE (SQL Form)” in SQL Data Definition Language and “DBS Control (dbscontrol)” in Utilities: Volume 1 (A-K).

    Result Type and Attributes

    The result data type, format, and title for CURRENT_TIME are:

     

    Data Type

    Format

    Title

    TIME WITH TIME ZONE

    Default format for the TIME WITH TIME ZONE data type.

    For more information on the default formats, see “Data Type Formats and Format Phrases” in SQL Data Types and Literals.

    Current Time
    (
    fractional_precision)

    To convert CURRENT_TIME, use Teradata explicit conversion syntax or ANSI CAST syntax. For an example that uses Teradata explicit conversion syntax to change the default output format, see “Example 3: Changing the Default Output Format” on page 358.

    Precision

    The seconds precision of the result of CURRENT_TIME is limited to hundredths of a second. CURRENT_TIME returns zeros for any digits to the right of the two most significant digits in the fractional portion of seconds.

    CURRENT_TIME Fields

    The fields in CURRENT_TIME are:

  • HOUR
  • MINUTE
  • SECOND
  • TIMEZONE_HOUR
  • TIMEZONE_MINUTE
  • CURRENT_TIME versus TIME

    CURRENT_TIME provides similar functionality to the Teradata function TIME using ANSI-compliant syntax. For information on the Teradata TIME function, see “TIME” on page 376.

    Example : Requesting the Current Time

    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 CURRENT_TIME;
       SELECT CURRENT_TIME AT LOCAL;

    The result is similar to:

       Current Time(0)
       ---------------
        15:53:34+00:00

    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 : Requesting the Current Time with a Time Zone String

    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 CURRENT_TIME AT TIME ZONE 'America Pacific';
       SELECT CURRENT_TIME AT 'America Pacific';

    Example : Changing the Default Output Format

    To change the default output format of the CURRENT_TIME result, use Teradata explicit conversion syntax and specify the FORMAT phrase. For example, the following statement requests the current time and specifies a format that is different from the default:

       SELECT CURRENT_TIME (FORMAT 'HH:MIBT');

    The result looks like this:

       Current Time(0)
       ---------------
              02:29 PM

    For more information on Teradata explicit conversion syntax, see “Teradata Conversion Syntax in Explicit Data Type Conversions” on page 593. For more information on default data type formats and the FORMAT phrase, see “Data Type Formats and Format Phrases” in SQL Data Types and Literals.