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

Description

The REMAP_TIMESTAMPS function counts the number of passed time units for given dates since the epoch year (1970-01-01 00:00:00.000). The timestamps for which to calculate the passed time and also the time unit to use, are given as a parameter to the function call. Additionally, the user can specify a CALENDAR configuration which allows to restrict the dates considered in the calculations. For example, using the WEEKDAY_CALENDAR allows to only consider certain valid weekdays in the calculations.

Syntax

REMAP_TIMESTAMPS ( table.column, time_unit [, calendar_specification ] [, calendar_id_column])

column: The column containing the timestamps to be remapped.
time_unit: The time unit to map the calculation to. One of DAYS, HOURS, MINUTES, SECONDS or MILLISECONDS
calendar_specification: One of WEEKDAY_CALENDAR, FACTORY_CALENDAR, WORKDAY_CALENDAR, or INTERSECT.
calendar_id_column: Column to create a mapping between the respective activities and their used calendar specification. This is mandatory when using multiple calendar specification. For more details, please take a look at the respective documentation of the DateTime Calendar (Since: CELONIS 4.7).

NULL handling

If the input value is NULL, then the result is NULL as well.

Tips

The query may have more than one calendar specification by using INTERSECT on two or more calendar specifications.

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 DATEs in the calculations to only valid dates according to the calendar's definition. There are currently three supported types of calendars ( WEEKDAY_CALENDAR, FACTORY_CALENDAR and WORKDAY_CALENDAR ) and one way to combine these calendars ( INTERSECT ).

Examples

[1] Calculate the number of days since 1970-01-01 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

[2] Calculate the number of days since 1970-01-01 for a constant timestamp (1969-12-01) by using a WEEKDAY_CALENDAR.

Query

Column1

REMAP_TIMESTAMPS (
    TO_TIMESTAMP ( '01.12.1969 06:30.30' , 'DD.MM.YYYY HH:mm.SS' ) ,
    DAYS ,
    WEEKDAY_CALENDAR ( MONDAY TUESDAY WEDNESDAY THURSDAY FRIDAY )
)

Input

Output

Result

Column1 : INT
-22

[3] Calculate the number of seconds since 1970-01-01 00:00:00.000 for a timestamp column by using a FACTORY_CALENDAR.

Query

Column1

REMAP_TIMESTAMPS (
    "activity"."time" ,
    SECONDS ,
    FACTORY_CALENDAR ( "calendar"."start" , "calendar"."end" )
)

Input

activity

id : INT	case : INT	act : STRING	time : 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 : INT	start : DATE	end : 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

[4] Here we use a WORKDAY_CALENDAR based on a TFACS table for the years 1907, 1974, 2018 and 2019. We want to calculate the number of passed workdays since 1970-01-01 until the specified dates (here null and 1974-2-1). The first date is 'null' which always maps to null. The second input is the first of February in the year 1974. From 1970-01-01 until this date, there are 22 valid workdays. This is because all the years between 1970 and 1974 are not covered by the TFACS table (and thus assumed to be invalid workdays). Only the days between 1974-01-01 and 1974-02-01 are used for the calculation. Within this date range, there are 22 valid workdays in Bavaria Germany.

Query

Column1

REMAP_TIMESTAMPS ( Table1.Column1 , DAYS , WORKDAY_CALENDAR ( "TFACS" , 'BAY' ) )

Input

TFACS

IDENT : STRING	JAHR : INT	MON01 : STRING	MON02 : STRING	MON03 : STRING	MON04 : STRING	MON05 : STRING	MON06 : STRING	MON07 : STRING	MON08 : STRING	MON09 : STRING	MON10 : STRING	MON11 : STRING	MON12 : STRING
'BAY'	1904	'0001101100111110011111001111100'	'11111001111100111110011111001'	'1111001111100111110011111001111'	'000011110011111001111100111110'	'0111110011101001111100011110011'	'101001111100111110011111001111'	'1001111100111110011111001111100'	'1111100111110001111001111100111'	'110011111001111100111110011111'	'0011111001111100111110011111001'	'011100111110011111001111100111'	'1100111110011111001111100011110'
'BAY'	1907	'0111001111100111110011111001111'	'1001111100111110011111001111'	'1001111100111110011111001111000'	'011110011111001111100111110011'	'0110011101001111100011110011101'	'001111100111110011111001111100'	'1111100111110011111001111100111'	'1100111110011101001111100111110'	'011111001111100111110011111001'	'1111001111100111110011111001111'	'000111110011111001111100111110'	'0111110011111001111100110010011'
'BAY'	1974	'0111001111100111110011111001111'	'1001111100111110011111001111'	'1001111100111110011111001111100'	'111110011110000111100111110011'	'0110011111001111100111010011111'	'000111100111010001111001111100'	'1111100111110011111001111100111'	'1100111110011101001111100111110'	'011111100111110111110011111001'	'1111001111100111110011111001111'	'000111110011111001101100111110'	'0111110011111001111100110010011'
'BAY'	2018	'0111100111110011111001111100111'	'1100111110011111001111100111'	'1100111110011111001111100111100'	'001111001111100111110011111001'	'0111001110100111110001111001110'	'100111110011111001111100111110'	'0111110011111001111100111110011'	'1110011111001101100111110011111'	'001111100111110011111001111100'	'1101100111110011111001111100111'	'010011111001111100111110011111'	'0011111001111100111110010011001'
'BAY'	2019	'0111001111100111110011111001111'	'1001111100111110011111001111'	'1001111000111110011111001111100'	'111110011111001111000011110011'	'0110011111001111100111110011101'	'001111100011110011101001111100'	'1111100111110011111001111100111'	'1100111110011101001111100111110'	'011111001111100111110011111001'	'1101001111100111110011111001111'	'000111110011111001101100111110'	'0111110011111001111100110010011'

Table1

Column1 : DATE
null
Fri Feb 01 1974 00:00:00.000

Output

Result

Column1 : INT
null
22

The provided workday calendar contains one or more gaps between its start and end year. Please note, that all the missing years (i.e., the gaps) are ignored for calendar calculations. Affected IDENT value: BAY.

[5] The following example shows how the time unit influences the result. The first of January 1970 is a Thursday. The 7th of January is a Wednesday. Due to the defined shifts REMAP_TIMESTAMPS takes the following times into account:
1970-01-01 - 4 hours
1970-01-02 - 0 hours
1970-01-03 - 0 hours
1970-01-04 - 0 hours
1970-01-05 - 8 hours
1970-01-06 - 8 hours
1970-01-07 - 7.5 hours

That results to 27 hours and 30 minutes. Depending on the timestamps the result is rounded down.
DAYS -> 1
HOURS -> 27
MINUTES -> 1650

Query

Column1

REMAP_TIMESTAMPS (
    "T"."C" ,
    MINUTES ,
    WEEKDAY_CALENDAR (
        MONDAY 08:00 - 16:00
        TUESDAY 08:00 - 16:00
        WEDNESDAY 08:00 - 16:00
        THURSDAY 08:00 - 12:00
    )
)

Column2

REMAP_TIMESTAMPS (
    "T"."C" ,
    HOURS ,
    WEEKDAY_CALENDAR (
        MONDAY 08:00 - 16:00
        TUESDAY 08:00 - 16:00
        WEDNESDAY 08:00 - 16:00
        THURSDAY 08:00 - 12:00
    )
)

Column3

REMAP_TIMESTAMPS (
    "T"."C" ,
    DAYS ,
    WEEKDAY_CALENDAR (
        MONDAY 08:00 - 16:00
        TUESDAY 08:00 - 16:00
        WEDNESDAY 08:00 - 16:00
        THURSDAY 08:00 - 12:00
    )
)

Input

T

C : DATE
Wed Jan 07 1970 15:30:00.000

Output

Result

Column1 : INT	Column2 : INT	Column3 : INT
1650	27	1

[6] REMAP_TIMESTAMPS with additional Calendar Id column so that different calendar can be used for different activities.

Query

Column1

REMAP_TIMESTAMPS (
    "Table2"."Timestamp" ,
    DAYS ,
    FACTORY_CALENDAR ( "Table1" ) ,
    "Table2"."CalendarID"
)

Input

Table1

IDENT : STRING	START : DATE	END : DATE
'DE'	Thu Jan 01 1970 00:00:00.000	Fri Jan 02 1970 01:00:00.000
'USA'	Thu Jan 01 1970 00:00:00.000	Fri Jan 02 1970 01:00:00.000
'JPN'	Thu Jan 01 1970 00:00:00.000	Fri Jan 02 1970 01:00:00.000

Table2

CalendarID : STRING	Timestamp : DATE
'DE'	Sat Jul 06 2019 00:00:00.000
'DE'	Wed Jan 07 1970 15:30:00.000
'JPN'	Sat Jul 06 2019 00:00:00.000
'USA'	Wed Jan 07 1970 15:30:00.000
'JPN'	Wed Jan 07 1970 15:30:00.000

Output

Result

Column1 : INT
1
1
1
1
1

Page tree

Description

Syntax

NULL handling

Tips

How It Works

Calendar Specification

Examples

Column1

Result

Column1

Result

Column1

activity

calendar

Result

Column1

TFACS

Table1

Result

Column1

Column2

Column3

T

Result

Column1

Table1

Table2

Result

See also: