REMAP_TIMESTAMPS
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: The time unit to map the calculation to. One of
DAYS,HOURS,MINUTES,SECONDSorMILLISECONDScalendar_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 function determines the time between 1970-01-01 00:00:00.000 to the date value in the input timestamp column and returns it as an INT value in the given unit.
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.
|
[2] Calculate the number of days since 01.01.1970 for a constant timestamp. No calendar is specified.
|
[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.
|
[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 |
[5] Calculate the number of seconds since 01.01.1970 by using a specified factory calendar.
|
[6] Calculate the number of seconds since 01.01.1970 by using a specified factory calendar with a filter.
|