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

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, [ calendar_specification | factory_calendar_specification ] )
  • column: The column containing the timestamps to be remapped.
  • time_unit: DAYS | HOURS | MINUTES | SECONDS | MILLISECONDS
  • calendar_specification: ( day [ day_time ] )*
    • day: MONDAY | TUESDAY | WEDNESDAY | THURSDAY | FRIDAY | SATURDAY | SUNDAY
    • day_time: Restrict the time of the day in the format HH:MM-HH:MM. If no time is specified, a full day (00:00-24:00) is assumed.
  • factory_calendar_specification: FACTORY_CALENDAR ( calendar_table.start_date_column, calendar_table.end_date_column [, filter_condition ] )
    • start_date_column: The date column containing the start dates of the intervals.
    • end_date_column: The date column containing the end dates of the intervals.
    • filter_condition: A filter condition to be applied.

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 Specification

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. Please note, the according intervals in the Factory Calendar table must not overlap.

Examples


[1] Calculate the number of working days between two dates, assuming that working days are Monday to Friday:

Query
Column1
REMAP_TIMESTAMPS ( "Table1"."DateColumn2", DAYS, MONDAY TUESDAY WEDNESDAY THURSDAY FRIDAY ) - REMAP_TIMESTAMPS ( "Table1"."DateColumn1", DAYS, MONDAY TUESDAY WEDNESDAY THURSDAY FRIDAY )
Input
Table1
DateColumn1 : DATEDateColumn2 : DATE
Fri Feb 01 2019 00:00:00.000
Fri Feb 01 2019 00:00:00.000
Fri Feb 01 2019 00:00:00.000
Sat Feb 02 2019 00:00:00.000
Fri Feb 01 2019 00:00:00.000
Sun Feb 03 2019 00:00:00.000
Fri Feb 01 2019 00:00:00.000
Mon Feb 04 2019 00:00:00.000
Fri Feb 01 2019 00:00:00.000
Tue Feb 05 2019 00:00:00.000
Fri Feb 01 2019 00:00:00.000
Wed Feb 06 2019 00:00:00.000
Fri Feb 01 2019 00:00:00.000
Thu Feb 07 2019 00:00:00.000
Fri Feb 01 2019 00:00:00.000
Fri Feb 08 2019 00:00:00.000
Fri Feb 01 2019 00:00:00.000
Sat Feb 09 2019 00:00:00.000
Fri Feb 01 2019 00:00:00.000
Sun Feb 10 2019 00:00:00.000
Fri Feb 01 2019 00:00:00.000
Mon Feb 11 2019 00:00:00.000
Fri Feb 01 2019 00:00:00.000
Tue Feb 12 2019 00:00:00.000
Output
Result
Column1 : INT
0
1
1
1
2
3
4
5
6
6
6
7



[2] Calculate the number of days since 01.01.1970 for a constant timestamp. No calendar is specified:

Query
Column1
REMAP_TIMESTAMPS ( TO_TIMESTAMP ( '02.01.1970', 'DD.MM.YYYY' ), DAYS )
Input
Output
Result
Column1 : INT
1



[3] Calculate the number of days since 01.01.1970 for a constant timestamp by using a specified day based calendar. The actual day time is ignored:

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
Output
Result
Column1 : INT
-23



[4] Calculate the number of hours since 01.01.1970 for a constant timestamp by using a specified time based calendar. The full timestamp is taken into account:

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
Output
Result
Column1 : INT
-10



[5] Calculate the number of seconds since 01.01.1970 by using a specified 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



[6] Calculate the number of seconds since 01.01.1970 by using a specified 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


  • No labels