sql_func - Time Stamp Functions - ABAP Keyword Documentation

ABAP - Keyword Documentation → ABAP - Programming Language → 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 → sql_func - Special Functions → sql_func - Date Functions and Time Functions →

Mail Feedback

sql_func - Time Stamp Functions

Syntax Forms

Generic Time Stamp Functions

1. ... IS_VALID( date|time|utclong )
    | EXTRACT_YEAR( date|utclong )
    | EXTRACT_MONTH( date|utclong )
    | EXTRACT_DAY( date|utclong )
    | EXTRACT_HOUR( time|utclong )
    | EXTRACT_MINUTE( time|utclong )
    | EXTRACT_SECOND( time|utclong )
    | DAYNAME( date|utclong )
    | MONTHNAME( date|utclong )
    | WEEKDAY( date|utclong )
    | DAYS_BETWEEN( {date1|utclong1},{date2|utclong2} )
    | ADD_DAYS( {date|utclong},days )
    | ADD_MONTHS( {date|utclong},months ) ...

Functions for UTCLONG

2. ... UTCL_CURRENT( )
| UTCL_ADD_SECONDS( utclong,seconds )
| UTCL_SECONDS_BETWEEN( utclong1,utclong2 ) ...

Functions for TIMESTAMP

3. ... 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] ) ...

Effect

These SQL functions perform operations on time stamps. The first set covers generic functions, the second set covers functions depending on the built-in data type UTCLONG, and the third set covers functions depending on the data element TIMESTAMP.

The arguments of the functions are specified as a comma-separated list in parentheses. A blank must be placed after the opening parenthesis and in front of the closing parenthesis. Optional parameters can be assigned actual parameters when called. Non-optional parameters must be assigned actual parameters.

SQL expressions of matching data types can be specified as actual parameters. Only enumerated constants of specific classes can be passed to the parameter on_error for the TIMESTAMP functions TSTMP_SECONDS_BETWEEN and TSTMP_ADD_SECONDS. If an actual parameter contains the null value, every function except IS_VALID and TSTMP_IS_VALID returns a null value.

Generic Time Stamp Functions

Syntax

... IS_VALID( date|time|utclong )
  | EXTRACT_YEAR( date|utclong )
  | EXTRACT_MONTH( date|utclong )
  | EXTRACT_DAY( date|utclong )
  | EXTRACT_HOUR( time|utclong )
  | EXTRACT_MINUTE( time|utclong )
  | EXTRACT_SECOND( time|utclong )
  | DAYNAME( date|utclong )
  | MONTHNAME( date|utclong )
  | WEEKDAY( date|utclong )
  | DAYS_BETWEEN( {date1|utclong1},{date2|utclong2} )
  | ADD_DAYS( {date|utclong},days )
  | ADD_MONTHS( {date|utclong},months ) ...

Effect

These SQL functions perform operations with arguments of the built-in data types DATN, DATS, TIMN, TIMS, and UTCLONG. The generic functions are documented in date functions and time functions.

Hint

All generic functions enforce strict mode from ABAP release 7.56.

Functions for UTCLONG

Syntax

... UTCL_CURRENT( )
| UTCL_ADD_SECONDS( utclong,seconds )
| UTCL_SECONDS_BETWEEN( utclong1,utclong2 ) ...

Variants:

1. ... UTCL_CURRENT( )

2. ... UTCL_ADD_SECONDS( utclong,seconds )

3. ... UTCL_SECONDS_BETWEEN( utclong1,utclong2 )

Effect

These SQL functions perform operations with arguments of the built-in data type UTCLONG.

Hint

The use of the functions UTCL_CURRENT( ), UTCL_ADD_SECONDS, and UTCL_SECONDS_BETWEEN enforces the strict mode from ABAP release 7.55.

Variant 1

... UTCL_CURRENT( )

Effect

This function generates a UTC time stamp from the system time and the system date of AS ABAP in accordance with POSIX. The return value has the built-in dictionary type UTCLONG.

All columns of a query have the same time stamp.

Example

Selects column CARRID from table SCARR and returns a time stamp for each row. The time stamp is generated only once for the query and, therefore, all rows have the same time stamp.

SELECT carrid, utcl_current( ) AS time_stamp
FROM scarr
INTO TABLE @FINAL(result).

cl_demo_output=>display( result ).

Variant 2

... UTCL_ADD_SECONDS( utclong,seconds )

Effect

The function UTCL_ADD_SECONDS adds seconds seconds to a time stamp utclong. It has two positional parameters. The actual parameter for the formal parameter utclong must have the built-in dictionary type UTCLONG and contain a valid time stamp in the format YYYYMMDDHHMMSSMMMUUUN. An invalid time stamp produces the initial value. The actual parameter for the formal parameter seconds must have either the built-in data type INT4 or the data type DEC with length 21 and 7 decimal places. Any negative values are subtracted. If the result is invalid, a catchable exception of class CX_SY_OPEN_SQL_DB is raised.

The result is of type UTCLONG.

Variant 3

... UTCL_SECONDS_BETWEEN( utclong1,utclong2 )

Effect

The function UTCL_SECONDS_BETWEEN calculates the difference between two specified time stamps utcl1 and utcl2 in seconds. It has two positional parameters. The actual parameters for the formal parameters utcl1 and utcl2 must have the built-in dictionary type UTCLONG and contain a valid time stamp in the format YYYYMMDDHHMMSSMMMUUUN. Any invalid time stamps produce an error. If utcl2 is greater than utcl1, the result is positive. If the values are identical, the result is 0. In all other cases, the result is negative.

The result is of type DEC with length 21 and 7 decimal places.

Functions for TIMESTAMP

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( tstmp )

2. ... TSTMP_CURRENT_UTCTIMESTAMP( )

3. ... TSTMP_SECONDS_BETWEEN( ... )

4. ... TSTMP_ADD_SECONDS( ... )

Effect

These SQL functions perform operations with arguments of the data element TIMESTAMP.

Hint

The use of the functions TSTMP_IS_VALID, TSTMP_CURRENT_UTCTIMESTAMP( ), TSTMP_SECONDS_BETWEEN, and TSTMP_ADD_SECONDS enforces the strict mode from ABAP release 7.53.

Variant 1

... TSTMP_IS_VALID( tstmp )

Effect

The function TSTMP_IS_VALID determines whether an argument tstmp contains a valid time stamp in the format YYYYMMDDHHMMSS. It has one positional parameter. 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.

Hints

The UTC time stamp is created from the clock on the database server. The system date and the system time in AS ABAP from which its time stamp is created using GET TIME STAMP are synchronized with the database server clock.
If the function TSTMP_CURRENT_UTCTIMESTAMP is used more than once within an ABAP SQL statement, it cannot be guaranteed that 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. All parameters are keyword parameters. 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 affects how 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 enumerated 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. All parameters are keyword parameters. 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. Negative values are subtracted. If the result is invalid, an error occurs.

The optional parameter on_error affects how 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 enumerated 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 DDIC database table DEMO_EXPRESSIONS. The class CL_DEMO_SQL_TIMESTAMP_FUNC executes this access to the table and displays the result. The column NUM1 of the DDIC database table is given a value that is added to a time stamp in the column TIMESTAMP1 as seconds. The difference is calculated 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. The number of seconds specified as type-compliant literal in the variable pack is added to the time stamp utcl, which has the type UTCLONG. Finally, the difference in seconds between two time stamps of type UTCLONG is calculated. Since utcl1 is greater than utcl2, the result is negative.

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 FINAL(timestamp1).

DATA pack TYPE p LENGTH 11 DECIMALS 7 VALUE '123.456'.

FINAL(utcl) = utclong_current( ).
FINAL(utcl1) =
  utclong_add( val     = utcl
               days    = 1000
               hours   = 100
               minutes = 10 ).

DELETE FROM demo_expressions.
INSERT demo_expressions FROM @( VALUE #(
  id         = 'X'
  num1       = seconds
  timestamp1 = timestamp1
  utcl1 = utcl1
  utcl2 = utclong_current( ) ) ).
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,
        UTCL_ADD_SECONDS( utcl2, @pack ) as add_seconds,
        UTCL_SECONDS_BETWEEN( utcl1,utcl2 ) as seconds_between
      INTO @FINAL(result).
      cl_demo_output=>display( result ).
  CATCH cx_sy_open_sql_db INTO FINAL(exc).
    cl_demo_output=>display( exc->get_text( ) ).
    RETURN.
ENDTRY.