15.00 - CURRENT_TIMESTAMP - 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_TIMESTAMP

Purpose  

Returns the current timestamp.

Syntax  

where:

 

Syntax element …

Specifies …

fractional_precision

an optional precision range for the returned timestamp value.

The valid range is 0 through 6, inclusive.

The default is 6.

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_TIMESTAMP 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_TIMESTAMP returns the current timestamp when the request started. If CURRENT_TIMESTAMP is invoked more than once during the request, the same timestamp is returned. The timestamp returned does not change during the duration of the request.

If you specify CURRENT_TIMESTAMP without the AT clause or CURRENT_TIMESTAMP 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_TIMESTAMP returns a timestamp constructed from the session time and session time zone.
  • If the TimeDateWZControl flag is disabled, CURRENT_TIMESTAMP returns a timestamp 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_TIMESTAMP returns a value that is adjusted to account for the start and end of daylight saving time (DST) only in the following cases:

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

     

    Data Type

    Format

    Title

    TIMESTAMP WITH TIME ZONE

    Default format for the TIMESTAMP 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 TimeStamp
    (
    fractional_precision)

    To convert CURRENT_TIMESTAMP, 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 4: Changing the Default Output Format” on page 362.

    Precision

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

    CURRENT_TIMESTAMP Fields

    The fields in CURRENT_TIMESTAMP are:

  • YEAR
  • MONTH
  • DAY
  • HOUR
  • MINUTE
  • SECOND
  • TIMEZONE_HOUR
  • TIMEZONE_MINUTE
  • Example : Requesting the Current Timestamp

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

       SELECT CURRENT_TIMESTAMP;
       SELECT CURRENT_TIMESTAMP AT LOCAL;

    The result is similar to:

                   Current TimeStamp(6)
       --------------------------------
       2001-11-27 15:53:34.910000+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 timestamp 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 : CURRENT_TIMESTAMP and the TimeDateWZControl Flag

    This example shows the effect of the DBS Control flag TimeDateWZControl on the results returned by CURRENT_TIMESTAMP when the function is specified without an AT clause or with an AT LOCAL clause.

    Assume the following:

  • The time local to the Teradata Database server is 11:59:00 Coordinated Universal Time (UTC), January 31, 2010.
  • User TK lives in Tokyo, and has a time zone defined as +9 hours offset from UTC.
  • User LA lives in Los Angeles, and has a time zone defined as -8 hours offset from UTC.
  • User TK and User LA run the CURRENT_TIMESTAMP function at exactly the same time.
  • If the TimeDateWZControl flag is enabled:

    For User TK, the CURRENT_TIMESTAMP function returns:

       2010-02-01 10:59:00.000000+09:00

    For User LA, the CURRENT_TIMESTAMP function returns:

       2010-01-31 16:59:00.000000-08:00

    If the TimeDateWZControl flag is disabled:

    For User TK, the CURRENT_TIMESTAMP function returns:

       2010-01-31 11:59:00.000000+09:00

    For User LA, the CURRENT_TIMESTAMP function returns:

       2010-01-31 11:59:00.000000-08:00

    Example : Requesting the Current Timestamp with a Time Zone String

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

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

    Example : Changing the Default Output Format

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

       SELECT CURRENT_TIMESTAMP (FORMAT 'MMMBDD,BYYYYBHH:MIBT');

    The result looks like this:

        Current TimeStamp(6)
       ---------------------
       Feb 19, 2002 07:45 am

    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.