Applies to:  CELONIS 4.0 CELONIS 4.2 CELONIS 4.3 CELONIS 4.4 CELONIS 4.5 CELONIS 4.6 

Description

The Celonis DATE type represents timestamps. The supported timestamps range from the year 1400 (including) to the year 10000 (excluding) on millisecond precision. Values outside this range will be mapped to null.

Date values can be defined as constants in two ways. Supported are date string inputs using {d } or integer inputs with a count of milliseconds since 01.01.1970 using {t }. The following two formats are supported as date string input:

  • yyyy-MM-dd HH:mm:ss
  • yyyy-MM-dd

Examples


[1] Create DATE types using date strings and number of milliseconds:

Query
Column1
{t 0}
Column2
{d'1970-01-02'}
Column3
{d'1970-01-03 00:00:00'}
Input
Output
Result
Column1 : DATEColumn2 : DATEColumn3 : DATE
Thu Jan 01 1970 00:00:00.000
Fri Jan 02 1970 00:00:00.000
Sat Jan 03 1970 00:00:00.000



[2] Filter activities by a DATE interval:

Query
Filter
FILTER "Activities"."Eventtime" > {d'2018-02-15'} AND "Activities"."Eventtime" < {d'2018-05-15'}
Column1
"Activities"."Eventtime"
Input
Activities
Eventtime : DATE
Mon Jan 01 2018 00:00:00.000
Thu Feb 01 2018 00:00:00.000
Thu Mar 01 2018 00:00:00.000
Sun Apr 01 2018 00:00:00.000
Tue May 01 2018 00:00:00.000
Fri Jun 01 2018 00:00:00.000
Sun Jul 01 2018 00:00:00.000
Output
Result
Column1 : DATE
Thu Mar 01 2018 00:00:00.000
Sun Apr 01 2018 00:00:00.000
Tue May 01 2018 00:00:00.000


To create a date constant TO_TIMESTAMP can be used.

  • No labels