Skip to main content

PQL47 (PQL Function Library - CPM 4.7)

REMAP_TIMESTAMPS

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

Output

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

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

Output

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

Result

Column1 : INT

null

22

Warning

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" ,
    MINUTES ,
    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

Output

T

C : DATE

Wed Jan 07 1970 15:30:00.000

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

Output

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

Result

Column1 : INT

1

1

1

1

1

See also: