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:
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:
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 |
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:
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:
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.