Applies to:  CELONIS 4.0 CELONIS 4.2 CELONIS 4.3 CELONIS 4.4 

Description

The Remap Timestamp operator counts time units in specified intervals. Users can create intervals like working weeks with breaks in between and count the number of time units inside these time intervals.

Syntax

REMAP_TIMESTAMPS ( table.column, time unit)
REMAP_TIMESTAMPS ( table.column, time unit, DAY [HH:MM-HH:MM] )
  • Calendar specification: If no time is specified, a full day (00:00-24:00) is assumed.
REMAP_TIMESTAMPS ( table.column, time unit, FACTORY_CALENDAR ( table.start_date_column, table.end_date_column [, filter_condition ] ) )
  • Factory calendar specification

Tips

The query may have more than one calendar specification.

When the query has more than one calendar specification for the same day, only the last one is considered and others are ignored. Queries can have either a calendar specification or a factory calendar specification.

How It Works

In the basic case, the operator counts the number of specified time units from the date 01.01.1970 to the date value in the input column and returns it as INT value.

Calendar Specification

A calendar specification allows to restrict the accounted time to the given time intervals per week. Input dates outside of the given intervals are moved to the beginning of the next interval. There are two types of calendar specifications:

  • Day Based Calendar
  • Time Based Calendar

Day Based Calendar

A day based calendar is triggered when only full days (no times) are specified. Such a calendar works only on full days. The actual daytime is ignored. For example the result for 01.01.2018 00:00 and 01.01.2018 23:30 is the same if day based calendar is used.

If the behavior of the day based calendar is not desired but the specified interval should be based on full days, it is recommended to specify at least one day with a time range of 00:00-23:59. The tiny error margin should be negligible in most cases.

Time Based Calendar

It is always applied, if at least on one weekday the daytime is restricted. A time based calendar takes the full timestamp into account.

Factory Calendar

The Factory Calendar allows to restrict the accounted time to fine grained intervals. The intervals are defined by the given start and end column. The Factory Calendar itself is a table, that has to be added to the data model. The table needs to have at least a start date column and an end date column, which can then be used by the Factory Calendar statement. For example, a Factory Calendar table could contain up-time intervals of a machine. Applying this to the Remap Timestamp operator allows to calculate exact throughput calculation for this machine's up-time.

Examples

Calculating duration in days based on a constant timestamp.
Query
Column1
REMAP_TIMESTAMPS(TO_TIMESTAMP('02.01.1970', 'DD.MM.YYYY'), DAYS)
Input
Output
Result
Column1 : INT
1



Calculating duration in days based on a constant timestamp and specified calendar.
Query
Column1
REMAP_TIMESTAMPS(TO_TIMESTAMP('01.12.1969 06:30.30', 'DD.MM.YYYY HH:mm.SS'), DAYS, MONDAY TUESDAY WEDNESDAY THURSDAY FRIDAY)
Input
Table1
Column1 : DATE
null
Thu Jan 01 1970 00:00:00.000
Output
Result
Column1 : INT
-23



Calculating duration in hours based on a constant timestamp and specified calendar with time intervals.
Query
Column1
REMAP_TIMESTAMPS(TO_TIMESTAMP('29.12.1969 06:30.30', 'DD.MM.YYYY HH:mm.SS'), HOURS, MONDAY 8:00-13:00 WEDNESDAY 8:00-13:00)
Input
Table1
Column1 : DATE
null
Thu Jan 01 1970 00:00:00.000
Output
Result
Column1 : INT
-10



Calculating duration in unit seconds by using a factory calendar.
Query
Column1
REMAP_TIMESTAMPS("activity"."time", SECONDS, FACTORY_CALENDAR("calendar"."start", "calendar"."end"))
Input
activity
id : INTcase : INTact : STRINGtime : DATE
1
1
'a'
Thu Jan 01 1970 00:00:00.000
2
1
'b'
Thu Jan 01 1970 00:01:00.000
3
1
'c'
Thu Jan 01 1970 00:02:00.000
4
1
'd'
Thu Jan 01 1970 00:03:00.000
5
2
'a'
Thu Jan 01 1970 00:01:00.000
6
2
'b'
Thu Jan 01 1970 00:02:00.000
7
2
'c'
Thu Jan 01 1970 00:04:00.000
8
2
'd'
Thu Jan 01 1970 00:05:00.000
9
3
'a'
Thu Jan 01 1970 00:00:00.000
10
3
'b'
Thu Jan 01 1970 00:01:00.000
11
3
'b'
Thu Jan 01 1970 00:02:00.000
12
3
'b'
Thu Jan 01 1970 00:03:00.000
calendar
id : INTstart : DATEend : DATE
1
Thu Jan 01 1970 00:00:00.000
Thu Jan 01 1970 00:01:00.000
2
Thu Jan 01 1970 00:02:00.000
Thu Jan 01 1970 00:03:00.000
3
Thu Jan 01 1970 00:04:00.000
Thu Jan 01 1970 00:05:00.000
4
Thu Jan 01 1970 00:06:00.000
Thu Jan 01 1970 00:07:00.000
Output
Result
Column1 : INT
0
60
60
120
60
60
120
180
0
60
60
120



Calculating duration in unit seconds by using a factory calendar with a filter.
Query
Column1
REMAP_TIMESTAMPS("activity"."time", SECONDS, FACTORY_CALENDAR("calendar"."start", "calendar"."end", "calendar"."filter" = 1))
Input
activity
id : INTcase : INTact : STRINGtime : DATE
1
1
'a'
Thu Jan 01 1970 00:00:00.000
2
1
'b'
Thu Jan 01 1970 00:01:00.000
3
1
'c'
Thu Jan 01 1970 00:02:00.000
4
1
'd'
Thu Jan 01 1970 00:03:00.000
5
2
'a'
Wed Dec 31 1969 23:59:00.000
6
2
'b'
Thu Jan 01 1970 00:02:00.000
7
2
'c'
null
8
2
'd'
Thu Jan 01 1970 00:05:00.000
9
3
'a'
Thu Jan 01 1970 00:00:00.000
10
3
'b'
Thu Jan 01 1970 00:01:00.000
11
3
'b'
Thu Jan 01 1970 00:02:00.000
12
3
'b'
Thu Jan 01 1970 00:03:00.000
calendar
id : INTfilter : INTstart : DATEend : DATE
1
1
Thu Jan 01 1970 00:00:00.000
Thu Jan 01 1970 00:01:00.000
2
1
null
Thu Jan 01 1970 00:03:00.000
3
1
Thu Jan 01 1970 00:04:00.000
null
4
2
Thu Jan 01 1970 00:04:00.000
Thu Jan 01 1970 00:03:00.000
Output
Result
Column1 : INT
0
60
60
60
0
60
null
60
0
60
60
60



Example for a Day Based Calendar. The actual day time is ignored. 01.01.1970 was a Thursday.
Query
Column1
REMAP_TIMESTAMPS(TO_TIMESTAMP('01.01.1970 04:00', 'DD.MM.YYYY HH:mm'), HOURS, THURSDAY FRIDAY)
Input
Output
Result
Column1 : INT
0



Example for a Day Based Calendar. The actual day time is ignored. 01.01.1970 was a Thursday.
Query
Column1
REMAP_TIMESTAMPS(TO_TIMESTAMP('01.01.1970 04:00', 'DD.MM.YYYY HH:mm'), HOURS, THURSDAY FRIDAY 00:00-12:59)
Input
Output
Result
Column1 : INT
4



  • No labels