Skip to content

ABAP Keyword Documentation →  ABAP − Reference →  Processing External Data →  ABAP Database Access →  ABAP SQL →  ABAP SQL - Operands and Expressions →  ABAP SQL - SQL Expressions sql_exp →  sql_exp - sql_func →  ABAP SQL - Built-In Functions sql_func →  ABAP SQL - Special Functions →  ABAP SQL - Date Functions and Time Functions 

sql_exp - Time Stamp Functions

Other versions: 7.31 | 7.40 | 7.54

Syntax



... TSTMP_IS_VALID( tstmp ) 
  | TSTMP_CURRENT_UTCTIMESTAMP( ) 

  | TSTMP_SECONDS_BETWEEN( tstmp1   = tstmp1, 
   
                       tstmp2   = tstmp2[, 

                           on_error = on_error] ) 

  | TSTMP_ADD_SECONDS( tstmp    = tstmp, 
  
                    seconds  = seconds[, 

                       on_error = on_error] ) ...

Variants

1. ... TSTMP_IS_VALID( ... )

2. ... TSTMP_CURRENT_UTCTIMESTAMP( )

3. ... TSTMP_SECONDS_BETWEEN( ... )

4. ... TSTMP_ADD_SECONDS( ... )

Effect

These SQL functions perform operations with arguments of the predefined data type DEC with length 15 or of the data element TIMESTAMP. The content of an argument like this is interpreted as an ABAP-specific time stamp in a packed number.

The arguments of the functions are specified as a comma-separated list in parentheses. A blank must be placed after the opening parenthesis and before the closing parenthesis. The function TSTMP_IS_VALID has a positional parameter, the function TSTMP_CURRENT_UTCTIMESTAMP does not have any parameters, and the functions TSTMP_SECONDS_BETWEEN and TSTMP_ADD_SECONDS have keyword parameters. Optional parameters can be assigned actual parameters when called; non-optional parameters, however, must be assigned actual parameters.

SQL expressions of matching data types can be specified as actual parameters. Only enumeration constants of specific classes can be passed to the parameter on_error. If an actual parameter contains the null value, every function except TSTMP_IS_VALID returns a null value.


Note

When used, these functions require the strict mode from Release 7.53.

Variant 1

... TSTMP_IS_VALID( ... )

Effect

The function TSTMP_IS_VALID determines whether an argument tstmp contains a valid time stamp in the format YYYYMMDDHHMMSS. The actual parameter must have the built-in data type DEC with length 15 and no decimal places. The result has the data type INT4. A valid time stamp produces the value 1 and all other input values (including the null value) produce the value 0.

Variant 2

... TSTMP_CURRENT_UTCTIMESTAMP( )

Effect

The function TSTMP_CURRENT_UTCTIMESTAMP returns a UTC time stamp in accordance with the POSIX standard. The result has the data type DEC with length 15 and no decimal places.


Note

  • If the function TSTMP_CURRENT_UTCTIMESTAMP is used more thane once within an ABAP SQL statement, it cannot be guaranteed that the every call within a database access produces the same result. Hence, later calls can produce later time stamps.

Variant 3

... TSTMP_SECONDS_BETWEEN( ... )

Effect

The function TSTMP_SECONDS_BETWEEN calculates the difference between two specified time stamps, tstmp1 and tstmp2 in seconds. The actual parameters for the formal parameters tstmp1 and tstmp2 must have the built-in data type DEC with length 15 and no decimal places and contain valid time stamps in the format YYYYMMDDHHMMSS. Any invalid time stamps produce an error. If tstmp2 is greater than tstmp1, the result is positive. In the reverse case, it is negative.

The optional parameter on_error defines the way errors are handled. The parameter for on_error must be an enumerated object with the enumerated type ON_ERROR from the class SQL_TSTMP_SECONDS_BETWEEN and the following enumeration constants can be passed:

  • SQL_TSTMP_SECONDS_BETWEEN=>FAIL (an error raises an exception (default))
  • SQL_TSTMP_SECONDS_BETWEEN=>SET_TO_NULL (an error returns the null value)

Variant 4

... TSTMP_ADD_SECONDS( ... )

Effect

The function TSTMP_ADD_SECONDS adds seconds seconds to a time stamp tstmp. The actual parameter for the formal parameter tstmp must have the built-in data type DEC with length 15 and no decimal places and contain a valid time stamp in the format YYYYMMDDHHMMSS. An invalid time stamp produces an error. The actual parameter for the formal parameter seconds must also have the built-in data type DEC with length 15 and no decimal places. Any negative values are subtracted. If the result is invalid, an error occurs.

The optional parameter on_error defines the way errors are handled. The parameter for on_error must be an enumerated object with the enumerated type ON_ERROR from the class SQL_TSTMP_ADD_SECONDS and the following enumeration constants can be passed:

  • SQL_TSTMP_ADD_SECONDS=>FAIL (an error raises an exception (default))
  • SQL_TSTMP_ADD_SECONDS=>SET_TO_NULL (an error returns the null value)


Example

Applies the time stamp functions to columns of the database table DEMO_EXPRESSIONS. The program DEMO_SQL_TIMESTAMP_FUNCTIONS executes this access to the table and represents the result. The column NUM1 of the database table is given a value that is added to a time stamp in the column TIMESTAMP1 as seconds. The difference is found between this sum and a time stamp retrieved on the database by the function TSTMP_CURRENT_UTCTIMESTAMP. A delay, wait, can be integrated between the time stamp in the ABAP program and the time stamp created on the database.

DATA(seconds) = 3600. 
cl_demo_input=>add_field( CHANGING field = seconds ). 
DATA(wait) = 1. 
cl_demo_input=>request( CHANGING field = wait ). 

GET TIME STAMP FIELD DATA(timestamp1). 

DELETE FROM demo_expressions. 
INSERT demo_expressions FROM @( VALUE #( 
  id         = 'X' 
  num1       = seconds 
  timestamp1 = timestamp1 ) ). 
WAIT UP TO COND #( WHEN wait > 10 THEN 10 
                   WHEN wait <  0 THEN 0 
                   ELSE wait ) SECONDS. 

TRY. 
    SELECT 
      SINGLE 
      FROM demo_expressions 
      FIELDS 
        timestamp1, 
        tstmp_is_valid( 
          timestamp1 ) 
            AS valid1, 
        tstmp_seconds_between( 
          tstmp1 = tstmp_current_utctimestamp( ), 
          tstmp2 = tstmp_add_seconds( 
                    tstmp    = timestamp1, 
                     seconds  = CAST( num1 AS DEC( 15,0 ) ), 
                     on_error = @sql_tstmp_add_seconds=>fail ), 
          on_error = @sql_tstmp_seconds_between=>fail ) 
            AS difference 
      INTO @DATA(result). 
  CATCH cx_sy_open_sql_db INTO DATA(exc). 
    cl_demo_output=>display( exc->get_text( ) ). 
    RETURN. 
ENDTRY.