Skip to main content

Celonis Product Documentation

TIMESTAMP_COLUMN
Description

TIMESTAMP_COLUMN allows to refer the timestamp column of an activity table in the data model without using the exact column name.

Syntax
 TIMESTAMP_COLUMN ( [ expression ] )
  • When an expression that involves a column of an activity table is passed as an argument, the timestamp column of that activity table is referenced.

  • When no argument is passed, the timestamp column of the default activity table is referenced.

  • When a table reference is passed as an argument, the timestamp column of that activity table is referenced.

Examples

[1]

In this example, the data model contains one activity table. Using a column reference as the input expression, we refer to the TIMESTAMP column of the related activity table:

Query

Column1

         TIMESTAMP_COLUMN ( "Activities"."ACTIVITY" )
        

Input

Output

Activities

CASE_ID : string

ACTIVITY : string

TIMESTAMP : date

'1'

'A'

Wed Jan 01 2020 00:00:00.000

'1'

'B'

Thu Jan 02 2020 00:00:00.000

'2'

'A'

Wed Jan 01 2020 00:00:00.000

'2'

'C'

Thu Jan 02 2020 00:00:00.000

'3'

'D'

Wed Jan 01 2020 00:00:00.000

Result

Column1 : date

Wed Jan 01 2020 00:00:00.000

Thu Jan 02 2020 00:00:00.000

Wed Jan 01 2020 00:00:00.000

Thu Jan 02 2020 00:00:00.000

Wed Jan 01 2020 00:00:00.000

[2]

In this example, the data model contains one activity table. By passing no arguments to the TIMESTAMP_COLUMN function, we refer to the TIMESTAMP column of the default activity table:

Query

Column1

         TIMESTAMP_COLUMN ( )
        

Input

Output

Activities

CASE_ID : string

ACTIVITY : string

TIMESTAMP : date

'1'

'A'

Wed Jan 01 2020 00:00:00.000

'1'

'B'

Thu Jan 02 2020 00:00:00.000

'2'

'A'

Wed Jan 01 2020 00:00:00.000

'2'

'C'

Thu Jan 02 2020 00:00:00.000

'3'

'D'

Wed Jan 01 2020 00:00:00.000

Result

Column1 : date

Wed Jan 01 2020 00:00:00.000

Thu Jan 02 2020 00:00:00.000

Wed Jan 01 2020 00:00:00.000

Thu Jan 02 2020 00:00:00.000

Wed Jan 01 2020 00:00:00.000

[3]

In this example, MERGE_EVENTLOG is used to combine two activity tables into one. We can then use TIMESTAMP_COLUMN to refer to the TIMESTAMP column of the merged activity table:

Query

Column1

         TIMESTAMP_COLUMN ( MERGE_EVENTLOG ( "ACTIVITIES_BSEG"."CASE_ID" , "ACTIVITIES_BKPF"."CASE_ID" ) )
        

Input

Output

ACTIVITIES_BKPF

CASE_ID : string

ACTIVITY : string

TIMESTAMP : date

'C1'

'A'

Wed Jan 01 2020 03:00:00.000

ACTIVITIES_BSEG

CASE_ID : string

ACTIVITY : string

TIMESTAMP : date

'D1'

'X'

Wed Jan 01 2020 01:00:00.000

'D1'

'Y'

Thu Jan 02 2020 01:00:00.000

'D2'

'X'

Wed Jan 01 2020 02:00:00.000

'D2'

'Z'

Thu Jan 02 2020 02:00:00.000

BKPF

CASE_ID : string

'C1'

BSEG

CASE_ID : string

SUPER_CASE_ID : string

'D1'

'C1'

'D2'

'C1'

Foreign Keys

BKPF.CASE_ID

ACTIVITIES_BKPF.CASE_ID

BSEG.CASE_ID

ACTIVITIES_BSEG.CASE_ID

BKPF.CASE_ID

BSEG.SUPER_CASE_ID

Result

Column1 : date

Wed Jan 01 2020 01:00:00.000

Wed Jan 01 2020 03:00:00.000

Thu Jan 02 2020 01:00:00.000

Wed Jan 01 2020 02:00:00.000

Wed Jan 01 2020 03:00:00.000

Thu Jan 02 2020 02:00:00.000

[4]

In this example, the data model contains one activity table. Using a table reference as the input expression, we refer to the TIMESTAMP column of the related activity table:

Query

Column1

         TIMESTAMP_COLUMN ( "Activities" )
        

Input

Output

Activities

CASE_ID : string

ACTIVITY : string

TIMESTAMP : date

'1'

'A'

Wed Jan 01 2020 00:00:00.000

'1'

'B'

Thu Jan 02 2020 00:00:00.000

'2'

'A'

Wed Jan 01 2020 00:00:00.000

'2'

'C'

Thu Jan 02 2020 00:00:00.000

'3'

'D'

Wed Jan 01 2020 00:00:00.000

Result

Column1 : date

Wed Jan 01 2020 00:00:00.000

Thu Jan 02 2020 00:00:00.000

Wed Jan 01 2020 00:00:00.000

Thu Jan 02 2020 00:00:00.000

Wed Jan 01 2020 00:00:00.000